Synchronized Comboboxes

[Echilon]

I have two comboboxes on a nested form. I've linked the outer form to the
comboboxes, so when an item in either is selected the inner form selection
changes.

The two comboboxes are showing different fields from the same table (I want
to select by either ID or Name). How can I synchronize the selection, so when
the user selects a new ID, the corresponding title will also be selected?
I've tried VB but my VB rates just above my FORTRAN.

 

[Graham Mandeno]

Hi Echilon

I don't quite understand your description (not sure what you mean by "nested
form"), so if my answer is way off the mark please forgive me and repost
with some more information.

I'm assuming you have a main form (unbound) containing two combo boxes and a
subform which is bound to your table or query. The underlying table has a
primary key (let's call it IDField) and a unique text field (let's call it
NameField). The two combo boxes have their RowSources based on the same
table or query as the subform, with one listing the IDField and the other
listing NameField.

Now, I understand that when you select either an ID or a Name from either
combo, you want the other combo to be changed to show the corresponding
Name/ID, and you want the subform to display the record corresponding to the
chosen ID/Name.

Correct?

OK, then set your combo box properties as follows:

ID Combo:
Name: cboSelectID
RowSource: Select IDField, NameField from YourTable order by IDField;
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 1.5cm
ListWidth: 5cm

Name Combo:
Name: cboSelectName
RowSource: Select IDField, NameField from YourTable order by NameField;
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0

Now set the properties of your subform control as follows:
LinkMasterFields: cboSelectID
LinkChildFields: IDField

Finally, add AfterUpdate event procedures for both your combos to keep them
in synch:

Private Sub cboSelectID_AfterUpdate()
cboSelectName = cboSelectID
End Sub

Private Sub cboSelectName_AfterUpdate()
cboSelectID = cboSelectName
End Sub