One form to do all commands.

[jpv37]

I'm creating a database for my wife's recipes using Access.
I have three tables right now, the main recipe table, a category table, and a course table. I created the second two so she can search by category and course to bring up certain recipes. My question is, How do I create a form that will search all the tables by selected criteria. She wants to input Italian and have all the Italian recipes displayed.

Thanks

 

[PHV]

Have you tried the combo wizard ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PPJOSEPH]

Hi JPV37:
Why don't you create the two fields (category and course) in your main table and then run a simple query wizard.
Once you create the query, open a new form and click on form wizard, and in the box to the right of "Choose table or query where the object's data come from", use the drop down arrow and select the new query that you created. Follow the rest of the prompts.

For the second part of your question, how to query only italian:
I assume you have identified italian under category field,
then in the query, under the category column, in criteria you can use a parameter [ENTER CATEGORY] so when you open the form it will ask you for which category and based on which category you type in, those will be displayed in the form.

Finally, food for thought, why should she see all the italian reciepies on the form, why don't generate a report (preview) so she has an option to print it too.

You can add a new record in via form or edit an existing record.

 

[FancyPrairie]

The recipe table should contain a field that points to the category table and a field that points to the course table. Then, if your wife wants the category of Italian, you just need to search the category field within the recipe table. Your tables would look something like this:

tblCategory
lngCatID ...... autoNumber
strCategory ... Text

tblCourse
lngCourseID ... autoNumber
strCourse ..... Text

tblRecipe
lngRecipeID ... autoNumber (not really necessary)
memRecipe ..... memo
lngCatID ...... number (Long)
lngCourseID ... number (Long)

When you define the field for lngCatID within tblRecipe, select the tab at the bottom that says LookUp. Then set the Display Control to combobox. Then set the RowSource of the combobox to "Select lngCatID, strCategory From tblCategory Order By strCategory;". Then set the column count to 2 and the Column Widths to 0;1.

Do the same when you define the lngCourseID within tblRecipe.

Now when you open tblRecipe, you will be able to choose the category and course from a drop down. And you will be able to filter/search based on the category or course you want.

 

[jpv37]

Thanks for the responses.

I have my tables set up like Fancy said, they are related by the corresponding ID fields.

I was trying to make it so my wife wouldn't have to deal with the table views and such. She could open the form and find recipes according to what she typed in. Is there a way to display the results of the form in a report? Trying to make it easy for her.

 

[itsuit]

Not for nothin', but did you know there's a Recipes Collection template on the MS Office website?

 

[jpv37]

Thanks, looks pretty good.