Knowledge Base

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

Article ID: 146415

Article Last Modified on 1/19/2007

APPLIES TO

Microsoft Access 95 Standard Edition
Microsoft Access 97 Standard Edition

This article was previously published under Q146415

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

SUMMARY

By design, the DFirst() and DLast() functions always return the first and last record that you entered in the underlying table, not the first and last record as ordered by the table's index or the query's sort order. DFirst() and DLast() ignore indexes, even primary keys and sort orders.

This article shows you how to write custom domain functions that you can use in a way similar to DFirst() and DLast(). The DStart() and DEnd() custom domain examples in this article return the first and last records listed in a sorted query.

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

To create and use custom domain functions similar to DFirst() and Dlast(), follow these steps:

Open the sample database Northwind.mdb.

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

Close and save the new module.

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

Close and save the query as RecentOrders.
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.
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.

REFERENCES

For more information about BuildCriteria() function, search the Help Index for "BuildCriteria Method."

Keywords: kbhowto kbprogramming KB146415

Microsoft KB Archive/146415

Contents