Using Forms with Queries in Access

Saturday, 12 March 2011 11:04 Last Updated on Saturday, 03 September 2011 11:13 Written by Steve Powell

A common situation in Microsoft Office is the need to supply values from forms to queries as arguments. One way to do this is by referencing the form directly in the query. In the example below the criteria for ClientID would be [Forms]![View Invoice Form]![ClientIdList] which refers to a combo box on the "View Invoice Form".

The disadvantages of using this technique are:

1. If you move the form around the query may not work anymore. E.g. if you take the form and embed it into another form then you will need to change it to something like: [Forms]![Parent Form]![Navigation Subform]![Some Control].
2. The query can only easily refer to the values in one form. For instance, a query that is used in a report that can be opened by two forms would be difficult to achieve. One form might create an invoice than open up a report to show it while another would allow the user to review a previously created report. If you reference the forms directly than you would need two almost identical copies of the report.
3. It's inflexible in that you cannot change the filter. For instance you might want the user to be able to use date fields, a list of categories or a numeric field. If the query refers to the controls then it becomes more difficult to only use a subset of fields.

Exhibit 1: The criteria for ClientID is set to [Forms]![View Invoice Form]![ClientIdList] in this example. It's not easy to have a different criteria depending on options selected by the user or use the query for different reports/forms.

The solution is to leave out the criteria altogether from the design of the query and create the filter in an event using VBA. For example suppose you have a small form like exhibit 2 that opens up a report (an invoice) when you choose a client and invoice number and click "View".

Exhibit 2: The view invoice form.

The click event for the view button looks this:

Private Sub OkButton_Click() 
	DoCmd.OpenReport "View Invoice", acViewReport,, _
	"Invoices.ClientId=" & ClientIdList.Value & _
	" AND Invoices.InvoiceNumber=" & InvoiceNo.Value 
End Sub 

We can also have another form for creating invoices that has a different set of fields and creates the invoice record before opening up the report.

Exhibit 3: The create invoice form.

The click event for this form will use a variable "InvoiceNo" which was obtained from the query used to create the invoice record.

DoCmd.OpenReport "View Invoice", acViewReport, , _
"Invoices.ClientId=" & ClientIdList.Value & _
 " AND Invoices.InvoiceNumber=" & InvoiceNo 

The query looks like this:

SELECT DISTINCT Log.WorkDate, Log.Hours, Log.Description,
IIf(Log.Hours=0,0,Clients.HourlyRate) AS HourlyRate, 
IIf([Hours]=0,Log.Cost,[HourlyRate]*[Hours]) AS Amount, 
Clients.Terms AS ClientTerms, Clients.ID, Clients.ClientName,
Clients.[Mailing Address], Invoices.InvoiceNumber AS 
InvoiceNumber, Invoices.ClientId, Invoices.InvoiceNumber, 
Invoices.EndDate AS InvoiceDate, Taxes.Rate AS TaxRate, 
Invoices.Paid AS Paid FROM Clients, Log, Invoices, Taxes 
WHERE (((Log.WorkDate) Between [Invoices]![StartDate] 
And [Invoices]![EndDate]) AND ((Clients.ID)=[Log].[ClientID]) 
AND ((Invoices.ClientId)=[Log].[ClientId] ) AND 
Invoices.EndDate >= Taxes.StartDate AND 
(Invoices.StartDate <= Taxes.EndDate 
OR Taxes.EndDate is NULL)); 

It's not the easiest SQL query to read but the WHERE clause doesn't contain any mention of invoice numbers or client IDs so the forms are just providing some additional filtering and we keep the SQL as simple as possible.