Microsoft KB Archive/103403

= ACC: Custom Domain Functions Similar to DFirst and DLast =

Article ID: 103403

Article Last Modified on 5/6/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q103403



Moderate: Requires basic macro, coding, and interoperability 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.

Microsoft Access online Help incorrectly states:

  DFirst and DLast return values from the first and last occurrence according to the order of records in domain. If domain is an indexed table, the order follows the current index. Otherwise, the order follows the actual order of the records.

This article provides examples of how to write custom domain functions that can be used 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. Also included in this article is the DFix function, which can be used to overcome a limitation of concatenating variables into criteria strings. Information about the limitations of custom domain functions and how to use DFix appears at the end of the article.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x, or the "Building Applications" manual, Chapter 3, "Introducing Access Basic" in version 2.0.



MORE INFORMATION
Create a new module within Microsoft Access and add the following functions with the appropriate declaration section. Each command should be entered on one line.

NOTE: In the following sample code, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

  '- ' GLOBAL DECLARATION '-  Option Compare Database Option Explicit

'--  ' Use DStartinstead of DFirst to return ' the first sorted record in a domain. '--  Function DStart (FieldName As String, DomainName As String, _                    Criteria As String)

Dim MyDB As Database, Myset As Dynaset

' ERROR OUT IF THERE IS NO FIELDNAME SENT. If Len(FieldName) = 0 Then MsgBox "You Must Specify a Field name",, "DStart" End 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.CreateDynaset(DomainName)

'     If Len(Criteria) > 0 Then Myset.Filter = Criteria Set Myset = Myset.CreateDynaset 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 DEndinstead of DLast to return ' the last sorted record in a domain. '--  Function DEnd (FieldName As String, DomainName As String, _                  Criteria As String) Dim MyDB As Database, Myset As Dynaset

' 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.CreateDynaset(DomainName)

If Len(Criteria) > 0 Then Myset.Filter = Criteria Set Myset = Myset.CreateDynaset End If     If Myset.EOF Then DEnd = Null Else Myset.MoveLast DEnd = Myset(FieldName) End If     Myset.Close MyDB.Close End Function

Function DFix (ByVal T, DQuote As Integer) '--     ' Fixes string arguments that are passed '  to Criteria in domain functions '     ' DQuote should be TRUE or -1 if Double Quotes (") delimit Criteria      ' DQuote should be FALSE or 0 if Single Quotes (') delimit Criteria      '      ' For example, this gives an error      '   (note the quote (') in the data)      '   X="Mike's Diner"      '   A=DStart("ID","Clients","Name='" & X & "'")      '      ' Use either:      '   X=DFix("Mike's Diner",False)      ' Or:      '   A=DStart("ID","Clients","Name='" & DFix(X,False) & "'")      '--      Dim P As Integer, OldP As Integer, Q As String * 1

If VarType(T) = 8 Then If DQuote = 0 Then Q = "'" Else Q = """" End If        P = InStr(T, Q)         Do While P > 0 OldP = P + 2 T = Left$(T, P) & Q & Mid$(T, P + 1) P = InStr(OldP, T, Q)        Loop End If     DFix = T   End Function

General Limitations to Custom Domain Functions
  You cannot use Forms!FormName!ControlName or Form.ID in quotation marks. For example, instead of

     DStart("ID","LOG","Name=Form.Name")

use:

DStart("ID","LOG","Name = '" & DFix(Name,False) & "'")

The DFix function is another custom function.  You must always specify a criteria, even if it is "" or Null. Do not place field names or domain names in square brackets ([]), that is, use "First Name" instead of "[First Name]".

NOTE: DFix can be used anywhere you concatenate criteria strings, such as the FindFirst method or when building custom SQL criteria.

Also, Declarations for P and OldP can be removed completely.

