ACC2000: How to Use Optional Arguments

ID: Q210179

The information in this article applies to:

Microsoft Access 2000

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).

SUMMARY

This article shows you two examples of how to create and use procedures with optional arguments.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

When you declare a function argument using the Optional keyword, you can give the optional variable a default value. For example:


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

However, the IsMissing() function requires that the Optional argument be declared as a Variant. Further, if the Optional keyword is used, all subsequent arguments in the argument list must also be optional and declared using the Optional keyword. Optional can't be used for any argument if ParamArray is used.

Example 1

Create a module and type the following line in the Declarations section if it is not already there:


Option Explicit

Type the following procedures:


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 As Variant)
   If IsMissing(Title) Then
      Debug.Print lname & ", " & fname
   Else
      Debug.Print lname & ", " & fname & "   " & Title
   End If
End Sub

To test this function, open the Employees form in Form view.

Press CTRL+G to open the Immediate window. Type the following line in the Immediate window, and then press ENTER:
```
? CallEmployeeInfo() 
```
Note that the relevant information is displayed in the Immediate window. 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 Immediate window.

Example 2

The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.6 Object Library.

Create a module and type the following line in the Declarations section if it is not already there:


Option Explicit

Type the following procedure:

 
Function OptionalTest(Optional Country)
   Dim dbs As DAO.Database, rst As DAO.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
   rst.Close
   dbs.Close
End Function

Type the following line in the Immediate window, and then press ENTER:
```
? OptionalTest("UK") 
```
Note that the value displayed in the Immediate window is the number of records where the ShipCountry is equal to UK.

Type the following line in the Immediate 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 based on forms.

REFERENCES

For more information about the Optional keyword, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "Optional arguments" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words:

Keywords : kbprg kbdta AccCon KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto