Unanswered: How can I update two table fields with one choice on a form?
I'm sure this is SOOO basic, but I've searched this forum as well as the Access help and I'm not getting there.
I'm building a recipe database in Access 2000. You can enter in all your recipes on a form (which has a sub-form with the list of ingredients). Then, I'm trying to build a weekly menu table and form that will allow you to choose your meals for the week. The recipes have Recipe IDs which is the primary key. When I do the weekly menu, though, I don't care about the ID - I just want to choose by recipe name. When I choose the recipe name, though, I want it to populate both the name and the ID field in the weekly menu table. I'm thinking there MUST be a way to do that.
More specifically, in the weekly menu table, I have a field for the day and then the following fields:
Main Course ID
Main Course Name
Side 1 ID
Side 1 Name
The main course name is set up as a lookup field against the recipe table based on certain categories (meat, fish, soup, etc.) I'm trying to figure out how I can pick the main course name on the Weekly Menu form so that it will populate both the Main Course Name and the Main Course ID fields in the table. (I'll need the ID so that I can pull a shopping list of the ingredients later.)
Please note that I know NOTHING of VB coding. So far, I'm doing everything with Access functionality. Even with Access I'm definitely a beginner, so the more step by step you're willing to give the better.
You're having trouble finding it because you shouldn't really do it. Just store the ID in the second table, then join the 2 tables together in a query to get the name from the recipe table. Storing the name as well would be considered poor design and a violation of normalization rules.