Statements for Use in Modules

Modules contain declarations of variables (and sometimes constants), and Sub and function procedures that can be used anywhere in a database (for standard modules) or in a specific form or report (for Code behind Forms modules). Functions yield a return value, and Sub procedures don’t—at least theoretically. The idea is that you should use a Sub when you want to do something, and a function when you need a return value to work with elsewhere in your code. However, the lines are sometimes blurred in Access. For example, if you need to run a procedure from a macro, it has to be a function because only functions are selectable for the RunCode macro action. This means that if you need to run a Sub from a macro action (say, the AutoExec macro that runs when a database is opened), you need to convert it into a function, with a throwaway return value.

Statements are used in all types of modules to process data in a systematic way, using logical structures to control data flow. The following sections describe the most useful statements.

Call

The Call statement is used to run a Sub procedure, with the following syntax:

Call NewEmployeeID

You can omit the word “Call” and the line of code will still run, but in the interests of clarity I recommend leaving it in so that you know that a procedure is being called.

Do While . . . Loop

The Do While . . . Loop logical structure runs a code segment repeatedly so long as a condition is met. The function that follows (from the basUtilities module in the Outlook Data Exchange database for Chapter 12, Working with Outlook) strips nonalphanumeric characters from a text string, using a Do While . . . Loop structure to process the characters in the string one by one.

 Public Function StripChars(strText As String) As String On Error GoTo ErrorHandler    Dim strTestString As String    Dim strTestChar As String    Dim lngFound As Long    Dim i As Integer    Dim strStripChars As String    strStripChars = " ()-"    strTestString = strText        i = 1    Do While i <= Len(strTestString)      ‘Find a strippable character      strTestChar = Mid$(strTestString, i, 1)      lngFound = InStr(strStripChars, strTestChar)      If lngFound > 0 Then        strTestString = Left(strTestString, i - 1) & Mid(strTestString, i + 1)      Else        i = i + 1      End If    Loop    StripChars = strTestString     ErrorHandlerExit:    Exit Function ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Function  

Do Until . . . Loop

The Do Until . . . Loop logical structure runs a code segment repeatedly until a condition is met. The code segment below processes all records in a recordset based on an Access table, adding text to nodes of a treeview control. The code is from the tvwBooks_Fill function from frmTreeViewBookNotes in the EBook Companion sample database for Chapter 9, Reworking an Existing Application.

       Do Until rst.EOF          Debug.Print "Adding Level 1 item: " & rst![AuthorID]          strNode1Text = StrConv("Level1 - " & rst![AuthorID],              vbLowerCase)          Debug.Print "Node 1 text: " & strNode1Text          strVisibleText1 = rst![LastNameFirst]          Debug.Print "Level 1 visible text: " & strVisibleText1          Set nod = .Nodes.Add(Key:=strNode1Text,              Text:=strVisibleText1)          nod.Expanded = True          rst.MoveNext       Loop       rst.Close 

For . . . Next

The For . . . Next statement repeats processing for a specified number of times. Before the For Each . . . Next statement was added to Access VBA, you had to use the For . . . Next statement to process members of collections, but now it is rarely used. One example of the use of For . . . Next comes from the cmdSelectAll command button on the frmExportToWordDatasheet form in the Word Data Exchange sample database for Chapter 11, Working with Word. You need to use For . . . Next in this case because the rows in a listbox aren’t a collection.

 Private Sub cmdSelectAll_Click() On Error GoTo ErrorHandler        ‘Select all rows in listbox    Set lst = Me![lstSelectMultiple]    intRows = lst.ListCount - 1        For intIndex = 0 To intRows       lst.Selected(intIndex) = True    Next intIndex     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Sub 

For Each . . . Next

