Cascading Combo Boxes

[SeadnaS]

Hi,

On my database I have two combo boxes, one called sizecombo and one called classcombo. sizecombo lists all of the tables in my database.

Now, I want classcombo to limit the number of tables listed in sizecombo to tables that begin with the word selected in classcombo.

So when it works I will be able to select a class from the classcombo. This will shorten the list in sizecombo to list only tables that start with the letters selected in classcombo. So say I select "ABCD" from classcombo then sizecombo will only list the tables I have that start with the 4 letters ABCD.

I have a lot of tables and this will help to make my form easier to use.

Thanks for any help!

Hope that makes sense!

 

[SeadnaS]

SELECT MsysObjects.Name, Mid([Name],5) AS QueryName FROM MsysObjects WHERE (((Left([Name],3))=Me.classcombo)) ORDER BY MsysObjects.Name;

Thats what ive tried as the row source. Not working.

 

[Gammachaser]

I get nervous when I see people even referring to MsysObjects. Maybe it is just me.

I would build a table (tblMyTables) containing the names of the tables that I wanted to use for the RowSource. The Table should have one Text Field (TableName) and nothing else. Use the following as your After_Update Event for ClassCombo:

Private Sub ClassCombo_AfterUpdate
Dim tbl As TableDef

DoCmd.RunSQL "DELETE * FROM tblMyTables"
For Each tbl In CurrentDb.TableDefs
   Select Case tbl.Name Like ClassCombo & "*"
      Case True
          DoCmd.RunSQL "INSERT INTO tblMyTables Values('" & tbl.Name & "')"
   End Select
Next
SizeCombo.Requery
End Sub

Make the RowSource for SizeCombo

SELECT * FROM tblMyTables OrderBy TableName

You could, of course, make the table temporary and delete the whole table and recreate it each time if you prefer.

This is one method. I would like to see how others (i.e. more skilled than I am) would do it.

 

[SeadnaS]

Tried using that but i get an error message: Syntax Error in FROM clause.

 

[SeadnaS]

Kinda don't understand how your code is supposed to do what i need.

 

[SeadnaS]

I changed it to this

SELECT MsysObjects.Name, Mid([Name],5) AS QueryName FROM MsysObjects WHERE (((Left([Name],3))=classcombo)) ORDER BY MsysObjects.Name;

got it working.