Microsoft KB Archive/103401

= ACC: How to Create Custom Domain Function Similar to DCount =

Article ID: 103401

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 Q103401



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

Custom domain functions can be used to provide functionality other than that provided by the standard domain functions DLookup, DMin, DLast, and so on.

This article demonstrates how to write a custom domain function that is similar to the DCount function. In addition, it demonstrates a sample function, DFix, to overcome limitations in concatenating variables in criteria strings. Information about the limitations of custom domain functions is at the end of this 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
The DRecCount sample custom domain function is useful when the domain is a totals or aggregate query based on an attached SQL table. The DRecCount custom domain function will accurately return the count of such a dynaset.

NOTE: Domain functions such as Dcount function normally with SQL attached tables when using Microsoft Access for Windows 95.

To create these examples, open a new module within Microsoft Access and add the following functions with the appropriate declaration section:

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

Function DRecCount (FieldName, DomainName, Criteria) '---     ' Use DRecCount to return a count '  of records when the domain is a query based on a      '   totals/aggregate query on an attached SQL table. '---    Dim MyDB As Database, Myset As Dynaset 'Comment for 7.0 or 97

If VarType(FieldName) <> 8 Or Len(FieldName) = 0 Then MsgBox "You Must Specify a Field name",, "DRecCount" Exit Function End If    If VarType(DomainName) <> 8 Or Len(DomainName) = 0 Then MsgBox "You Must Specify a Domain name", ,"DRecCount" Exit Function End If    If VarType(Criteria) <> 8 And Not IsNull(Criteria) Then MsgBox "Invalid Criteria",, "DRecCount" Exit Function End If

Set MyDB = CurrentDB Set Myset = MyDB.CreateDynaset(DomainName) If FieldName <> "*" Then If Len(Criteria) > 0 Then Criteria = Criteria & " AND " End If      Criteria = Criteria & "[" & FieldName & "] Is Not Null" Myset.Filter = Criteria Set Myset = Myset.CreateDynaset End If    If Myset.EOF Then DRecCount = 0 Else Myset.MoveLast DRecCount = Myset.recordcount 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      '      ' e.g. this gives an error      '   (note the quote (') in the data)      '   X="Mike's Diner"      '   A=DRecCount("*","Clients","Name='" & X & "'")      '      ' Use either:      '   X=DFix("Mike's Diner",False)      ' Or:      '   A=DRecCount("*","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. The following example is not allowed:

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

Instead, use:

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

Note that the DFix function is another custom function that is demonstrated in this article.  You must always specify a criteria, even if it is "" or Null. Do not place field names or domain names in brackets. For example, use "First Name" instead of "[First Name]".

NOTE: DFix can be used with other criteria strings, such as the FindRecord method, or when building custom SQL criteria.

Additional query words: modules ab

Keywords: kbhowto kbprogramming KB103401

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.