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.

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 statement is generated based on the BuildSQLSelectFrom procedure.

 'generate the first part of the SQL Statement strSQL = BuildSQLSelectFrom()

A series of statements for each of the search controls is located on the form. Each control is tested to see if a value is present. If some are present, the BuildSQLWhere procedure is called to add the criteria in the particular control to the WHERE clause of the SQL statement. In the following code, I list statements for only a few of these controls.

 'build the where criteria based on the criteria filled in 'by the user in one or more of the search fields on the form If txtCustomerNum <> "" Then     strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _                         txtCustomerNum, "CustomerID") End If If txtPhone <> "" Then     strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _                         txtPhone, "Phone") End If If txtLName <> "" Then     strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _                         txtLName, "LastName") End If

Keep stepping through the code in the Visual Basic Editor until you are in the BuildSQLWhere function, as shown here.

 Function BuildSQLWhere(blnPriorWhere As Boolean, strPriorWhere As String, _          strValue As String, strDbFieldName As String) As String       On Error GoTo HandleError     Dim strWhere As String

If this is the first WHERE clause being added to the SQL statement (that is, it is the first control on the form that has criteria), then you must add the WHERE keyword to the SQL string. Otherwise, an AND statement is added to separate the existing WHERE clause correctly.

 If blnPriorWhere Then     'add to the existing where clause     strWhere = strPriorWhere & " AND " Else     'create the where clause for the first time     strWhere = " WHERE " End If

You must handle the Phone field in a special way, because you want the user to be able to type a single phone number for the customer but have the application search all the phone fields in the database. Thus, if the phone field is specified, you must add the WHERE clause that will search all three phone fields in the database.

 If strDbFieldName = "Phone" Then     'search each of phone fields in the db for this value to see     'if exact match or starts with this value for any one of the     'phone fields     strWhere = strWhere & "(HomePhone LIKE '" & PadQuotes(strValue) & "%' " & _         " OR WorkPhone LIKE ' " & PadQuotes(strValue) & "%' " & _         " OR CellPhone LIKE ' " & PadQuotes(strValue) & "%')"

Otherwise, you add the WHERE clause using the value that was passed in along with the LIKE keyword. The LIKE keyword will locate a match if the field begins with or matches the value input by the user. This saves time because the user does not have to type the entire field, although he can do so if he wants.

 Else     'build where clause using LIKE so will find both exact     'matches and those that start with value input by user     strWhere = strWhere & strDbFieldName & " LIKE ' " & PadQuotes(strValue) & _                "%' " End If

Now that there is a prior WHERE clause, the blnPriorWhere value is set to True:

 blnPriorWhere = True

The SQL statement is then returned to the calling procedure:

     'return where clause     BuildSQLWhere = strWhere     Exit Function HandleError:     GeneralErrorHandler Err.Number, Err.Description, DB_LOGIC, "BuildSQLWhere"     Exit Function End Function

If you press F5 to allow the code execution to continue, you see a Search Results screen similar to that shown in Figure 14-30.

Figure 14-30

In Figure 14-30, the search criteria that the user specified includes a portion of a last name and a company name. After you built the SQL string in the manner described previously, you executed the SQL statement against the database and the resulting records were displayed in the Search Results list. If you double-click an item in the Search Results list, the View/Manage Customer Account screen is displayed for that specific record, as illustrated in Figure 14-31.

Figure 14-31

In this instance, the only customer record open on the View/Manage Customer Accounts is the selected customer. This feature is useful in many scenarios, such as when the user works in a call center and needs to retrieve the customer’s record quickly based on limited information. When he or she enters the search criteria and locates the correct customer record, the user can then open the customer account and view the information. In the example illustrated in Figure 14-31, the customer was first on a local plan but then switched to a national plan shortly thereafter.

If the View/Manage Customer Accounts screen is opened independently of the Customer Search screen, multiple records are displayed and the navigation buttons allow the user to navigate through the customer records. The screen appears to be the same as the one shown in Figure 14-31, only the values in the screen change for the current customer record.

If changes are made to the customer information, the database is updated with the new data. The database updates to an existing customer record are made through the spUpdateCustomer stored procedure created earlier. Stored procedures are used to retrieve and update data for the entire application, except for the dynamic SQL statement executed from the Customer Search screen.

For those stored procedures that required parameters, you first had to add parameters to the ADO Command object that match the parameters expected by the stored procedure. An example of adding parameters using the CreateParameter method is shown in the following excerpt from the AddParameters procedure that performs an update to an existing customer record:

 Sub AddParameters(strSPname As String, cmdCommand As ADODB.Command, _          objCust As clsCustomer)     On Error GoTo HandleError     Dim parParm As ADODB.Parameter     'if updating existing record     If strSPname = "spUpdateCustomer" Then         'Add parameter for existing Customer Id to be passed to stored         procedure         Set parParm = cmdCommand.CreateParameter("CustomerId", adInteger, _                       adParamInput)         cmdCommand.Parameters.Append parParm         parParm.Value = objCust.CustomerId   End If     'Add parameter for Last Name to be passed to stored procedure     Set parParm = cmdCommand.CreateParameter("LastName", adVarChar, _                   adParamInput, 50)     parParm.Value = objCust.LastName     cmdCommand.Parameters.Append parParm     'Add parameter for First Name to be passed to stored procedure     Set parParm = cmdCommand.CreateParameter("FirstName", adVarChar, _                   adParamInput, 50)     parParm.Value = objCust.FirstName     cmdCommand.Parameters.Append parParm

After all the required parameters are added, or if none is required for the stored procedure, the stored procedure can be executed using the Execute method of the Command object. The following example illustrates a portion of code from the ExecuteStoredProcedure procedure that runs the stored procedure after calling the AddParameters procedure:

 'set the command to the current connection Set cmdCommand.ActiveConnection = cnConn

 'set the SQL statement to the command text cmdCommand.CommandText = strSPname 'execute the command against the database cmdCommand.Execute

After specifying any required parameters for the stored procedure, you just assign the CommandText property of the Command object to the name of the stored procedure and then run the Execute method. Learning how to retrieve data from SQL server databases and how to correctly execute SQL Server stored procedures from your VBA code are probably the two hardest parts of working with SQL Server from Access. You have just learned how to do so.