How can I create a user interface for parameter queries?

[Gina Louise]

My database is essentially a big diary for lots of trainers so we can plan future training dates and record past training. There are a variety of queries we need to run on this data, e.g. look up the activity of a particular trainer during a specific month; look up the percentage of time spent physically in the training room, rather than other BAU tasks; look up the activity of a whole team of trainers for a certain time period...

I want to create a standard set of parameter queries so that my users (who are not familiar with Access) can easily look up information. However, many of the fields in my main table are lookups (e.g. the trainer name field looks up a list of trainers so the user gets a drop-down list to select from) so the data stored in that table are ID numbers (generated through autonumber) rather than the descriptive items my users will understand.

I thought I could get around this by taking the input for my parameter queries via a form. I added the relevant fields to a form (which, as I mentioned, are lookups so appear as drop-down boxes) and included buttons that run the desired query so, for example, my user selects the trainer whose data they wish to view and click a button to run the query. I have just realised that the resulting form is actually editing the data in my table, rather than just selecting from the various lookup options (which now seems pretty obvious given that's what forms are generally for!).

How can I create an interface for my users that allows them to easily select the parameter they want to input into the query?

[liimra]

Solution/

For the editing problem: Forms are usually used to create,edit & delete data whereas reports are used for viewing data. what you have to create is unbound form and then create combo boxes that will have the options. For example, you create one ComboBox for choosing trainers where its raw source would be all trainers, ... There might be another ComboBox for classes and so on.
Suppose you want to have time-frame as one of the criteria which users can choose from, then you might want to create two Date/ text fields.

Now I recommend that you create query based on the table and then a report based on the query you just created.

For viewing the data which the user chooses, you just add a button that opens the report/query with selected info and run this report based on the user selection (there will be a button for opening the report depending on users' parameters). For example, suppose you want the user to be able to see data related to one trainer (one criteria) and the name of the user's form is usrForm and the one for choosing the trainer is TrnControl. Then the OnClick event of the button will be:

 DoCmd.OpenReport "ReportName", acViewReport, "", "[Trainer]=[Forms]![usrForm]![TrnControl]", acNormal
End If

If you want two criteria; for example: the trainer and the date..Then

 DoCmd.OpenReport "ReportName", acViewReport, "", "[Trainer]=[Forms]![usrForm]![TrnControl] And [DateName] Between [Forms]![usrForm]![DateFieldOne] And [Forms]![usrForm]![DateFieldTwo]", acNormal

This shows Trainer activity for a specified period.

Suppose you only have two criteria, trainer and date where the user can use the trainer, the date, or both; then you can use this:

If (Eval("[Forms]![usrForm]![TrnControl] Is Not Null And [Forms]![usrForm]![DateFieldOne] Is Null And [Forms]![usrForm]![DateFieldTwo] Is Null")) Then
       
        DoCmd.OpenReport "ReportName", acViewReport, "", "[Trainer]=[Forms]![usrForm]![TrnControl]", acNormal
End If 

If (Eval("[Forms]![usrForm]![TrnControl] Is Null And [Forms]![usrForm]![DateFieldOne] Is Not Null And [Forms]![usrForm]![DateFieldTwo] Is Not Null")) Then
DoCmd.OpenReport "ReportName", acViewReport, "", "[Forms]![usrForm]![DateFieldOne] And [Forms]![usrForm]![DateFieldTwo]", acNormal
End If
If (Eval("[Forms]![usrForm]![TrnControl] Is Not Null And [Forms]![usrForm]![DateFieldOne] Is Not Null And [Forms]![usrForm]![DateFieldTwo] Is Not Null")) Then
DoCmd.OpenReport "ReportName", acViewReport, "", "[Trainer]=[Forms]![usrForm]![TrnControl] And [DateName] Between [Forms]![usrForm]![DateFieldOne] And [Forms]![usrForm]![DateFieldTwo]", acNormal
End If

and so on...

Hope this helps,

Regards,
Ali