Steve listens and quickly understands requirements. He delivers a superior product on time, on budget and beyond the ask. I definitely recommend Steve's work!
Saturday, 12 March 2011 11:04 Last Updated on Saturday, 03 September 2011 11:13
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:
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.