Create SQL on the Fly Part2

An Access database where I work had grown so large by the time it landed on my desk that it was rendered virtually useless.  I was tasked with rebuilding the file and I had a think about how I could stop the file rebuild organically growing as a result of query creep.  What tends to happen is a query is developed for a one time use and left in the file forever.  This can happen hundreds of times depending on the size of the file.  Keeping track of these queries can be an utter nightmare as the database grows in size.

A thought struck me – what if I create a master form which contained all of the possible metrics.  Then we could generate all possible reporting combinations from a single Access database.  Now I just have to come up with the solution.  The fundamentals of this are outlined in the Create SQL on the Fly article.  

As you can see above the names are selected.  As you click on the button titled Run the following output is achieved.

The way this is created is I have a table and one query which feed into the user form and a query as the output from the userform.  The following is the relationship.

Working from top to bottom the Employees is the table with

  • First Name
  • Last Name
  • Address
  • City
  • State
  • Zip

For the purposes of this exercise and simplicity I will only use First Name and Last name. A query is created to join the First Name and Last Name called qryAllNames. The field name is FullName and the formula is;

FullName: [FirstName] & " " & [LastName]

A final query is set up which I have named qryNameSelect.  This draws on the qryAllNames Query and has just one field (FullName).

Next set up a form with a list box on the form.  Ensure the listbox’s source is qryAllNames, the first query which was made.

The final task is to create a button which will trap all of the items in the list box which are clicked on and output these items to the Query qryNameSelect.

The code is a little complex but I will talk through the parts which need to change.

Option Explicit

Private Sub cmdRunMster2_Click()
Dim frm As Form
Dim ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim qrynme as string

'Name of the Query to draw from
qrynme=“qryNameSelect”

Set frm=Forms!frmEmpChoice
Set ctl=frm!LstEmp
Set db=CurrentDb
Set qdf=db.QueryDefs(Qrynme)
strSQL="Select * From qryAllNames where [FullName]="

'Loop through each item selected in the list box
For Each varItem In ctl.ItemsSelected
strSQL=strSQL & """" & ctl.ItemData(varItem) & """ OR [FullName]="
Next varItem
If strSQL="Select * From qryAllNames where [FullName]=" Then
MsgBox "Select some people"
Exit Sub
End If
'Trim the end of strSQL
strSQL=Left$(strSQL, Len(strSQL) - 16)
strSQL=strSQL & """" & ";"
qdf.SQL=strSQL
DoCmd.OpenQuery Qrynme

Set qdf=Nothing
Set db=Nothing

End Sub

The string qrynme is the name of the query the procedure will manipulate. Basically the SQL for qryNameSelect is being re written each time the procedure runs based on the items selected in the list box.

This technique can be taken further still. I have built on this theory in the following article called Name of article.