Custom combobox sync solution

[rhewitt]

I have three combo boxes representing species. The first of which is Taxa (taxonomic group; ie Amphibian, Bird, Snail, etc). The user first makes a selection in this box, which limits the next two comboboxes. *I've already accomplished the cascading portion.

The second and third boxes are on the same level of the hierarchy. The boxes are Common Name and Scientific Name for which there is only one record for each species. The reason I'm using two separate comboboxes instead of a combobox and a text box is that for some of the rare species, a common name does not exist. In this case the user would have to make their selection on the Scientific Name from the filtered list. The common names are usually easier for people to recognize, but they may be forced to choose with the Scientific name.

I've managed to get this to work with the following VBA:
Me.cboScientific.Value = Me.cboScientific.ItemData(Me.cboCommonName.ListInd ex)

The problem is if the user makes a mistake in their selection of species name. The user can go back into the combobox and the list is still filtered by the specified taxa, but the user cannot change the value in this box. Even selecting another species leaves the original species in the combo box.

Do I need to requery or refresh somehow?

[Missinglinq]

Assuming that the Bound Column of each Combobox is the one containing the names, and that both sets of names are in a single Table, here's how to set the CommonName cbo:

Private Sub cboScientificName_AfterUpdate()
 Me.cboCommonName = DLookup("CommonNameField", "TableOrQueryName", "[ScientificNameField]= '" & Me.cboScientificName & "'")
End Sub

you'll have to substitute your actual names, of course. And reverse everything to go in the opposite direction.

Linq ;0)>

[rhewitt]

Excuse my ignorance, I'm new to access and I'm not well versed in terminology as of yet. The data for this query is all in one table.

To this point I've been dealing with unbound combo boxes -- can I retroactively go back and bind them to fields?

[Missinglinq]

That's fine, simply replace the TableOrQueryName in the code with the name of your Query.

Although the Comboboxes do not have to be Bound, for my code to work, you can, indeed, retroactively Bind a Combobox to a Field in the underlying Table/Query.

Linq ;0)>

[rhewitt]

The following code prints the correct name to the 'Immediate Window,' but does not update the opposing combobox.

Private Sub cboCommonName_AfterUpdate()
    Me.cboScientific = DLookup("[ScientificName]", "Species", "[CommonName] = '" & Me.cboCommonName.Text & "'")
    Debug.Print DLookup("[ScientificName]", "Species", "[CommonName] = '" & Me.cboCommonName.Text & "'")
End Sub

Am I missing something obvious?

[rhewitt]

I hacked together the following solution, but I'm still interested if anyone can help me to synchronize the two combo boxes!


When the user chooses an option from EITHER combobox, the text boxes below are filled with both species names. The problem is the user could select different Common and Scientific names, and if they're not paying attention wouldn't know which species is selected.