Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Using Forms with Queries in Access

, 18 Mar 2011
Rate this:
Please Sign up or sign in to vote.
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:

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.

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

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

License

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

Share

About the Author

Steve L. Powell
Software Developer Excel Adviser
Canada Canada
I am a freelance software developer with a variety of different interests. My main area of expertise is Microsoft Office add-ins (Excel/Outlook mostly) but I also develop Windows applications, Access Databases and Excel macros . I develop in VBA, C# and C++. My website exceladviser.com has articles on Excel, Access, Microsoft Office development, and general Windows programming (WPF, etc.).

Comments and Discussions

 
GeneralMy vote of 5 Pinmemberjustineclement21-Mar-11 23:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140926.1 | Last Updated 18 Mar 2011
Article Copyright 2011 by Steve L. Powell
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid