ACC: Creating Functions Similar to DFirst and DLast (95/97)

MORE INFORMATION

1. Open the sample database Northwind.mdb.
2. Create a new standard module and enter the following two functions:

         '--------------------------------------
         ' Use DStart()instead of DFirst() to return
         ' the first sorted record in a domain.
         '--------------------------------------
         Function DStart(FieldName As String, DomainName As String, Optional
                      Criteria As Variant)
   
           Dim MyDB As DATABASE, MySet As Recordset
   
           ' Error out if there is no fieldname sent.
           If Len(FieldName) = 0 Then
              MsgBox "You Must Specify a Field name", , "DStart"
              Exit Function
           End If
   
           ' Error out if there is no domain sent.
           If Len(DomainName) = 0 Then
              MsgBox "You Must Specify a Domain name", , "DStart"
              Exit Function
           End If
   
           Set MyDB = CurrentDb()
           Set MySet = MyDB.OpenRecordset(DomainName, dbOpenDynaset)
   
           ' Apply a filter to the recordset if a criteria is sent.
           If Not IsMissing(Criteria) Then
              MySet.Filter = Criteria
              Set MySet = MySet.OpenRecordset()
           End If
   
           ' If there are no records, return the null, else return the value
           ' of the first record.
           If MySet.EOF Then
              DStart = Null
           Else
              MySet.MoveFirst
              DStart = MySet(FieldName)
           End If
   
           MySet.Close
           MyDB.Close
         End Function
   
         '-------------------------------------------
         'Use DEnd()instead of DLast() to return
         '  the last sorted record in a domain.
         '-------------------------------------------
   
         Function DEnd(FieldName As String, DomainName As String, Optional _
           Criteria As Variant)
   
           Dim MyDB As DATABASE, MySet As Recordset
   
           ' Error out if there is no fieldname sent.
           If Len(FieldName) = 0 Then
              MsgBox "You Must Specify a Field name", , "DEnd"
              Exit Function
           End If
   
           ' Error out if there is no domainname sent.
           If Len(DomainName) = 0 Then
              MsgBox "You Must Specify a Domain name", , "DEnd"
              Exit Function
           End If
   
           Set MyDB = CurrentDb()
           Set MySet = MyDB.OpenRecordset(DomainName, dbOpenDynaset)
   
           ' Apply a filter to the recordset if a criteria is sent.
           If Not IsMissing(Criteria) Then
              MySet.Filter = Criteria
              Set MySet = MySet.OpenRecordset()
           End If
   
           ' If there are no records, return the null, else return the value
           ' of the last record.
           If MySet.EOF Then
              DEnd = Null
           Else
              MySet.MoveLast
              DEnd = MySet(FieldName)
           End If
           MySet.Close
           MyDB.Close
         End Function
   					

3. Close and save the new module.
4. Create a new query based on the Orders table as follows:

         Query: Orders By Date
         ---------------------
         Type: Select Query
   
         Field: OrderID
            Table: Orders
         Field: OrderDate
            Table: Orders
            Sort: Descending
   					

5. Close and save the query as RecentOrders.
6. Open the Debug Window, type the following expression, and then press ENTER:

   ?DStart("OrderID","RecentOrders")

   Note that the OrderID 11077 is returned, which is the first OrderID in the query.
7. Type the following expression in the Debug Window, and then press ENTER:

   ?DFirst("OrderID","RecentOrders")

   Note that the OrderID 10314 (or 10248 in 7.0) is returned, which is the first OrderID entered in the Orders table, not the first in the RecentOrders query.

General Limitations to Custom Domain Functions

• You cannot use Forms!FormName!ControlName or Form.ID in quotation marks. For example, instead of

        DStart("[ProductName]","Products","[ProductID]=Forms![Products] _
           ![ProductID]")
  						

  use:

        DStart("[ProductName]","Products", BuildCriteria("[ProductID]" _
           ,dblong,"=" & Forms![Products]![ProductID]))
  						

  Note that the BuildCriteria() function is a Visual Basic function that creates a properly formed criteria string. You can use BuildCriteria() anywhere you concatenate criteria strings, such as the FindFirst method or when building custom SQL criteria.