Connecting the User Interface to the Code | Beginning Access 2007 VBA

You are now ready to tie everything you have done so far together by adding the VBA code to the Customer Search and View/Manage Customer Accounts forms. Most of this code will be event procedures that fire when different buttons are clicked. Some of the code will also be local procedures that deal with user-interface-specific features. It just did not make sense to put these in a standard or class module.

The Customer Search Form

An example of the Customer Search form, called frmSearch, is shown in Figure 14-27 with some sample data populated. Keep it in mind as you write the code for the form.

image from book
Figure 14-27

Try It Out-Writing Code for the frmSearch

As previously mentioned, you are now ready to write the VBA code that will finish up the application. Start with the frmSearch form, and finish with the frmCustomers form.

Open the frmSearch form and select the Form_Load event for the form to bring up the Visual Basic Editor. Add the following code to the form:

  Private Sub Form_Load()     On Error GoTo HandleError         'create new recordset     Set rsSearch = New ADODB.Recordset         Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, SEARCH_FORM, "Form_Load"     Exit Sub End Sub

Add the following code to the General Declarations section of the form:

  Option Compare Database Option Explicit Const SEARCH_FORM = "frmSearch" Dim rsSearch As ADODB.Recordset

Add the following Click event procedures for running the search:

  Private Sub cmdSearch_Click()     On Error GoTo HandleError         'run the search     Call RunSearch         Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, SEARCH_FORM, _             "cmdSearch_Click"     Exit Sub End Sub Sub RunSearch()     On Error GoTo HandleError         Dim strSQL As String         'get the SQL statement for the search     strSQL = GetSQL         'if the SQL statement was generated successfully     If strSQL <> "ERROR" Then         'execute the SQL statement against the         'database and put results in recordset         Set rsSearch = ProcessRecordset(strSQL)             'load the search results into the list on the form         PopulateListFromRecordset lstResults, rsSearch, 11         End If         Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, SEARCH_FORM, "RunSearch"     Exit Sub End Sub Function GetSQL() As String On Error GoTo HandleError     Dim strSQL As String     Dim strSQLWhereClause As String     Dim blnPriorWhere As Boolean         blnPriorWhere = False         'generate the first part of the SQL Statement     strSQL = BuildSQLSelectFrom()         '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     If txtFName <> "" Then         strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _                             txtFName, "FirstName")     End If     If txtCompany <> "" Then         strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _                             txtCompany, "Company")     End If     If txtAddress <> "" Then         strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _                             txtAddress, "Address1")     End If     If txtCity <> "" Then         strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _                             txtCity, "City")     End If     If txtRegion <> "" Then         strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _                             txtRegion, "Region")     End If     If txtPostalCode <> "" Then         strSQLWhereClause = BuildSQLWhere(blnPriorWhere, strSQLWhereClause, _                             txtPostalCode, "PostalCode")     End If     If blnPriorWhere Then         'build the final SQL statement with the Select…From…and          'Where clause         strSQL = strSQL & strSQLWhereClause     Else         MsgBox "You must enter at least one search criteria!"         strSQL = "ERROR"     End If         'return the SQL statement     GetSQL = strSQL     Exit Function HandleError:     GeneralErrorHandler Err.Number, Err.Description, SEARCH_FORM, "GetSQL"     Exit Function End Function

Add the following procedures for clearing the search results:

  Private Sub cmdClear_Click()     On Error GoTo HandleError         'clear the search fields     Call ClearFields         Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, SEARCH_FORM, _            "cmdClear_Click"     Exit Sub End Sub  Sub ClearFields()     On Error GoTo HandleError         'clear fields     txtCustomerNum = ""     txtLName = ""     txtF Name = ""     txtCompany = ""     txtAddress = ""     txtCity = ""     txtRegion = ""     txtPostalCode = ""     txtPhone = ""     Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, SEARCH_FORM, "ClearFields"     Exit Sub End Sub Private Sub Form_Unload(Cancel As Integer)     On Error GoTo HandleError         'reset lookup id to 0     intCustomerLookupId = 0         If Not rsSearch Is Nothing Then         'close recordset         rsSearch.Close         Set rsSearch = Nothing     End If             Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, SEARCH_FORM, "Form_Unload"     Exit Sub     End Sub Private Sub 1stResults_DblClick(Cancel As Integer) On Error GoTo HandleError    'store the value of the selected customer id to be    'used later by frmCustomers to open selected record    intCustomerLookupId = lstResults.Column(0)       'open frmCustomers    DoCmd.OpenForm "frmCustomers"     Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, SEARCH_FORM, _            "lstResults_DblClick"     Exit Sub End Sub

The View/Manage Customer Accounts Form

An example of the View/Manage Customer Accounts form, called frmCustomers, is shown in Figure 14-28 with some sample data populated. Keep this form in mind as you write the code.

image from book
Figure 14-28

Try It Out-Writing Code for the frmCustomers

Now that you have finished with the VBA code for the frmSearch form, you can turn to the frmCustomers form.

Open the frmCustomers form and select the Form_Load event for the form to bring up the Visual Basic Editor. Add the following code to the form:

  Private Sub Form_Load()     On Error GoTo HandleError         Set objCustomer = New clsCustomer     Set rsCustomer = New ADODB.Recordset         'lock the customer number field so user cannot modify it     '(since assigned by the database)     txtCustomerNum.Locked = True         'set addmode to false since not adding yet     blnAddMode = False         'load the records from the database     Call LoadRecords         'populate plans combo box     Call PopulatePlans     Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _             "Form_Load"     Exit Sub End Sub

Add the following code to the General Declarations section of the form:

  Option Compare Database Option Explicit Const CUSTOMERS_FORM = "frmCustomers" Dim objCustomer As clsCustomer Dim rsCustomer As ADODB.Recordset Dim rsHistory As ADODB.Recordset Dim blnAddMode As Boolean

Add the following code behind the form for adding a new customer record:

  Private Sub cmdAddNew_Click()     On Error GoTo HandleError         'clear the current controls to enable adding a new     'customer record     Call AddEmptyCustomerRecord         Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _            "cmdAddNew_Click"     Exit Sub End Sub Sub AddEmptyCustomerRecord()     On Error GoTo HandleError         'set add mode to true     blnAddMode = True         'clear the current values in the contacts object     objCustomer.ClearObject         'clear the current controls on the form so the     'user can fill in values for the new record     Call ClearCustomerControls         Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _            "AddEmptyCustomerRecord"     Exit Sub End Sub Sub ClearCustomerControls()     On Error GoTo HandleError         'clear the values in the controls on the form     Me.txtCustomerNum = ""     Me.txtLName = ""     Me.txtFName = ""     Me.txtMName = ""     Me.txtCompany = ""     Me.txtAddress1 = ""     Me.txtAddress2 = ""     Me.txtCity = ""     Me.txtRegion = ""     Me.txtPostalCode = ""     Me.txtWorkPhone = ""     Me.txtHomePhone = ""     Me.txtCellPhone = ""     Me.txtEmail = ""     Me.cboPlan = ""         'clear the plan history list box     lstPlanH istory.RowSource = ""         Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _            "ClearCustomerControls"     Exit Sub End Sub

Add the following procedures to the form for making updates to the data:

  Private Sub cmdSave_Click()     On Error GoTo HandleError         Dim intCurCustomer As Integer         'save the id of the current record if in update mode     If Not blnAddMode Then         intCurCustomer = objCustomer.CustomerId     Else         intCurCustomer = 0     End If             'populate object with current info on form         objCustomer.PopulatePropertiesFromForm             'save all changes to current record         objCustomer.Save blnAddMode, rsCustomer                 'move back to the customer that was current before the requery         If intCurCustomer > 0 Then             'move back to the contact that was just updated             rsCustomer.Find "[CustomerId] = " & intCurCustomer             'refresh the history list box for the record             Call RefreshHistory         Else             'if just added new record, move to the beginning of             'the recordset             Call MoveToFirstRecord(rsCustomer, objCustomer, blnAddMode)             'populate the controls on the form with the current record             Call PopulateCustomerControls         End If             Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _            "cmdSave_Click"     Exit Sub End Sub

Add the following code to the form for navigating through the customer records:

  Private Sub cmdMoveFirst_Click()     On Error GoTo HandleError          'move to the first record in the local disconnected recordset     Call MoveToFirstRecord(rsCustomer, objCustomer, blnAddMode)          'populate the controls on the form with the current record     Call PopulateCustomerControls         Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _            "cmdMoveFirst_Click"     Exit Sub End Sub Private Sub cmdMoveLast_Click()     On Error GoTo HandleError         'move to the last record in the local disconnected recordset     Call MoveToLastRecord(rsCustomer, objCustomer, blnAddMode)         'populate the controls on the form with the current record     Call PopulateCustomerControls         Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _            "cmdMoveLast_Click"     Exit Sub End Sub Private Sub cmdMoveNext_Click()     On Error GoTo HandleError         'move to the next record in the local disconnected recordset     Call MoveToNextRecord(rsCustomer, objCustomer, blnAddMode)         'populate the controls on the form with the current record     Call PopulateCustomerControls         Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _            "cmdMoveNext_Click"     Exit Sub End Sub Private Sub cmdMovePrevious_Click()         On Error GoTo HandleError         'move to the previous record in the local disconnected recordset     Call MoveToPreviousRecord(rsCustomer, objCustomer, blnAddMode)         'populate the controls on the form with the current record     Call PopulateCustomerControls         Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _            "cmdMovePrevious_Click"     Exit Sub End Sub

