Data Type error in code to prevent duplicates

Question

I want to create a more user-friendly message than the Access message to notify users that they are about to create a duplicate record.  I found the following code in a post and have attempted to adapt it to my situation. It's in the Before Update event. I'm getting a data mis-match message and I figured out (duh) it is the references to "strCriteria," because the fields I want to check for are long integer data type.  I don't know how to change this so the code refers to a number rather than text. (PartnerID is indexed, no duplicates.) Or, perhaps there's another approach? Any help would be appreciated.

Const MESSAGETEXT = "A Profile Record already exists for this partner ID.”

Dim strCriteria As String

If Me.NewRecord Then

   strCriteria = "PartnerID = """ & Me.PartnerID & """ "

   If Not IsNull(Me.PartnerID ) Then

      If Not IsNull(DLookup(DLookup("PartnerID", "tbl_SFTP_Profile_Records", strCriteria)) Then

        MsgBox MESSAGETEXT, vbExclamation, "Duplicate Profile Record"

        Cancel = True

      End If

   End If

End If

End Sub

Answer 1

Now another glitch -- I get the proper message, but then another message pops up "No Current Record." I don't know where that's coming from. And the record stays open.

Do I need to add something else to issue a message like "Cannot save record." and close the record? I could add that message to the original one, but the No Current Record is a problem. So I need to add a line to close the record.

Or...just let the users see Access's long-winded message. and be done with it.

Answer 2

Got a syntax error with the quote after "PartnerID =" & Me.PartnerID"

Tried putting the quote in different places

Then took it off after & Me. PartnerID -- which is exactly what you showed in your response.

And it worked!

Thank you, thank you.

Answer 3

Thanks for the response. Now I'm getting a syntax error (3075) missing operator on the strCriteria line and then, after dismissing the error message, the DCount line is highlighted. I've checked my field name and control name on the form, and they are both PartnerID. Is that a problem?

Shouldn't be a problem, in this case, to have them named the same.  But I did miss a quote.

This

strCriteria = "PartnerID = & Me.PartnerID

Should be

strCriteria = "PartnerID =" & Me.PartnerID

Answer 4

Thanks for the response. Now I'm getting a syntax error (3075) missing operator on the strCriteria line and then, after dismissing the error message, the DCount line is highlighted. I've checked my field name and control name on the form, and they are both PartnerID. Is that a problem?

Answer 5

My suggestion on the code is:

Const MESSAGETEXT = "A Profile Record already exists for this partner ID.”

Dim strCriteria As String

If Me.NewRecord Then    strCriteria = "PartnerID = & Me.PartnerID       If DCount("PartnerID", "tbl_SFTP_Profile_Records", strCriteria) >0 Then

MsgBox MESSAGETEXT, vbExclamation, "Duplicate Profile Record"        Cancel = True      End IfEnd IfEnd Sub