The For Each . . . Next statement is a variant of the For . . . Next statement, which processes all the members of a collection without having to know how many there are. The following code segment (from the FormExists function in basUtilities) uses a For Each . . . Next statement to process the items in the AllForms collection of the current project.

 On Error GoTo ErrorHandler    Dim prj As Object    Dim itm As Object        Set prj = Application.CurrentProject    For Each itm In prj.AllForms       If itm.Name = strForm Then          FormExists = True          GoTo ErrorHandlerExit       Else          FormExists = False       End If    Next itm ErrorHandlerExit:    Exit Function ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description    Resume ErrorHandlerExit End Function 

GoTo

The GoTo statement goes to a specific label in a procedure. The On Error GoTo ErrorHandler line that is used in my standard error handler goes to the ErrorHandler label when an error occurs.

If . . . Then . . . Else

An If . . . Then . . . Else statement lets you run one code segment if a condition is met and another code segment if the condition isn’t met. Optionally, you can add one or more ElseIf statements to an If . . . Then . . . Else statement to run a second (or further) code segment if the first condition isn’t met.

The command button event procedure listed below (from the standard main menu form created by my Menu Manager add-in, described in Chapter 6, Printing Data with Reports) has several nested If . . . Then . . . Else statements, one with an ElseIf clause:

 Private Sub cmdReports_Click() On Error GoTo ErrorHandler    Dim strReportName As String    Dim strRecordSource As String        If Nz(Me![cboReports]) <> "" Then       strReportName = Me![cboReports]       strRecordSource = Me![cboReports].Column(2)       If Nz(DCount("*", strRecordSource)) > 0 Then          If Me![fraReportMode] = 1 Then                DoCmd.OpenReport ReportName:=strReportName, view:=acPreview           ElseIf Me![fraReportMode] = 2 Then             DoCmd.OpenReport ReportName:=strReportName, view:=acNormal          End If       Else          MsgBox "No records for this report"          GoTo ErrorHandlerExit       End If    Else       Me![cboReports].SetFocus       Me![cboReports].Dropdown    End If ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Sub 

Select Case . . . End Select

The Select Case . . . End Select statement lets you respond to any number of different values of a field or control (or other alternatives) and (unlike the Switch function) doesn’t require that the responses are all of the same data type. Typically, in a line of code before the Select Case . . . End Select statement, a variable is set to a value picked up from a control on a form (or a field), and then a case is set up for each expected value of the field or control, possibly with a Case Else to handle other possibilities.

