created doubly linked combo boxes

P

peter

I've looked around and tried a number of suggestions that I've seen,
but can't get it to work. I'm relatively new to Access, so that's
part of my problem I'm sure. I'd be grateful for any advice.

I'd like to create two combo boxes, where the user can enter a value
in either one, and the other box will show the corresponding value
from the table. I've got a table of species which lists common names,
scientific names, and short codes for each. I'd like to be able enter
the codes in box 1, and have box 2 automatically show the common name.
I'd also like the reverse to be able to happen- i.e., input the common
name in box 2 and have the code pop up in box 1.

How would this work?

Cheers-
Peter
 
D

Douglas J. Steele

You'd put code in the AfterUpdate event of both combo boxes to change the
row source for the other combo box based on what's been selected in the
given one.

Something like:

Private Sub Combo1_AfterUpdate()

Me!Combo2.RowSource = "SELECT Field1, FIeld2 " & _
"FROM Table2 " & _
"WHERE Field3 = " & Me!Combo1 & _
" ORDER BY FIeld2"

End Sub

Private Sub Combo2_AfterUpdate()

Me!Combo1.RowSource = "SELECT Field1, FIeld2 " & _
"FROM Table1 " & _
"WHERE Field3 = " & Me!Combo2 & _
" ORDER BY FIeld2"

End Sub

Of course, since what's displayed in, say, combo2 will only be a subset of
the possibilities, since it'll only show data related to what's already
selected in combo1, you may not be able to actually select what you want in
combo2. You may need to add buttons that will let you set the RowSource back
to unfiltered.
 
Top