Create SQL on the Fly

Developing a program which creates SQL on the fly can be a very useful if you want to generate data based on selection in a form.  I recently faced this problem where I needed to create a report which required the selection of multiple metrics in a report.  One way to do this is to make the SQL behind the form flexible enough to change based on the selection on a list box or the selection of a combo box or the ticking of an option button.

The following is a simple example, which is the building blocks for more complex examples explained in other articles.  I will start with a simple user form and add a list box with names in the list box.  Based on the selection of the names a Query will be produced which shows the names selected.

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 Create SQL on the Fly Part2.