Solved first description repeatedly replaced with primary key (1 Viewer)

[ELiedeker]

Hi, I have a continuous form, Menu Planner, where you can plan weekly meals. It contains MealDate, MealName, Recipe, and Other Supply. Other than MealDate, which is simply a date field, the other fields are comboboxes referencing primary key-description pairs from various tables. The form stores these values into the MenuPlanner table, which contains a primary key, MealDate (date), and all the other fields are stored as numbers referencing the relevant primary keys for MealName, RecipeID, and SupplyID.

The problem I'm having, and I've had it in another database, is that when I use this form, the description value of the first row in the Recipes table gets overwritten with the primary key of the last recipe selected on this form.

I'm hoping this is a familiar problem that someone can give me a clue where to look.

Thanks in advance for any pointers!
Eileen

 

[arnelgp]

look likes a continuous Form?
so it was.
we need to see how the form was setup.

 

[ELiedeker]

What is the best way to show the details you need?

 

[Mike Krailo]

You can upload your database in zipped format for starters. If it's too big, just strip out the majority of data leaving enough to work with.

 

[ELiedeker]

The offending form is Meal Planner 10-3-2020.

 

[CJ_London]

to get a wider audience, you'll need to save it as 2010 or earlier.

 

[ELiedeker]

ugh. It gives me the option to save as 2002-2003, but apparently I have some incompatible functionality.

 

[Mike Krailo]

OK took a brief look at it and there is a problem with the Recipes table. You have a bunch of Yes/No fields that have nothing to do with recipes. If you are doing an actual Recipes table, it should contain recipes and nothing else. You already have a MealNames table which is good but Breakfast, Lunch, Dinner, Snack, Dessert, etc... have nothing to do with recipes. Remove all of those fields.

A single recipe has a Name and a list of ingredients with quantities/amounts. That list would be a separate table of all possible ingredients. Then you could just select the ingredient from a drop down. You supplies table has ingredients in it, but it also has order info which is incorrect. Use a separate Order table for that.

 

[ELiedeker]

This is probably not obvious, since this database only makes sense to me, but those fields all describe the type of recipe, and I use them when searching recipes. For example, I don't want to see oatmeal when I'm searching for dinner recipes or fajitas when I'm searching breakfast recipes.
Do you think the presence of these fields are impacting the form somehow, or are they just making it hard to troubleshoot?

 

[Mike Krailo]

That's fine, but could you not just use a single combo box that referenced the MealNames table and select from a combo box? I'm not saying you can't have a RecipeType field, just use a combo instead of all those yes/no fields.

 

[ELiedeker]

I'm a little confused about the suggestion. I'm using all these booleans to help with searching for recipes (which is the major reason I created the database). A given recipe can fall into multiple of these categories, they are not mutually exclusive. A recipe may be flagged as both a salad and a main, and as an option for both lunch and dinner.

The MealNames table is used when I make a meal plan. The Menu Planner form stores a record in MenuPlanner for MealDate, MealName, and RecipeID (along with some other stuff). The main purpose there is to keep track of my meal plan, and tell me what supplies I need to shop for (another form).

I appreciate your time and help!

 

[mike60smart]

Have tried to replicate the error on the Form specified with no Joy.

Can you upload a screenshot of the problem?

 

[ELiedeker]

Hm. You're right. This makes no sense to me, but it appears to be the search tool at the top causing the problem (but it should all be read only??)
You can see the problem below, where breakfast tacos became "86". It didn't occur when I chose menu items for subsequent days down below. It happened at some point when I was using the search tool above. I haven't totally narrowed what action caused it though.

 

[Mike Krailo]

The Recipe combo has query "Recipe List" as the source and bound column is ID. So what you are seeing is the actual stored data for that field and not the Recipe Name. You should get rid of all those spaces in the field names when you get a chance. That's not the problem but just an observation.

 

[ELiedeker]

I keep telling myself one of these days I'm going to remove all those spaces and all the references to them.

Yes, I bound the combo to the primary key Recipe.ID, but in the format properties for the control, Column Count is 2, Column Widths is 0";2". So it displays the recipe names rather than the key. What's happening is that the "recipe name" for breakfast tacos is getting overwritten at some point with the primary key of another row in the recipes table. You can see it if you look into the recipes table. I'm sure it's because it's the first row of the table, I just can't figure out why.

This is one of the things that confuses me. Coming from the Oracle world, you would store the primary key and do the work of joining in the description yourself. But it seems that in Access, when you define a table with a column that references a foreign key (as MenuPlanner references RecipeID), as long as you define the Lookup in the column definition to reference where it's coming from, it displays the descriptor for that foreign key -- even when you're looking directly at the table. It seems to me that somewhere in all that stuff that's happening automagically is the root of my problem.

 

[Mike Krailo]

That MenuPlanner table has lookup fields in it. Removing those is the first thing to do to more clearly see what is happening. After I started removing those lookup fields, the data in SupplyOrder disappered! Why would you use a lookup field on the primary key field of the SupplyOrder table???

Same thing in Vegan Sub field for Recipes table. After removing the lookup field and looking at the data, you have multiple values in one of the fields:

ID=33
Vegan Sub = 291, 328

That's a data error. remove one of those values as you can only have one of them.

You should be creating the lookups in the form itself, not the table.

 

[ELiedeker]

I started out using a multipick combo box somewhere, and ended up removing it because I didn't like the way it worked.. I thought I had removed all the data associated with that, but it sounds like that's what's happening in veganSub.

Oof. Don't look at SupplyOrder. That's still a playground.

 

[Mike Krailo]

Here was the other clue in your relationships window. Notice that "Vegan Sub.Value".

 

[ELiedeker]

Yes, it looks like it's still designed to accept multiple values. I'm not sure if I like that feature or not. It's very bizarre database design, for me. But hey, getting to know the Access world. What do you think?

 

[Mike Krailo]

Where did you determine that it is setup to accept multivalues? I didn't see that for your particular Vegan Sub field. It looks like a regular number field to me in the recipe table!

Nevermind, I see it now. I had deleted the lookup fields but this is defined in your original.