Input parameters for query, text box, is there any other way?

[Regression]

All,

I am developing a database for a kitchen staff that is not very familiar with computers. I am trying to limit the possibility for errors as much as possible.

I am curious is there any other way to supply parameters to a query without typing them in the text box?

For instance if I am looking for Week 1 Meal 1 (Week and meal being separate fields in a table) is there a way to make this a drop down at the time the query is run rather than having it as [Enter Week:] then [Enter Meal:]?

I am afraid they will leave out the space or something else silly and not get the results they need to work.

Thanks

 

[grnzbra]

You could create a form with Week and Meal being combo boxes or list boxes and then set the criteria for a query equal to the values selected. What is supposed to happen when the selection is made? Is it supposed to generate a report. You could ad a line of code to the OnOpen event of the report which would open the form in the dialog mode. I use the following:

DoCmd.OpenForm "ReportCritInp", , , , , acDialog, Name()

ReportCritInp is the form name and the function Name() supplies the name of the report calling the form. (I have numerous reports being fed parameters from this same form so it needs to know which report called it. I use the function so that all I have to do for a new report is copy the line of code.)

The form will pop up, and the selection can be made. Then, click on the command button and parameters are set up (I use global variables) and the report will finish opening.

 

[vb6novice]

Regression,

I'm all about finding ways to keep users from doing something you don't want. If you want to give them as few things to have to get right as possible, give them a single combo box that has all the choices in it. Create a table that has the week numbers as its only field. Number them 1 to 52 (or 1 to 36) or whatever you need. Create another table with the meal number as its only field. Number them 1 to 3 (or however many meals you have for the day.

Create a query that has four columns: From the week numbers table, the week number, and a new field that has "Week " & [weeknumber] in the Field: row. From the mealnumbers table, the meal number, and a new field that has "Meal" & [weeknumber] in the Field: row.

Create a combobox on your form that has ColumnCount as 4, Bound Column as 1, and Column Widths as 0";1";0";1" (assuming a 2" wide combo box.

In the query that you want to pull up data for, make the criteria for the week number field "Forms![MyForm]![ComboBoxName].Columns(0)", and in the meal number field make the criteria "Forms![MyForm]![ComboBoxName].Columns(2)"

User will click the combo box, which will present all combinations of weeks and meals (the only chance for error is if the pick the wrong one). Your query will read the value of column 0 for the week criteria and column 2 for the meal criteria and give you the results the same as [Enter...].

Hope that helps.

 

[Regression]

Guys,

Excellent stuff. You got my mind working.

The main issue I was having was that the form I was working with was based off this query. So I was getting prompted to suppy the parameter before I amde any adjustments to the form.

What I did was create a new form that only has the Week Combo and Meal combo on it. I create a switchboard option to open this form. I then created an on update event for the meal field. This event runs a macro which opens my form needing the parameters. It grabs the parameters from this new selection form I created and opens the primary form that has the query as it's data source. It then closes the selection form.

Beautiful!

Thanks again!