469,636 Members | 1,527 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,636 developers. It's quick & easy.

two combo boxes working together

107 100+
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
Dec 5 '07 #1
2 2112
675 512MB
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.

A) ComboBox1.RecordSource 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
Expand|Select|Wrap|Line Numbers
  1. Private Sub ComboBox1_Change()
  2.     ComboBox2 = ""      'Clear old value from ComboBox2
  3.     ComboBox2.RowSource = "SELECT Table2.Code, Table2.Number " & _
  4.             "FROM Table2 " & _
  5.             "WHERE (((Table2.Number)=" & ComboBox1 & ")) " & _
  6.             "ORDER BY Table2.Number;"
  7. 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.RowSource and add an ORDER BY Clause, so the list presents Bob, Jim, Tom in that order.

I hope this helps.

Dec 5 '07 #2
107 100+
Thank you
Thanks for solution and explanation.
Dec 6 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by cognoscento | last post: by
4 posts views Thread by Dave | last post: by
2 posts views Thread by franc sutherland | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.