Microsoft KB Archive/210179

= HOW TO: Use Optional Arguments in Access 2000 =

Article ID: 210179

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210179



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

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

IN THIS TASK
SUMMARY
 * Example 1
 * Example 2

REFERENCES



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

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. 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.

back to the top

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. 

back to the top

Example 2
NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

  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 </li>  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. </li>  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. </li></ol>

back to the top

<div class="references_section">