Add the following code to the form for populating the fields on the form with the current customer record:

  Sub PopulatePlans()   On Error GoTo HandleError     'populate the Plans combo box with values from the database     Dim rsPlans As New ADODB.Recordset         'populate the list of plans from the database     Set rsPlans = ExecuteSPRetrieveRS("spRetrievePlans", 0)     cboPlan.RowSource = ""     cboPlan.LimitToList = True     cboPlan.ColumnCount = 2     cboPlan.RowSourceType = "Value List"     cboPlan.BoundColumn = 0         Do While Not rsPlans.EOF         'populate the plans combo box         cboPlan.AddItem rsPlans!PlanId & ";" & rsPlans!PlanName         rsPlans.MoveNext     Loop     Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _            "PopulatePlans"     Exit Sub End Sub Sub LoadRecords()     On Error GoTo HandleError          'load the customer recordset     Set rsCustomer = objCustomer.RetrieveCustomers         'if the recordset is empty     If rsCustomer.BOF And rsCustomer.EOF Then         Exit Sub     Else         'populate the object with values in the recordset         objCustomer.PopulatePropertiesFromRecordset rsCustomer         Call MoveToFirstRecord(rsCustomer, objCustomer, blnAddMode)           'populate the controls on the form with the current record         Call PopulateCustomerControls         End If         Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, "LoadRecords"     Exit Sub End Sub Sub PopulateCustomerControls()         On Error GoTo HandleError         'Populate the controls on the Customers form with the values of the         'current record in the contacts object.         If Not rsCustomer.BOF And Not rsCustomer.EOF Then             Me.txtCustomerNum = objCustomer.CustomerId             Me.txtLName = objCustomer.LastName             Me.txtFName = objCustomer.FirstName             Me.txtMName = objCustomer.MiddleName             Me.txtCompany = objCustomer.Company             Me.txtAddress1 = objCustomer.Address1             Me.txtAddress2 = objCustomer.Address2             Me.txtCity = objCustomer.City             Me.txtRegion = objCustomer.Region             Me.txtPostalCode = objCustomer.PostalCode             Me.txtWorkPhone = objCustomer.WorkPhone             Me.txtHomePhone = objCustomer.HomePhone             Me.txtCellPhone = objCustomer.CellPhone             Me.txtEmail = objCustomer.Email             Me.cboPlan = objCustomer.PlanId             'refresh the history list box             Call RefreshHistory         ElseIf rsCustomer.BOF Then             'past beginning of recordset so move to first record             Call MoveToFirstRecord(rsCustomer, objCustomer, blnAddMode)         ElseIf rsCustomer.EOF Then             'past end of recordset so move back to last record             Call MoveToLastRecord(rsCustomer, objCustomer, blnAddMode)         End If         Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _            "PopulateCustomerControls"     Exit Sub End Sub

Add the following RefreshHistory procedure:

  Sub RefreshHistory()     On Error GoTo HandleError     'populate the recordset for plan history list     Set rsHistory = New ADODB.Recordset     'get plan history for current customer     Set rsHistory = ExecuteSPRetrieveRS("spRetrieveCustomerHistory", _                     objCustomer.CustomerId)     'Set rsHistory = objCustomer.RetrieveHistory(objCustomer.CustomerId)     PopulateListFromRecordset Me.lstPlanHistory, rsHistory, 5     rsHistory.Close                 Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, _            "RefreshHistory"     Exit Sub End Sub

Add the following code to the Form_Unload event procedure:

  Private Sub Form_Unload(Cancel As Integer)     On Error GoTo HandleError         'reset lookup id to 0     intCustomerLookupId = 0         If Not rsCustomer Is Nothing Then         'close recordset         rsCustomer.Close         Set rsCustomer = Nothing     End If             Exit Sub HandleError:     GeneralErrorHandler Err.Number, Err.Description, CUSTOMERS_FORM, "Form_Unload"     Exit Sub     End Sub

That is all the code for the application. So, it’s time now to resolve any typographical errors if you have not done so already.