COMBO BOX LIST ALL TABLES ENTRIES 2

[ciel220]

Hello,

I have a .mdb file with the following tables:

Jan01
Feb01
Mar01
...
Sep02
Oct02

All tables are named under the month and year of creation.
I want to creat a form, with a combo box listing all existing tables name.

How could I do that? I search so long on the web and on the MS Access help but I couldn't find any info. on that.

Please help!

ciel220

 

[essnrv]

Try Below Code

Dim tdfTemp As TableDef,strTableName as string

For i = 0 To dbTemp.TableDefs.Count - 1
strTableName=dbTemp.TableDefs(i).Name
'Assign StrTableName(s) as value list
Next i

 

[essnrv]

Exact code

'In design time set Combo Box Row Source type to Value list and add Below code in form_load event
Dim curDb As Database, tb As TableDef

For i = 0 To curDb.TableDefs.Count - 1
If strTableName = "" Then
strTableName = curDb.TableDefs(i).Name
Else
strTableName = strTableName & ";" & curDb.TableDefs(i).Name
End If
Next i
cmb.RowSource = strTableName

 

[jebry]

Hi!

Another possibility is to use this SQL in the row source of the combo box:

Select Name From MySysObjects Where Type = 1 And Name Not Like 'MSys*'

This should return all of the table names

hth
Jeff Bridgham
bridgham@purdue.edu

 

[ciel220]

Sorry, i'm sort of new to Access.

Jebry - how can I enter a SQL in row source? It doesn't allow me to enter 'text' but only select table/query form the build in drop down box. What is 'MySysObjects'? Is 'Name' a Key Word?

Essnrv - I tried to put the code to my on_load event procedure but it says 'there's no such type as 'Database' nor 'TblDef'. How could I create these data type?

Thank so much to both of you. I truly appreciate that!

 

[jebry]

Hi again!

MySysObjects is a table that Access automatically creates to track objects. If you want to see it select Tools/Options click on the view tab and place a check in the box next to system objects. For the other question you are looking at the row source type which allows you only the choices you mentioned. Row source is below it and you type the SQL in there. Alternatively, you can allow the system table to be seen as described above and then create the query in the query design view as I described above. Then you can put the name of the query in the row source.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[ciel220]

Hello Jebry,

Thanks so much for your prompt response!
Unfortunately, I follow all your instructions and it still doesnt' work.

I created a form and a combo box with it.
In row source -> zoom, I enter your query "Select Name From MySysObjects Where Type = 1 And Name Not Like 'MSys*'"
In row source type, I leave it blank (I actually try all options in row source type).

When I open my form, I see nothing but only the query you gave. None of the table name is printed.
What should I do? Please help!

 

[jebry]

Hi!

Sorry, the MySysObjects should be MSysObjects and you don't need the double quotes.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[ciel220]

Hello,

I have put in 'MSysObjects' and remove the "".
When I use this in a query, it works (I run the query and all tables come out). But when it comes to the row source, it doesn't.

Is there anything wrong w/ the setting of my combo box? Thanks so much for your help and attention!

Thanks!!!!

 

[essnrv]

If you are in VB Environment then you need to add DA0 3.5/3.6 to your project. In Access forms you need not to add anything.

Hope this may help

 

[ciel220]

Many thanks to both of you!

ciel220