You can set a variable for each case, call procedures from the cases, or run code of various types. The Select Case statement that follows (from the fraFilter_AfterUpdate event procedure on the Dynamic Contact Search form in the sample Toy Workshop database, described in more detail in the “Sort and Filter Forms” section later in this chapter) responds to the user’s choice of a filter type in an option group. Depending on the choice, some controls on the form are enabled, others are disabled, and various other properties are set as needed.

    Select Case intFilter           Case 1       ‘Unfiltered       Me![cboCity].Enabled = False       Me![cboCity].ControlSource = ""       Me![cboCountry].Enabled = False       Me![cboCountry].ControlSource = ""       Me![cboCompany].Enabled = False       Me![cboCompany].ControlSource = ""       Me![cboLastMeetingDate].Enabled = False       Me![cboLastMeetingDate].ControlSource = ""       Me![cboSalary].Enabled = False       Me![cboSalary].ControlSource = ""       Me![cmdContacts].Caption = "Open Unfiltered Contacts Form"       Me![txtFilter].Visible = False       Me![subSearchResults].Form.RecordSource = "qryContacts"                    Case 2       ‘Filter by City       Me![cboCity].Enabled = True       Me![cboCity].ControlSource = "FilterValue"       Me![cboCity].SetFocus       Me![cboCity].Dropdown       Me![cboCountry].Enabled = False       Me![cboCountry].ControlSource = ""       Me![cboCompany].Enabled = False       Me![cboCompany].ControlSource = ""       Me![cboLastMeetingDate].Enabled = False       Me![cboLastMeetingDate].ControlSource = ""       Me![cboSalary].Enabled = False       Me![cboSalary].ControlSource = ""       Me![cmdContacts].Caption = "Open Filtered Contacts Form"       Me![txtFilter].Visible = True              Case 3       ‘Filter by Country       Me![cboCity].Enabled = False       Me![cboCity].ControlSource = ""       Me![cboCountry].Enabled = True       Me![cboCountry].ControlSource = "FilterValue"       Me![cboCountry].SetFocus       Me![cboCountry].Dropdown       Me![cboCompany].Enabled = False       Me![cboCompany].ControlSource = ""       Me![cboLastMeetingDate].Enabled = False       Me![cboLastMeetingDate].ControlSource = ""       Me![cboSalary].Enabled = False       Me![cboSalary].ControlSource = ""       Me![cmdContacts].Caption = "Open Filtered Contacts Form"       Me![txtFilter].Visible = True              Case 4       ‘Filter by Company Name       Me![cboCity].Enabled = False       Me![cboCity].ControlSource = ""       Me![cboCountry].Enabled = False       Me![cboCountry].ControlSource = ""       Me![cboCompany].Enabled = True       Me![cboCompany].ControlSource = "FilterValue"       Me![cboCompany].SetFocus       Me![cboCompany].Dropdown       Me![cboLastMeetingDate].Enabled = False       Me![cboLastMeetingDate].ControlSource = ""       Me![cboSalary].Enabled = False       Me![cboSalary].ControlSource = ""       Me![cmdContacts].Caption = "Open Filtered Contacts Form"       Me![txtFilter].Visible = True              Case 5       ‘Filter by Last Meeting Date       Me![cboCity].Enabled = False       Me![cboCity].ControlSource = ""       Me![cboCountry].Enabled = False       Me![cboCountry].ControlSource = ""       Me![cboCompany].Enabled = False       Me![cboCompany].ControlSource = ""       Me![cboLastMeetingDate].Enabled = True       Me![cboLastMeetingDate].ControlSource = "FilterValue"       Me![cboLastMeetingDate].SetFocus       Me![cboLastMeetingDate].Dropdown       Me![cboSalary].Enabled = False       Me![cboSalary].ControlSource = ""       Me![cmdContacts].Caption = "Open Filtered Contacts Form"       Me![txtFilter].Visible = True              Case 6       ‘Filter by Salary       Me![cboCity].Enabled = False       Me![cboCity].ControlSource = ""       Me![cboCountry].Enabled = False       Me![cboCountry].ControlSource = ""       Me![cboCompany].Enabled = False       Me![cboCompany].ControlSource = ""       Me![cboLastMeetingDate].Enabled = False       Me![cboLastMeetingDate].ControlSource = ""       Me![cboSalary].Enabled = True       Me![cboSalary].ControlSource = "FilterValue"       Me![cboSalary].SetFocus       Me![cboSalary].Dropdown       Me![cmdContacts].Caption = "Open Filtered Contacts Form"       Me![txtFilter].Visible = True           End Select 

With . . . End With

A With . . . End With statement lets you work with a variety of properties and methods of an object, without repeating its name. The code segment listed below is from the cmdImportDatafromWordTable_Click event procedure from the Word Data Exchange database for Chapter 11, Working with Word. It first sets an appWord variable to reference the Word instance, and then uses a With . . . End With statement to work with the Word Selection object.

    Set doc = appWord.Documents.Add(strDocName)    appWord.Visible = True    doc.Activate    With appWord.Selection       .GoTo What:=wdGoToTable, Which:=wdGoToFirst, Count:=1, Name:=""       lngStartRows = .Information(wdMaximumNumberOfRows)       Debug.Print "Total table rows: " & lngStartRows       .MoveDown Unit:=wdLine, Count:=1       .MoveRight Unit:=wdCell       .MoveLeft Unit:=wdCell    End With