Dupe check - getting data type mismatch - is it the curreny field? 1

[desperateUser]

I've been scoping out code from previous posts. I'm checking for a duplicate entry on the BeforeUpdate event of a subform. We want to make sure that a payment on a claim hasn't already been made. We're trying to do this by checking for entries in the tblBillAuditDetail table that have a matching payment amount, code mod and date.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim rs As DAO.Recordset, Criteria As String, ID As Long
Dim Msg As String, Style As Integer, Title As String, DL As String, NL As String

NL = vbNewLine
DL = NL & NL

Criteria = "[Payable Amount] = " & Me![Pay Amt] & " And " & _
            "[CodeMod] = " & Me![Proc Code] & " AND " & _
            "[Date of Service]= #" & Me![Date of Service] & "#"

ID = DLookup("CodeMod", "tblBillAuditDetail", Criteria)

If ID > 0 Then
    Msg1 = "A claim payment like this already exists," & DL & _
          "check that this isn't a duplicate. Press OK to ignore this message and save the record" & DL & _
    Style = vbCritical + vbOKOnly
    Title = "Potential record duplicate..."
    MsgBox Msg, Style, Title
    Me.Undo
    Me.Recordset.FindFirst "CodeMod = " & ID
    Cancel = True
End If

Set rs = Nothing

End Sub

When I run this code it gives me

Run-time error '3464':
Data type mismatch in criteria expression.

I would like to throw claim number in there but it's on the main form and when I add that to the criteria string

"[Claim #] = '" & Forms!frmEORHeader.[Claim #] & "'  And " & _

I get run-time error 2001.

Any help is appreciated WAY in advance! Thank you.

Penelope

 

[lameid]

I would guess that the [Claim #] field is a number as opposed to text so you don't need single/double quotes as delimteters.

"[Claim #] = " & Forms!frmEORHeader.[Claim #] & "  And " & _

Note the removal of ' twice.

 

[desperateUser]

Well, no. The claim number IS a text field. I double-checked that as my first stab at trouble shooting. (I had it as a number before but when I removed the single quotes the error message I was getting didn't change any.)

This database wasn't my creation. Its a remnant of a db made by my predecessor. We're going to a new claims system soon - but meanwhile, this little piece of functionality would really help the clerks doing the payment entries.

 

[lameid]

Perhaps Claim # is also the name of a calculated control and not just a field name? I would think you would want the control value anyway... try giving it a name like txtClaimNum and using Me!txtClaimNum in your expression.

 

[desperateUser]

I've added the claim id to the search parameter - so now my code looks like this:

Dim rs As DAO.Recordset, Criteria As String, ID As Long
Dim Msg As String, Style As Integer, Title As String, DL As String, NL As String

NL = vbNewLine
DL = NL & NL

Criteria = "[Payable Amount] = " & Me![Pay Amt] & " And " & _
            "[CodeMod] = " & Me![Proc Code] & " AND " & _
            "[Claim #] = '" & Forms!frmEORHeader.[cmbClaimID] & "'  And " & _
            "[Date of Service]= #" & Me![Date of Service] & "#"

ID = DLookup("[Claim #]", "tblBillAuditDetail", Criteria)

If ID > 0 Then
    Msg1 = "A claim payment like this already exists," & DL & _
          "check that this isn't a duplicate. Press OK to ignore this message and save the record" & DL & _
    Style = vbCritical + vbOKOnly
    Title = "Potential record duplicate..."
    MsgBox Msg, Style, Title
    Me.Undo
    Me.Recordset.FindFirst "[Claim #] = " & ID
    Cancel = True
End If

Set rs = Nothing

End Sub

When I run it I get this message:

Run-time error '2001':

You cancelled the previous operation.

I'm searching now on the claim number of the record instead of the code mod field.

Researching error code 2001 brings up that there is probably something wrong with the syntax of my criteria. It's why I double-checked and discovered that the claim id was a text data type instead of numeric. I had removed it from my original post code to try and narrow down the issue.

When I mouse over my code in run mode, values pop up for everything but the claim id.

Thank you for any insights!

 

[lameid]

Is the claim ID the last value you are updating? It could be that the Claim ID is not commited since you are using a before update event.

A solution would be to use an unbound form for entry and then use an insert query after it has passed validation on the click event of a button. You might also have luck with using the text property instead of the value property.
Setting the focus to another control may also help too.

You you are using a dlookup to lookup the claim# and using it as part of your criteria... I think you may really want dcount instead.

 

[desperateUser]

The data I'm getting is in a subform that is on one of three tabs of the main form. The claim # is populated from tab one of the main form.

I'm just trying to figure out why I get error 2001?

Thanks!

 

[formerTexan]

Hi desperateUser,

What happens if you comment out the Me.undo line? This line seems a bit redundant since Cancel = true will result in the update not being processed.

Cheers, Bill

 

[formerTexan]

Sorry, disregard the Me.undo comment. It should have no bearing on the error. And its not redundant either.

Cheers, Bill

 

[lameid]

I don't expect it would matter but use a ! instead of .

Forms!frmEORHeader[red]![/red][cmbClaimID]

 

[desperateUser]

@lameid - man, thanks for sticking with me here!

Adding the ! did result in the parameter actually showing up when I mouse over it in run mode - I wasn't even getting that before.

I'm still getting the run-time error 2001. VB highlights the following line in yellow:

ID = DCount("[Claim #]", "tblBillAuditDetail", Criteria)

The message says "You canceled the previous operation."

wha..??

 

[lameid]

Above the line with the error...

Debug.print Criteria

That will put the value of criteria in the immediate window... Post that value here just in case I am missing something or we can use it to make a query equivalent to the dlookup so we can see if that runs.

 

[desperateUser]

[Payable Amount] = 432.95 And [CodeMod] = 01400 AND [Claim #] = '20090127' And [Date of Service]= #01/09/2008#

I'm not quite familiar enough with the VB code to have the data types definitions down cold (like what needs single quotes, double quotes, etc.) But in order to clarify I looked up the following:

CodeMod: data type text
Payable Amount: data type currency
Date of Service: data type Date/Time
Claim #: data type text

So do I have the data types not coming through the criteria as the correct data type?

Thanks so much - I truly appreciate your help.

 

[lameid]

Dates and Times (1 datatype) require the # delimeter.
Text datatypes (Text and Memo) require a text delimeter, either the double or single quote... " Or '
Numbers do not require delimeters (A bunch of datatypes including currency).

So what you are missing is text delimeters for Codemod.

Criteria = "[Payable Amount] = " & Me![Pay Amt] & " And " & _
            "[CodeMod] = '" & Me![Proc Code] & "' AND " & _
            "[Claim #] = '" & Forms!frmEORHeader.[cmbClaimID] & "'  And " & _
            "[Date of Service]= #" & Me![Date of Service] & "#"

 

[desperateUser]

Okay - thank you very much. It's all working now. I had to realize a few things and work through some message box stuff but it all seems good to go now. Stars for a thank you!

Penelope