|
|
|||
| | Feedback | | |||
It is often sufficient to use query parameters 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. Using an Access form will allow 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 (2000) makes this task complicated. It is not possible to directly refer to the form field values and it is necessary to incorporate the form fields within conditional query formulas using IIF() or CHOOSE(). For example, instead of simply calling for a formname!fieldname value, it is necessary to declare IIF(formname!fieldname=X, X, formname!fieldname).
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.
 |
||||
 |
||||
| Field: | Group_Type | Booking_Ref | F_From: IIf([for | ChkFrom: IIf([fo |
| Table: | UserGroups | Bookings | ||
| Sort: | ||||
| Show: | Y | Y | Y | Y |
| Criteria: | Like Choose([forms]![f | "N" | ||
| Or: | ||||
| Field | Table | Criteria |
| Group_Type | UserGroups | (below) |
| Like Choose([forms]![fqp]![optiongrp_users],"YOUTH","CLUBS","*") | ||
| BOOKING_REF | Bookings | |
| USERGROUP_NAME | Bookings | |
| INVOICE_AMOUNT | Bookings | |
| F_From: IIf([forms]![fqp]![fld_fromref]=1000, 1000,[forms]![fqp]![fld_fromref]) | ||
| F_To: IIf([forms]![fqp]![fld_toref]=6000, 6000, [forms]![fqp]![fld_toref]) | ||
| ChkFrom: IIf([forms]![fqp]![fld_fromref]<= [booking_ref],"N","Y") | "N" | |
| ChkTo: IIf([forms]![fqp]![fld_toref]>= [booking_ref],"N","Y") | "N" | |
| F_OptGrp: Choose([forms]![fqp]![optiongrp_users], 1,2,3) | ||
| F_Totals: IIf([forms]![fqp]![check_printtotalsonly]=True,"Y","N") | ||
Query fields such as F_From and F_To are used to retrieve and contain values read from the form (the first and last booking reference numbers). Query fields such ChkFrom and ChkTo read the same form field values but then compares them against the current record and returns a 'Y' or 'N' depending on whether the value is inside the required range.
|
|||||||||||
| Group_Type | Bkg Ref | User Group | Invoice £ | Inv Pd on | F_From | F_To | ChkFrom | ChkTo | F_OptGrp | F_Totals | |
| CLUBS | 2445 | HIGH FLYERS ATHLETICS | £25 | 10/09/05 | 2445 | 2450 | N | N | 3 | Y | |
| OTHER | 2446 | ST MARTINS CHURCH CHOIR | £184 | 28/10/05 | 2445 | 2450 | N | N | 3 | Y | |
| YOUTH | 2448 | CAPED CRUSADERS CLUB | £211 | 11/11/05 | 2445 | 2450 | N | N | 3 | Y | |
| YOUTH | 2449 | CHELSFIELD GIRL GUIDES | £21 | 11/11/05 | 2445 | 2450 | N | N | 3 | Y | |
Would you like to learn more?
Access 2000 developers handbook volume 1
The F_Totals 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. Clubs, Youth, Other). If the value is "Y" 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 F_Totals = "Y" 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 to be generated.
| file: accformparams.htm | Page last updated Jul06 | © MeadInKent.co.uk 2006 |  |