?
Solved

Dynamically populating Access (ADP) List Box

Posted on 2007-07-23
12
Medium Priority
?
264 Views
Last Modified: 2013-12-05
I have a access (ADP) form which has (1) a combo box, "Table_Name", from which a user can select the name of a database table and (2) a List Box, "Table_Entries", which displays the contents of that table.  To populate "Table_Entries", I have the following code:

Private Sub Table_Name_Change()
  Me.Table_Entries.RowSource = "select * from " & [Table_Name]
  Me.Table_Entries.Requery
End Sub

However, this code does nothing.  "Table_Entries" remains blank and if I look at the value of "RowSource" under design view, its value is unchanged.  However, if I manually edit the value of "RowSource" under design view, "Table_Entries" is correctly populated.

Boring stuff:
OS: MS XP Version 2002, 5.1.2600 Service Pack 2 Build 2600
Access: Access 2003 (11.6566.8122) SP2
Database: Microsoft SQL Server 2005
 
Anyone have a clue whats going on?
0
Comment
Question by:hcstttam
  • 6
  • 4
  • 2
12 Comments
 
LVL 75
ID: 19548684
"and if I look at the value of "RowSource" under design view, its value is unchanged"

Well, since you are setting this dynamically, it will not be saved per se.

ANd try this:

Me.Table_Entries.RowSource = "select * from  [Table_Name]"

mx
0
 

Author Comment

by:hcstttam
ID: 19548921
I tried:

Me.Table_Entries.RowSource = "select * from  [Table_Name]"

Table_Entries is still blank.  If I use the debugger, the "RowSource" property is set to  "select * from  [Table_Name]", so the code is running as expected.

0
 
LVL 44

Expert Comment

by:GRayL
ID: 19549298
Try this:

Me.Table_Entries.RowSource = "select * from " & Me![Table_Name]
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75
ID: 19549468
Sorry ... I missed that this came from a combo box.

Is the table name in the combo the Bound Column ... and is it Column 1 ?

Otherwise, you would need something like

Me.Table_Entries.RowSource = "select * from " & Me.Table_Name.Column(1)   ' Column are zero based.

IF ... it is Column one (physically), then


Me.Table_Entries.RowSource = "select * from " & Me.Table_Name

should work.

mx



0
 

Author Comment

by:hcstttam
ID: 19549904
GRayL: your suggestion didn't change anything.

DatabaseMX:

 I think your first line of code should have been:
  Me.Table_Entries.RowSource = "select * from " & Me.Table_Name.Column(0)   ' Column are zero based.
I changed the "1" to a "0".   Anyhow, I tried both, and neither worked.  Using 0, set the property to what I expected.  Using 1, caused the table name to just be an empty string.

I have only a single column in my combo box, so I don't think it is an issue with me using the wrong column.

Note that if I set a break point immediately before calling Requery, the value of Me.Table_Entries.RowSource is set to the correct value when I use the code:
  Me.Table_Entries.RowSource = "select * from " & Me.Table_Name

But for some reason the Table_Entries form is not updated.

0
 
LVL 44

Expert Comment

by:GRayL
ID: 19549944
As the table name is most certainly text:

Me.Table_Entries.RowSource = "select * from '" & Me![Table_Name] & "';"

Note the use of single quotes.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 150 total points
ID: 19549960
Actually ... you don't need the Requery btw ... setting the Row Source does that automatically. So, you end up doing it twice.

Oh ... I just noticed. You are using OnChange.

Use AfterUpdate ... of the combo box

OK
0
 
LVL 75
ID: 19549982


Private Sub Table_Name_AfterUpdate()
  Me.Table_Entries.RowSource = "select * from " & Chr(34) &  Me.Table_Name & Chr(34)
End Sub

mx
0
 

Author Comment

by:hcstttam
ID: 19550580
I moved the code to AfterUpdate(), but nothing changed.  I also tried putting the table name into single quotes: no change.  :(  Thanks for the suggestions so far.
0
 
LVL 75
ID: 19550695
This MUST have something to do with ADP ...

Can you post the SQL for the Row Source for combo box "Table_Name"  ??

mx
0
 

Author Comment

by:hcstttam
ID: 19550840
weird, after playing around with manually editing the Row Source property through the design view, things started to work.  After some more testing, it appears that moving the code to AfterUpdate() plus manually editing the row source property at least once, fixes the code.  Very weird.  Thanks for everyone's help
0
 
LVL 75
ID: 19550880
You are welcome ...

mx
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question