Use a multi-field combobox on a form to fill in a single value in a data table

[rhewitt]

I have a form that allows users to see two names for the same record (scientific name and common name). The Control Source of the combobox is the Species_ID. The Species_ID is included in the multi combobox, but is hidden with columnWidth = 0. I'm unable to actually select anything in the drop-down menu. All of the names are present and correct, but the combobox won't set the value based on what I click.

It seems like his might be a 'Type' issue. Obviously I'm selecting based on text fields, and the output needs to be a number. I've included the Species_ID in the combobox; how do I get the combobox to accept user input and enter the Species_ID value into the data table?



A bit difficult to articulate, but I hope that gets the point accross.

[June7]

Is the ColumnCount property set to 3?
Is the Species_ID the first field in the RowSource query?
Is the BoundColumn property set to 1?

[rhewitt]

Column Count = 4

Columns in Order; Column Width:
1: Species_ID; 0
2: Taxa; 0
3: Common Name; 2.5
4: Scientific Name; 2.5

RowSource is dynamic, defined by After_Update event of cboTaxa:

cboSpecies.RowSource = "SELECT Species_ID, Taxon, CommonName, ScientificName " & _
    "FROM Species " & _
    "WHERE Species.Taxon = '" & cboTaxa & "'; "

Bound Column = 1

[June7]

Looks all okay.

Now I suspect the form RecordSource. Is it table or query? If query, show the SQL statement.

If you want to provide db for analysis, follow instructions at bottom of my post.

[rhewitt]

Test.zip

The form in question is called "jnctnSpeciesContact" under the Data Entry Forms tab of the Navigation Pane.

This form enters data into a junction table for a many-to-many relationship.

Thanks for taking a look!

[June7]

The Taxon combobox should be unbound - i.e., no ControlSource.

There is no reason to include the Species table in the form's RecordSource, but if do, change the jointype to: 'Include all records from jnctnSpeciesContacts ...'

The contact combobox is not set up correctly. The RowSource has only the name, not ID, however the bound field is for Contact_ID.

[rhewitt]

The Taxon combobox should be unbound - i.e., no ControlSource.

There is no reason to include the Species table in the form's RecordSource, but if do, change the jointype to: 'Include all records from jnctnSpeciesContacts ...'

The contact combobox is not set up correctly. The RowSource has only the name, not ID, however the bound field is for Contact_ID.

Thanks! I'm really starting to get the hang of this now that I'm getting over some of the terminology hurdles.