MeadInKent
| Feedback | Things to do with Queries |

Getting Access database query values from a form

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.

a form named 'fqp' which supplies values to a query

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
  Source
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

Access 2010 VBA Programming

 
UK more ...   USA more ...

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 View a selection of recommended books on Access