ACC: How to Use Optional Arguments

MORE INFORMATION

   Sub MyTest(strTest1 As String, _
       Optional Test2 As Variant, _
       Optional Test3 As Variant)
				

    Sub MyTest(strTest1 As String, _
       Optional Test2 As String = "Test2", _
       Optional Test3 As String = "Test3")
				

Example 1

1. Create a module and type the following line in the Declarations section if it isn't already there:

         Option Explicit

2. Type the following procedure:

         Function CallEmployeeInfo()
            If Forms!employees!Title <> "Sales Representative" Then
              EmployeeInfo Forms!employees!FirstName, Forms!employees!LastName
            Else
              EmployeeInfo Forms!employees!FirstName, _
              Forms!employees!LastName, Forms!employees!Title
            End If
         End Function
   
         Sub EmployeeInfo(fname, lname, Optional Title)
            If IsMissing(Title) Then
               MsgBox lname & ", " & fname
            Else
               MsgBox lname & ", " & fname & "   " & Title
            End If
   
         End Sub
   						

3. Open the Employees form in Form view.
4. To test this function, type the following line in the Debug window, and then press ENTER:

   ? CallEmployeeInfo()

   Note that the message box opens. If the title is not Sales Representative, then the option title argument is not sent to the Sub routine but the Sub routine will still process. It just displays the two arguments that were provided. If the title is Sales Representative, the argument title is sent and displayed in the message box.

Example 2

1. Create a module and type the following line in the Declarations section if it isn't already there:

        Option Explicit

2. Type the following procedure:

         Sub OptionalTest(Optional Country)
            Dim dbs As DATABASE, rst As Recordset
            Dim strSQL As String
   
            ' Return Database variable pointing to current database.
            Set dbs = CurrentDb
            If IsMissing(Country) Then
               strSQL = "SELECT * FROM Orders"
               'This will return all the records
            Else
               strSQL = "SELECT * FROM Orders WHERE [ShipCountry] = '" &_
               Country & "';"
            'This will return only values matching the argument you entered.
            End If
            Set rst = dbs.OpenRecordset(strSQL)
            rst.MoveLast
            Debug.Print rst.RecordCount
   
         End Sub
   						

3. Type the following line in the Debug window, and then press ENTER:

   OptionalTest "UK"

   Note that the record count will return the number of records where the ShipCountry is equal to UK.
4. Type the following line in the Debug window, and then press ENTER:

   OptionalTest

   Note that you receive a record count for the whole table. This is because the optional argument was not supplied when the Sub procedure was called. This feature can be very useful for setting up criteria for queries.