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

MORE INFORMATION

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