Dynamically populating Access (ADP) List Box

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?
hcstttamAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"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
 
hcstttamAuthor Commented:
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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
GRayLCommented:
Try this:

Me.Table_Entries.RowSource = "select * from " & Me![Table_Name]
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
hcstttamAuthor Commented:
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
 
GRayLCommented:
As the table name is most certainly text:

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

Note the use of single quotes.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:


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

mx
0
 
hcstttamAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
This MUST have something to do with ADP ...

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

mx
0
 
hcstttamAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You are welcome ...

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.