List of all tables for List Box

[mpm32]

I am trying to create a listing of tables to use in a list box. I found this code and I am trying to use it to create the value list for the listbox.

Private Sub Form_Load()

Dim objAO As AccessObject
Dim objCP As Object
Dim strValues As String

Set opjCP = Application.CurrentProject

For Each objAO In opbCP.AllTables
    strValues = strValues & objAO.Name & ";"
Next objAO

AllTables.RowSourceType = "Value List"
AllTables.RowSource = strValues

End Sub

When I open the form I get the error; "Runtime error 424 object required"

When I debug it highlights this line of code; For Each objAO In opbCP.AllTables

Any ideas on what I'm doing wrong?

Thanks in advance.

 

[SgtJarrow]

If you copied/pasted this code, then you have a spelling issue.

Change For Each objAO In opbCP.AllTables to For Each objAO In objCP.AllTables

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[mpm32]

Ha,

caught by a spelling error, let me try that.

Thanks

 

[mpm32]

Hmm, I fixed the spelling error but now get this; Run-Time error '91' Object variable or With block variable not set.

When I mouse over 'objAO' in the highlighted line; For Each objAO In objCP.AllTables, the pop-up says "objAO=nothing"

Maybe I'm missing a reference?

 

[PHV]

Replace this:
Set opjCP = Application.CurrentProject
with this:
Set opjCP = Application.CurrentData

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mpm32]

Hmm, that didn't work either.

I'm not sure what I'm trying to do with the listbox will even work anyway.

There are a number of tables with all the same fields, a new one comes in each month.

I would like to select the table in the listbox and have that table name be used for the fields in a query. This way a new query would not have to be made each month or the table names don't have to be updated in the query. So like a dynamic query but the table names changing, not the fields.

I did a test with a prefilled list box and the query is not working.

So this exercise in having the tables in the list box may be for naught.

I did not create the original db and I wouldn't have set it up this way but I need to find a way to do what I'm looking for it to do.

Thanks for your help and I'll keep trying to find a way for this to work.

 

[PHV]

that didn't work either
Which error message ? with which code ?

 

[mpm32]

I'm getting the same error Run-Time error '91' Object variable or With block variable not set.

The debug highlights 'For Each objAO In objCP.AllTables'

Private Sub Form_Load()

Dim objAO As AccessObject
Dim objCP As Object
Dim strValues As String

Set opjCP = Application.CurrentData

For Each objAO In objCP.AllTables
  strValues = strValues & objAO.Name & ";"
Next objAO

AllTables.RowSourceType = "Value List"
AllTables.RowSource = strValues

End Sub

But like I explained above, even if I figure this out, I'm not sure that it's going to do what I want it to do.

 

[PHV]

Set o[!]b[/!]jCP = Application.CurrentData

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mpm32]

Excellent that works great, I really need to proofread better.

Now I just have to figure out how to use this in a dynamic query where the fields come from the selected table in the query.

 

[missinglinq]

Another way, with the RowSourceType set to Table/Query, would be to use this as the Listbox RowSource:

SELECT [Name] FROM MSysObjects WHERE [Name] Not Like '~*' AND [Name] Not Like 'MSys*' AND [Type]=1;


The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!