Unable to save records when using Combo Box

[nika.duncan]

Hey


I have a database to select lunch for the week where I have created tables to store the choices; appetizer, protein, carbohydrates, side order and vegetables which would be selected by users which should be given to the chef the beginning of each morning. After selection I want the detail to be saved "Staff_Details" table. The combo box works after the first record is selected. When I select the day of the week "Monday" and the appetizer, carb_choice, protein, side orders and vegetables for Monday appears in the drop down combo boxes for selection but to add another record I receive the error message "cannot add record(s); join key of the table "Staff_Detail" not in recordset.

See file attached for a better understanding of what I am trying to do.

Any assistance will be greatly appreciated.

[aytee111]

The record source for the form needs to be the name of the table only - Staff_Details.

Also, one of the rule of database normalization is that data is never to be repeated - the staff name needs to be kept in one table only and the ID key carried to other places as needed.

[davegri]

Nika-LunchMenuFinal-v1.zip

See if this helps.
Made too many changes to list them all. Renamed table keys with _PK and _FK suffixes. Added search combobox to frmMainMenu.
Redid the relationship diagram to use the keys instead of the text items.

Since a staff member could have more than one detail record, I made a "Main Form"/"Subform" setup with Staff_Listing and Staff_Detail

Note that it's a bad idea to have a field in a table with the same name as the table, like Side_Choice (I changed the field to Side_Dish and Vegie_Choice to Vegie_Dish).
Another way to avoid this situation is to use a 'tbl' prefix for all table names, like tblVegie_Choice and tblSide_Choice.

None of the existing queries will run now, but I think they are all unnecessary anyhow.