1st item in combo box can't be selected because a related record is required in table

[MSG56]

I'm creating a recipe db and have a combo box caled "FoodCategories" in my main form (Recipes) that allows the user to select a food catagory. This combo box was recently converted from a text box. Now, when I input data in the Recipes form everything works fine unless I select the first item in the combo box, in which case I get the error message "You cannot add or change a record because a related record is required in the table 'FoodCategories' ". I know the item is in the Foood Categories table. When I first noticed the problem I couldn't add "Condiments", which was the first item in the combo box. Playing around I added a new category called "Breakfast", which moved to the top of the sorted list. Now, I can select Condiments in the combo box, but I can select Breakfast. I've attached a copy of table relationships, which I think are correct.

[ItsMe]

What is the Control Source of your Combo, "FoodCategoryID"? Did you assign the correct name from the correct table to your Control Source, eg Recipes.FoodCategoryID?

[MSG56]

The control source for the combo box is FoodCategoryID. The Row Source in [FoodCategories].Category

[ItsMe]

The control source for the combo box is FoodCategoryID. The Row Source in [FoodCategories].Category

Aside from your original post, this statement is problematic. You will want your Combo's Control Source to be the same data type as the .Value of the combo. If the RowSource only has Category (a field for the benefit of huimans), the combo will not be able to assign FoodCategoryID from table FoodCatagories PK to FoodCategoryID table Recipes FK.

Perhaps you should tale a look at the RowSource of your combo and make sure the correct value is getting assigned to the correct field. You can use the query builder for assistance.


.

[MSG56]

First, thank you for the help.

Okay, sorry, I should have expanded my property sheet. Please see the row source below.

So, the bound column is "0", which is the autonumber of the FoodCategories table. The Catogory field is text. Let me make sure I understand what you are saying about the Value of the combo. It is storing the autonumber, but my row sorce is catagory. So, are you saying that the row source should be the autonumber, namely the FoodCategoryID?

[ItsMe]

Sometimes an Index starts with 0 and other times it starts with 1. The Bound Column property will consider 1 as the first column and 2 as the second..... As for your question about your RowSource, yes, you should include your Primary Key field, FoodCategoryID, in your SELECT statement. You Relations Window indicates this is your Field Name for your PK. You should be storing PK values in the relative Foreign Key fields.

[MSG56]

You should be storing PK values in the relative Foreign Key fields.

I'm new at this so I'm having trouble understanding exactly what this means. I take it I should be storing the PK vaule, in this case FoodCategoryID, in the Foreign Key field, which I think would be the FoodCatagoryID filed of my Recipes table. Isn't that what I'm doing?

[MSG56]

Problem Solved! Even though you were giving me good advice and I'm sure you were on to my problem, being the rookie that I am with Access, I couldn't quite decipher the message. So....I deleted the combo box and created a knew one and now the Row Source is:

SELECT [FoodCategories].[FoodCategoryID], [FoodCategories].[Catagory] From FoodCategories ORDER BY [Catagory];

Thanks for all the help. I'm learning.

[ItsMe]

Glad you were able to get the RowSource taken care of. I was not sure that was the root of the problem but I knew it needed to be addressed. Welcome to the forum!