ACC: Custom Domain Functions Similar to DFirst() and DLast()
ID: Q103403
|
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
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 DStart()instead 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 DEnd()instead 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.
REFERENCES
For additional information, please see the following Visual Basic article
in the Microsoft Knowledge Base:
Q178070 HOWTO: Handle Quotes and Pipes in Concatenated SQL Literals
Additional query words:
ab modules docbug
Keywords : kbprg
Version : 1.0 1.1 2.0
Platform : WINDOWS
Issue type :