Beginning Access™ 2007 VBA

14.7. Touring the Finished Customer Service Application

Now that you have written all the code for the application, let's walk through how it works in a bit more detail. Let's start with the frmSearch form, which builds a SQL statement dynamically based on the search criteria input by the user. To see how this SQL Statement is dynamically built, start by adding a breakpoint to the cmdSearch_Click event of frmSearch, as shown in Figure 14-29.

Next, open the frmSearch form to run the form. Enter some criteria into the form. It is okay at this point if you do not have any customer records in the database. You just want to see how the SQL statement gets built dynamically. When you click the Search button, code execution should stop at the breakpoint you set in Figure 14-29.

Step through the code by selecting Debug Step Into (or by pressing F8). The RunSearch procedure is called and then the GetSQL function is called to generate the SQL statement from the values you entered on the form. The GetSQL function is the heart of the code that builds the dynamic SQL statement. Let's see in more detail how it works.

Function GetSQL() As String

On Error GoTo HandleError

    Dim strSQL As String
    Dim strSQLWhereClause As String
    Dim blnPriorWhere As Boolean

After you declare some variables, you set the blnPriorWhere value to False because no WHERE clause added yet.

blnPriorWhere = False

Next, the Select ...