ACC: How to Create Custom Domain Function Similar to DCount()

• Microsoft Access versions 1.0, 1.1, 2.0

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

     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]".