ACC2000: Creating Functions Similar to DFirst() and DLast()

ID: Q210127

The information in this article applies to:

Microsoft Access 2000

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

This article applies only to a Microsoft Access database (.mdb).

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.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

MORE INFORMATION

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

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

Open the sample database Northwind.mdb.

Create a new 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 Immediate 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 Immediate 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 the BuildCriteria() function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "BuildCriteria Method" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words:


Keywords          : kbprg kbdta AccCon KbVBA 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: July 6, 1999