I think this is a fairly common problem but I've yet to find anything on google to help so maybe someone here can. I've got a table Observation_Detailed which I want updated by frm_Obs_Detailed. One of the fields is Species_ID which I want selected by a combo populated from the Species_Information table. The Control Source for cbo_Species is:

=[Observation_Detailed_Form]![Species_ID]

The Row Source for 'cbo_Species` is:

SELECT Species_Information.Species_ID, Species_Information.Common_Name FROM Species_Information ORDER BY Species_Information.Common_Name; 

The combo box is populated by two columns (Species_ID, Common_Name) correctly and I want the Species_ID to be used for the relevant category on my Observation_Detailed table. I can't select anything from the list and it only has #Name? for a value.

The form has Allow Additions and Allow Editing enabled, Data Entry is True, and the combo isn't locked. Species_Information!Species_ID ->(one to many)-> Observation_Detailed_Form!Species_ID and both columns are of the same type (number). The Bound Column for the combo box is set to 1.

I've seen some advice that you can't have a row source and a control source but then how does the combo populate the table with the selection? Other advice suggesting something about not being able to use a query as a value for a control source?

The only way around it I can figure is to have no control source and then add a macro/VBA on the 'Submit' button to set Forms!Observation_Detailed_Form!Species_ID = Me!cbo_Species.Value or something similar? It seems a little ludicrous to me that it's this hard to select a value from another table but this is just the latest in week of constant frustrations with Access.

Thanks for reading!