Access Hacks

Hack #76. Shrink Your Code with Optional Arguments

Put subroutines to even more general use by accepting different numbers of arguments.

"Shrink Your Code with Subroutines" [Hack #75] shows you how to reduce code by using a generic subroutine. This hack takes that concept a step further. Subroutines can take optional arguments. Calling routines are required only to supply arguments that aren't optional. The optional ones are, well, optional.

Example 8-5 shows a handful of routines and the subroutine they call.

Example 8-5. A set of routines that call a subroutine

 Sub get_NY_records( )
   'get New York customers
    get_state_records "NY"
 End Sub
 
Sub get_CT_records( )
  'get Connecticut customers
   get_state_records "CT"
 End Sub

 Sub get_MA_records( )
   'get Massachusetts customers
    get_state_records "MA", "Boston"
 End Sub
 
Sub get_state_records(state As String, Optional city As String)
  Dim conn As ADODB.Connection
  Set conn = CurrentProject.Connection
  Dim recset As ADODB.Recordset
  Set recset = New ADODB.Recordset
  If state = "MA" Then
     recset.Open "Select * From Customers Where State='" & state _
     & "' And City='" & city & "'", conn
  Else
    recset.Open "Select * From Customers Where State='" & state & "'", conn
  End If
 Do Until recset.EOF
 ''Process records here
 recset.MoveNext
 Loop
 recset.Close
 Set recset = Nothing
End Sub

The subroutine takes two arguments: state, which is required, and city, which is optional. The Optional keyword is placed in front of the argument name:

 Sub get_state_records(state ...