Better code then dcount to check for entry in table

Is this what you are trying to do?

Function ReceiptCK() As Boolean 'Check for office service and PT payment entry
    Dim sStatus as String   
    sStatus = DLookup("Status", "tClaimHistory", "ClaimNum=" & [Forms]![FPEntryQuery]![FClaimQuery]![tClaimNum] 
    Select Case sStatus 
        case "PNT - Paid" 
            MsgBox "Payment (PMNT) entry needed to create a receipt", vbInformation, "Receipt"
            ReceiptCK = False
        Case "Office Service"
            MsgBox "Office service (OSrv) entry needed to create a receipt", vbInformation, "Receipt"
            ReceiptCK = False
        Case Else
            ReceiptOK = True
    End Select
End Function

Select all Open in new window

Try using DLookup instead of dcount.

The advantage of DLOOKUP is that it will return the first value found which matches your criteria, rather than searching the entire recordset to get the count, so it is probably a little bit faster than DCOUNT.

If you only have one record in your table (tClaimHistory) for each [ClaimNum], then Pat's SELECT Case with a single DLOOKUP( ) would be more efficient.  

But if this table contains a history of status values, with dates, then the dcount method would work.  As would creating a recordset filtered on the ClaimNum, then use the rs.FindFirst method to search for the records with particular status values.  This method might be quicker, but would involve more coding and "quicker" is relative.  The following is air code (not tested)

Dim rs as DAO.Recordset
Dim arrSearch() as string
Dim intLoop as integer 
Dim strSQL as string
arrSearch() = Split("PNT - PAID,Office Service", ",")

'set default value
ReceiptCK = true

strSQL = "SELECT Status FROM tCliamHistory WHERE [ClaimNum] = " & [Forms]![FPEntryQuery]![FClaimQuery]![tClaimNum]
set rs = currentdb.openrecordset(strSQL, dbopendynaset)
with .rs
    For intLoop = lbound(arrSearch) to ubound(arrSearch)
        .FindFirst "Status = '" & arrSearch(intLoop) & "'"
        if .nomatch AND intLoop = 1  
            MsgBox "Payment (PMNT) entry needed to create a receipt", vbInformation, "Receipt"
            ReceiptCK = False
            Exit For
        elseif .nomatch and intLoop = 2 then
            MsgBox "Office service (OSrv) entry needed to create a receipt", vbInformation, "Receipt"
            ReceiptCK = False
        end if
    Next
End with
rs.close
set rs = nothing

Select all Open in new window

I don't think you're right about DCOUNT being less efficient than DLOOKUP, especially when using DCOUNT on an indexed field (or just using *, which tells Access to use the "best" index). COUNT functions - at least in SQL Server - are significantly faster than any data lookup functions. I assume they're similar in Jet/ACE as well.

Here's another variation, untested...  (but I think performance -wise, these all might be splitting hairs):

Function ReceiptCK() As Boolean 

Dim s as string
dim sCrit as string

dim rs as dao.recordset

On Error Goto PROC_ERR

sCrit =  "ClaimNum=" & [Forms]![FPEntryQuery]![FClaimQuery]![tClaimNum]
s =  "SELECT SUM(Status = 'PNT - PAID') AS Paid,  SUM(Status = 'Office Service')  AS OfficeService FROM tClaimHistory WHERE " & scrit

set rs = currentdb.openrecordset (s, dbopendynaset, dbseechanges)

ReceiptCK = (rs!Paid Or rs!OfficeService )

if rs!Paid = False then 
    MsgBox "Payment (PMNT) entry needed to create a receipt", vbInformation, "Receipt"
    goto PROC_EXIT
end if

if rs!OfficeService  = False then 
    MsgBox "Office service (OSrv) entry needed to create a receipt", vbInformation, "Receipt"
    goto PROC_EXIT
end if

PROC_EXIT:
      rs.Close
      set rs = nothing
      Exit Function
PROC_ERR:
    msgbox "ERROR " & err.number & ": " & Err.description
end function

Select all Open in new window

I posted in another group regarding DCount and DLookup, but I'll post here for clarity:

I created a test database, with a table that contains 2 fields: ID (AN field) and Field1 (a Text field), and created and index on Field1. I added 500,000 records. I then ran code to test for both DCount and DLookup:

cnt = DLookup(“ID”, “Table1”, “Field1=’200000’”)

cnt = DCount(“*”, “Table1”,  “Field1=’200000’”)

Neither was better at performance than the other. They both report nearly identical return values. Interestingly, DCount consistently reported the same time (3 milliseconds), while DLookup occasionally returned 2, 3, or 4 milliseconds.

Note that prior to indexing the field, DCount was noticeably faster, by a factor of 5 or more, when dealing with values that were not close to the front of the recordset (records in the 10,000+ mark). Inside of the first 10,000 records, the values were pretty much the same.

Moral of the story is: Neither is better. Use what makes the most sense to your application.

Is ClaimNum unique in tClaimHistory, or can there be multiple rows for the same ClaimNum with different Status values?

---

»bp

ClaimNum can have multiple rows

Thanks PatHartman, Love the case statement but its not 100% correct.... I'm basically looking for missing entries... so I believe your code will hit only if it finds an entry which is just the opposite.

ClaimNum can have multiple rows

 The case statement wouldn't work anyway if there were multiple rows.  Please understand any code I wrote was a complete guess since I had no idea what you wanted to do.  And I still don't know what you are looking for since you haven't said anything except that what you were doing wasn't what you wanted.

OK thanks for the information from all.... not sure the best comment to select so let me know if I'm not correct in my selection.