two combo boxes working together

[buddyr]

Hello,
I am having trouble with two combo boxes working together.
two tables table1 table2
table1 fields -number and name
table 2 fields -code and number
data in table one field number 1,2,3
data in table one field name Tom, Bob, Jim
data in table two field code 11,22,33,44,55, 66,
data in table two field number 1,2,3,1,2,3

If I select Tom in the first combo box- I want the choices of 11 and 44 in the second combo box to show.
This is just a simple test- I am trying to make work - before I change my main database.
I have looked at different examples and cannot get second combo box to even have data after I enter all the code.
help will be apreciated.- I am a beginner on combo boxes.
thank you

[OldBirdman]

Assume (from your description of 2 tables):
1) ComboBox1 has 2 columns, Number and Name. The first column(Number) is the bound column, and is hidden.
2) ComboBox2 has 2 columns, Code and Number. The second column(Number) is the bound column, and is hidden.

Therefore:
A) ComboBox1.Recor dSource should be "SELECT Table1.Number, Table1.Name FROM Table1 ORDER BY Table1.Name;" This will probably be generated accurately by the Wizard when the ComboBox is created.
B) Create Event OnChange for ComboBox1

Private Sub ComboBox1_Change()
    ComboBox2 = ""      'Clear old value from ComboBox2
    ComboBox2.RowSource = "SELECT Table2.Code, Table2.Number " & _
            "FROM Table2 " & _
            "WHERE (((Table2.Number)=" & ComboBox1 & ")) " & _
            "ORDER BY Table2.Number;"
End Sub
 

Access will go a long way toward helping write this (or similar) SQL statements. In design view for the form, select Row Source (Data Tab) and then click the ". . ." on the Row Source Property for ComboBox2. This opens the Query Builder. Make changes as normal and test by clicking the Datasheet View button. At this point, the criteria will be constants entered in the Design Grid.

When the query is working correctly, click the "down arrow" next to the Design View / Datasheet View button and select SQL View. Copy the necessary parts to your code, changing the constants to your own variables / controls (Note above the " & ComboBox1 & " in the WHERE Clause of the SQL Statement.

You might want to change ComboBox1.RowSo urce and add an ORDER BY Clause, so the list presents Bob, Jim, Tom in that order.

I hope this helps.

OldBirdman

[buddyr]

Thank you
Thanks for solution and explanation.