By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,770 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

two combo boxes working together

100+
P: 107
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
Dec 5 '07 #1
Share this Question
Share on Google+
2 Replies


100+
P: 675
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.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
  8.  
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.

OldBirdman
Dec 5 '07 #2

100+
P: 107
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.