asked on

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?

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"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

hcstttam

ASKER

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.

GRayL

Try this:

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

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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

hcstttam

ASKER

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.

GRayL

As the table name is most certainly text:

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

Note the use of single quotes.

ASKER CERTIFIED SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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

mx

hcstttam

ASKER

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.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

This MUST have something to do with ADP ...

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

mx

hcstttam

ASKER

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

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

You are welcome ...

mx