| Feedback | Things to do with Queries | |
Query parameters can be configured to prompt a user to enter variables at run time. Simple dialog boxes can ask for numbers or text which can be used in a query as criteria or as temporary run-time field values. Sometimes however it may be helpful to have a more versatile and sophisticated method of collecting the variables. An Access form can contain text fields with default values, option groups, drop down lists and enable some reasonableness checking on the responses before the query is generated.
In this example a form has been created which will allow a user to enter a range of booking reference numbers, to specify a type of organisation making the booking and to declare whether or not a report (based on the query data) displays only total rows and no detail.
Access can make this task complicated. It is not always possible to directly refer to the form field values and it may be necessary to incorporate the form fields within functions such as VAL(), IIF() or CHOOSE(). For example, instead of simply calling for the result of the option group a [forms]![fqp]![optiongrp_users] value, it is necessary to declare Val([forms]![fqp]![optiongrp_users]).
The query definition (below) obtains some values from a form (named 'fqp') and will therefore not work if the form is closed or in design mode. In that situation it would generate a series of unwanted user prompts asking for the missing variables.
UserGroups_BookRefs: Select query (design) | ox | |||
Field: | Booking_Ref | Group_Name | Group_Type | Ref_Crit: "BR " & [forms]![fq |
Table: | Bookings | UserGroups | UserGroups | |
Sort: | ||||
Show: | Y | Y | Y | Y |
Criteria: | Between [forms]![fqp]![ | Like Choose([forms]![fq | ||
Or: | ||||
Field | Criteria |
BOOKING_REF | Between [forms]![fqp]![fld_fromref] And [forms]![fqp]![fld_toref] |
GROUP_NAME | |
GROUP_TYPE | Like Choose([forms]![fqp]![optiongrp_users],"YOUTH","COMMUNITY","*") |
Ref_Crit: "BR " & [forms]![fqp]![fld_fromref] & " - " & [forms]![fqp]![fld_toref] | |
Grp_Crit: Choose([forms]![fqp]![optiongrp_users],"1 Youth Only","2 Clubs only","3 All users") | |
Pr_Crit: IIf([forms]![fqp]![check_printtotalsonly]=0,"No","Yes") | |
Grp_OptNo: Val([forms]![fqp]![optiongrp_users]) | |
Pr_OptNo: Val([forms]![fqp]![check_printtotalsonly]) |
The last 5 fields (shaded in yellow) are for information or to demonstrate the option group and tick box values. The values returned by an option group are a simple sequence of 1,2,3 ... The tick box returns a value of either 0 for blank or -1 for ticked.
userGroups_BookRefs: Select query | ox | ||||||
Bkg_Ref | Group_Name | Group_Type | Ref_Crit | Grp_Crit | Pr_Crit | Grp_OptNo | Pr_OptNo |
2441 | SUBSTANTIAL THEATRE GROUP | COMMUNITY | BR 2441 - 2445 | 3 All users | Yes | 3 | -1 |
2442 | ST MARTINS CHURCH CHOIR | COMMUNITY | BR 2441 - 2445 | 3 All users | Yes | 3 | -1 |
2443 | HIGH FLYER ATHLETICS | SPORT | BR 2441 - 2445 | 3 All users | Yes | 3 | -1 |
2444 | MR AND MRS JONES | PRIVATE | BR 2441 - 2445 | 3 All users | Yes | 3 | -1 |
2445 | HIGH FLYERS ATHLETICS | SPORT | BR 2441 - 2445 | 3 All users | Yes | 3 | -1 |
The Pr_Crit field has been included to affect the formatting of a report which uses this query as it's record source. The report will display a sub-total for each type of group that place bookings (i.e. Community, Sport, Other). If the value is "Yes" then only the report Group Footer totals will be shown and the detail sections will be hidden (by setting their visible property to FALSE).
Within the report design, select the 'Detail' section properties and edit the 'On Format' event. Then add the following lines using the code builder:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) If Pr_Crit = "Yes" Then Detail.Visible = False Else Detail.Visible = True End If End Sub |
Note again that the form containing the query parameters must be open when the report is generated.
file: accformparams.htm | Page last updated Mar14 | © MeadInKent.co.uk 2014 |