asked on
Function ReceiptCK() As Boolean 'Check for office service and PT payment entry
If DCount("*", "tClaimHistory", "ClaimNum=" & [Forms]![FPEntryQuery]![FClaimQuery]![tClaimNum] & " AND Status = ""PNT - Paid""") = 0 Then
MsgBox "Payment (PMNT) entry needed to create a receipt", vbInformation, "Receipt"
ReceiptCK = False
ElseIf DCount("*", "tClaimHistory", "ClaimNum=" & [Forms]![FPEntryQuery]![FClaimQuery]![tClaimNum] & " AND Status = ""Office Service""") = 0 Then
MsgBox "Office service (OSrv) entry needed to create a receipt", vbInformation, "Receipt"
ReceiptCK = False
Else
ReceiptCK = True
End If
End Function
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
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
ASKER
ASKER
ClaimNum can have multiple rowsThe 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.
ASKER
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
TRUSTED BY
Open in new window