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.

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.