BaldyWeb

A Tutorial on Building SQL in VBA, part 2

Referencing form controls

--The Basics--

All of the earlier examples used "hard-coded" variables. This is good for demonstration purposes, but it is usually the case that you want to gather information from the user, and use those values in your SQL statement.

We're going to change the examples a bit because we want to build up the search form pictured earlier.

Every bit of the SQL statement can be constructed in VBA, but let's look first at simply using some form control values to construct a simple WHERE clause:
Code:
Private Sub cmdSearch_Click()
    Dim strSQL As String
    Dim rs As DAO.Recordset
    
    strSQL = "SELECT * FROM tblEmployee " & _
            "WHERE [EmployeeName] = " & Chr$(39) & Me.txtEmployeeName & Chr$(39)
            
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    Debug.Print strSQL
    Debug.Print rs![EmployeeName]
    
End Sub
SELECT * FROM tblEmployee WHERE [EmployeeName] = 'john'
John

Just like we saw before. Just a couple of points to make: one, note the "john" in the SQL statement is all lowercase, but the returned values is as it is stored in the table -- Jet is case-insensitive. Second, I use the Me object to get a reference to the current form. For more about referencing forms, please see This handy reference. The point is the form reference, when constructed in VBA, needs to be "outside" the SQL string. This can cause confusion if you are working out your SQL in the QBE grid and blindly cut and paste it into VBA code.

Now, leaving it here alone covers the basics, but this is a good a place as any to talk about creating a search form - a very common requirement that can require some tricky VBA.

--The Search Form--

On our search form, we want to allow a couple of "special" things. If the user checks the "Contains" checkbox, we want to allow partial name matches. If the user leaves a control empty, we want to ignore that criteria. If the user puts in a starting DOB, but not an ending DOB we want all DOB equal to or after the starting DOB, else limit the range as expected. We also want to allow the user to specify the sort order of the returned recordset.

Finally, we want to construct the VBA string and use it to set our subform's recordsource, showing only the matching records. This is one basic search form approach.

The database is attached for download (A2K format), but we'll briefly go over the relevant source here:
Code:
Private Sub cmdSearch_Click()
    Dim strSQLHead      As String
    Dim strSQLWhere     As String
    Dim strSQLOrderBy   As String
    Dim strSQL          As String
    Dim strJoin         As String
    
    strJoin = " AND "
    strSQLHead = "SELECT * FROM tblEmployee "
    
    If Len(Me.txtEmployeeName & vbNullString) Then
        If (Me.chkLike) Then
            strSQLWhere = "WHERE [EmployeeName] Like " & Chr$(39) & "*" & _ 
                                       Me.txtEmployeeName & "*" & Chr$(39)
        Else
            strSQLWhere = "WHERE [EmployeeName] = " & Chr$(39) & _
                                       Me.txtEmployeeName & Chr$(39)
        End If
    
        strSQLWhere = strSQLWhere & strJoin
    
    End If
   
    If Len(Me.txtDOBStart & vbNullString) Then
        If Len(strSQLWhere) = 0 Then
            strSQLWhere = "WHERE "
        End If
    
        If Len(Me.txtDOBEnd & vbNullString) Then
            strSQLWhere = strSQLWhere & "[EmployeeDOB] Between #" & Me.txtDOBStart & _
                                     "# AND #" & Me.txtDOBEnd & "#"
        Else
            strSQLWhere = strSQLWhere & "[EmployeeDOB] >= #" & Me.txtDOBStart & "#"
        End If
    
        strSQLWhere = strSQLWhere & strJoin
    
    End If
    
    If Len(Me.cboPosition & vbNullString) Then
        If Len(strSQLWhere) = 0 Then
            strSQLWhere = "WHERE "
        End If
        
        strSQLWhere = strSQLWhere & "[EmployeePosition] = " & Me.cboPosition
        
        strSQLWhere = strSQLWhere & strJoin
    End If
   
    If Len(strSQLWhere) Then
        strSQLWhere = Left$(strSQLWhere, Len(strSQLWhere) - (Len(strJoin) - 1))
    End If
    
    strSQLOrderBy = "ORDER BY "
    Select Case Me.fraOrderBy
    Case 1
        strSQLOrderBy = strSQLOrderBy & "[EmployeeName]"
    Case 2
        strSQLOrderBy = strSQLOrderBy & "[EmployeeDOB]"
    Case 3
        strSQLOrderBy = strSQLOrderBy & "[EmployeePosition]"
    End Select
    
    strSQL = strSQLHead & strSQLWhere & strSQLOrderBy
    
    Me.fsubEmployee.Form.RecordSource = strSQL
    
End Sub
The overall structure works like this: examine each control in turn and modify the SQL string as appropriate. Some controls need nested If...Then statements to handle the possibilities we outlined earlier (like what to do if the ending DOB is not given).

There are a couple of points worth special mention:
1. Note the careful planning of how to build the string.

The first control, if selected, will automatically insert the word "WHERE" in to strSQLWhere. However, all subsequent controls must first check and see if there is already something in the where string. If not, they must add the "WHERE".

Note each control automatically adds the " AND " to its part of the string. Then we strip off the last " AND ", leaving one trailing space for the ORDER BY clause. This careful planning and consistency in where to put the spaces makes the code easier to follow and modify.

2. The code handles "missing" information.

Even if no WHERE clause is present, the code is able to return all results. The code does not error if no results are returned (although in a real application, you may want to handle both of these problems more robustly).

It is easy to make a mistake that leads to code dependent on certain user input or in a certain order. This is part of the planning discussed in the first point.

3. Sufficient variable usage.

You could do all this code with quite a few less variables (you could do it with none!), but, by breaking down the SQL string into certain parts (the head, the WHERE clause, the ORDER BY clause), it becomes very easy to insert Debug.Print statements and check each part of the string for errors.

It would be very easy to add more controls to search more fields or modify the behavior of this procedure.

Some final points; this is just an illustrative sample. Real search forms are often more complex and sometimes better served by moving all the SQL string building into its own function (instead of the command button event handler as we see here).

Home