Prevent duplicates based on 2 fields

[Chew407]

I am trying to edit some code to prevent duplicate entries in a database based on two fields instead of just one. I would like to check the Fuel Usage table for records that match both the invoice number and date purchased. Any ideas on how to add the date purchased as criteria for duplicates in this code? Thank you!
My existing code looks like this:

Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.Invoice_No.Value
    stLinkCriteria = "[Invoice No]=" & "'" & SID & "'"

    'Check Fuel Usage table for duplicate invoice numbers
    If DCount("Invoice_No", "tbl_FuelUsage", _
              stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Invoice Number " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
            
   'Go to record of original Invoice Number
        rsc.FindLast stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If
     
    Set rsc = Nothing
End Sub

 

[dhookom]

No field on control names for the date?
Try something like:

Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.Invoice_No.Value
    stLinkCriteria = "[Invoice No]=" & "'" & SID & _
        "' AND DOP=#" & Me.DOP & "#"

    'Check Fuel Usage table for duplicate invoice numbers
    '   and DateOfPurchase
    If DCount("Invoice_No", "tbl_FuelUsage", _
              stLinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Invoice Number " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
            
   'Go to record of original Invoice Number
        rsc.FindLast stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If
     
    Set rsc = Nothing
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Chew407]

Thanks for the reply dhookom. The control for the purchase date is called cboStartDate and the field in the table is called Date_Purchased.

Should the following work if placed into the BeforeUpdate for Date Purchased? (Invoice number is entered into the form first then the date puurchased)

    SID = Me.Invoice_No.Value
    stLinkCriteria = "[Invoice No]=" & "'" & SID & _
        "' AND [Date_Purchased]=#" & Me.cboStartDate & "#"

 

[dhookom]

Should the following work

Did you try it?

Duane
Hook'D on Access
MS Access MVP

 

[Chew407]

Sorry, yes I tried it and received run-time error 2001: You canceled the previous operation.

 

[Chew407]

Saved the form. Ran through it again and the code is not picking up the duplicate (No message box pops up).

 

[Chew407]

I had a default in on the date control. I removed it and re-entered an existing invoice number and date and received the same run-time error 2001: You canceled the previous operation. Any ideas?

 

[PHV]

into the BeforeUpdate
So, add the following line in your code when a duplicate is found:
Cancel = True

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Chew407]

The error occurs on:

If DCount("Invoice_No", "tbl_FuelUsage", _
stLinkCriteria) > 0 Then

Wouldn't I need to update this part of the code as well to include "Date_Purchased"?

Already incorporated into the code is the line Me.Undo as can be seen below. This is the updated code in the BeforeUpdate procedure on the Date_Purchased control:

    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.Invoice_No.Value
    stLinkCriteria = "[Invoice No]=" & "'" & SID & _
        "' AND [Date_Purchased]=#" & Me.cboStartDate & "#"

    'Check Fuel Usage table for duplicate invoice numbers
    'and date purchased
    If DCount("Invoice_No", "tbl_FuelUsage", _
              stLinkCriteria) > 0 Then
        
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Invoice Number " _
             & SID & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
               
   'Go to record of original Invoice Number
        rsc.FindLast stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If
     
    Set rsc = Nothing
End Sub

Thanks again for your help!