Solved first description repeatedly replaced with primary key

[ELiedeker]

I think I set it up as a multipick originally, and had to go in and define the field that way. Then when I redefined the form to only allow single pick, I forgot to redefine the table.

 

[ELiedeker]

So, in fact I've now figured out the moment when the Recipes.[Recipe Name] is getting overwritten. It's when I select a recipe from the Recipes list inside the subform SF_RecipeSearch embedded at the top of the form. The primary key of the recipe I select overwrites the [recipe name] of the first row of the Recipes table.

 

[ELiedeker]

Geez. I'm embarrassed. The Recipes combobox on the subform of the search control at the top was bound to Recipes.ID.

Thanks so much for all of your help. I really feel like an idiot. Learning... I greatly appreciate everyone's time and feedback.

 

[Mike Krailo]

Glad we can help. That was an interesting problem for sure.

 

[ELiedeker]

Appreciate all your other thoughts and tips as well, Mike. Have a good one!

 

[Mike Krailo]

MenuPlan table has no primary key and that is the data source for the form in question. The "Meal Planner 10-3-2020" form then has no connection/link to the subform. Are you sure that is what you want? Doesn't make sense to me. Usually a subform has a Master/Child link to the main form via common ID field.

This is a strange use of a subform so if this is as intended, I would like to understand how this works. Usually, the main form would have a single view of one particular record and the subform is the many side of a One to Many relationship. In this case though, you have a continuous form with a non-linked subform in the header! Just trying to understand the purpose of this form. It appears to just show all the meal dates after a given date supplied in the Unbound search box. So no data is being entered, just viewed. Is that correct?

 

[ELiedeker]

I'm an experienced programmer, but not terribly experienced with Access forms, so I'm not surprised if I'm doing things in an unorthodox way! And this is where I really appreciate help from you all not to shoot myself in the foot.

The subform at the top is actually a search tool, so choosing the various parameters (Dinner, Main, Vegan, etc.) filters the list of recipes. Initially I had this happening on the form itself, and it worked fine until I changed the parameters and suddenly my previous menu selections got filtered out. Lol. So, I decided to consolidate the search functionality onto a subform. Other than the stupid error of binding the recipe combobox, it seems to work, although it's duplicated effort to find a recipe up above and then go down below and choose it for a particular day. I suppose I could try to make that happen programmatically.

I have other forms with subforms that work like you describe. Do you think it's a bad idea to use it this way?

 

[Mike Krailo]

It wasn't working for me as a search so if it is working for you, would you mind uploading the working version so I can see how it works. No matter what I select in the search form, it does nothing to filter the continuous list. I guess I was thrown off by the name of the form. MealPlanner, I would think would enable you to select different meals for the week or any date in the future. I would call that form MealList or something like that because that is all it is, a list of meals.

There is nothing wrong with what you are doing if it is just filtering out meals from a large list. I just misunderstood the use case of the form.
BTW, I just eat whatever my wife feeds me

 

[ELiedeker]

I just realized you're not seeing what I'm seeing, because the meals are also filtered using the date at the top of the form. So if you put in today's date, you'll see what I had entered for this week (if I entered before zipping). Or you could use an earlier date.

To clarify, the search criteria only filter the Recipe List at the top of the form, not the records.

 

[Mike Krailo]

Ahh, I see that now. I just entered in 9/1/20 for the date and then got 37 main records. Then the Recipes list was fully populated. Got it now. Just wasn't intuitive in how it was supposed to be used. Put taco in search term, and just the taco recipes are listed in the dropdown.

I'm still not sure what that gains you as there are no actual recipes there, just the names of the recipes. As long as it is doing what you want, I guess that's all that matters.

 

[ELiedeker]

Yes, so this form is just where I choose the menu for the week. Once I have the menu, I go to the first form "*Take Inventory for Meals" and it shows me what ingredients I need to make sure I have, with a way to add them to my shopping list (kludgy - again, my Access skills... I'd like a better way). I can take general inventory using the Food Inventory form. When I'm done figuring out what all I need, I go to the ShoppingList report, which breaks everything down by store and tries to sort by like items for ease of shopping.

 

[ELiedeker]

Ahh, I see that now. I just entered in 9/1/20 for the date and then got 37 main records. Then the Recipes list was fully populated. Got it now. Just wasn't intuitive in how it was supposed to be used. Put taco in search term, and just the taco recipes are listed in the dropdown.

I'm still not sure what that gains you as there are no actual recipes there, just the names of the recipes. As long as it is doing what you want, I guess that's all that matters.

Honestly, too, that form alone has done so much for me. It used to take me hours to come up with the week's menu. Now I have a place to keep track of dishes I want to try, and it's just a memory jog for stuff everyone likes that we didn't just eat last week. I tend to get in a rut.