ACC: How to Use Optional Arguments
ID: Q141605
|
The information in this article applies to:
-
Microsoft Access versions 7.0, 97
SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.
When you use the Optional keyword in Microsoft Access 7.0 and 97, you can
create procedures that accept optional (not required) arguments. This
article shows you two examples of how to create and use procedures with
optional arguments.
This article assumes that you are familiar with Visual Basic for
Applications and with creating Microsoft Access applications using the
programming tools provided with Microsoft Access. For more information
about Visual Basic for Applications, please refer to your version of the
"Building Applications with Microsoft Access" manual.
MORE INFORMATION
NOTE: In Microsoft Access 7.0, it was required that the variables declared
as optional were placed at the end of the calling procedure and that the
data type was explicitly a Variant. For example:
Sub MyTest(strTest1 As String, _
Optional Test2 As Variant, _
Optional Test3 As Variant)
This was a requirement because optional arguments were passed at the end
of the function call. In Microsoft Access 97 improvements have been made to
the use of the Optional keyword by allowing the data type casting of the
optional variable to be pre-assigned. For example:
Sub MyTest(strTest1 As String, _
Optional Test2 As String = "Test2", _
Optional Test3 As String = "Test3")
This allows for the placement of the optional variable anywhere in the
calling order of the function and allows you to test the contents of the
variable.
Example 1
- Create a module and type the following line in the Declarations section
if it isn't already there:
Option Explicit
- 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
- Open the Employees form in Form view.
- 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
- Create a module and type the following line in the Declarations section
if it isn't already there:
Option Explicit
- 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
- 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.
- 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.
REFERENCES
For more information about the Optional keyword, search the Help Index for
"optional argument," or ask the Microsoft Access 97 Office Assistant.
Additional query words:
Keywords : kbprg
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbhowto