Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / VB

Using Forms with Queries in Access

5.00/5 (1 vote)
18 Mar 2011CPOL2 min read 14.8K  
Using Forms with Queries in Access

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 opens up a report to show it while another would allow the user to review a previously created report. If you reference the forms directly, then 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.

image

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.

image

The click event for the view button looks this:

VB.NET
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.

image

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

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

Our query looks like this:

SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)