Prevent duplicate entries on an Input Form

[Uncle Gizmo]

On second thoughts or is it a third thoughts! Yes, the index would work, you'd have to apply it to the ID column and the 1 to 4 column. So I'm being swayed back to doing it the right way so to speak, mainly because in my experience there's always another column added sooner or later!

If you go the VBA route, then that means you've got to rewrite your VBA code, where, going the new table route, you can add as many new components as you like, CC, CW, SF, RF >>>> CX, CZ, CB, etc ... You just need to add them to the lookup table. The only fly in the ointment is you would probably have to add extra values to the 1 to 4 lookup... Still neither way is ideal, it's a choice between the devil and the deep blue sea I reckon....

 

[PatAccess]

I'm trying out all these options. Will let you know.
Thank you again for your help!

 

[Solo712]

On second thoughts or is it a third thoughts! Yes, the index would work, you'd have to apply it to the ID column and the 1 to 4 column. So I'm being swayed back to doing it the right way so to speak, mainly because in my experience there's always another column added sooner or later!

If you go the VBA route, then that means you've got to rewrite your VBA code, where, going the new table route, you can add as many new components as you like, CC, CW, SF, RF >>>> CX, CZ, CB, etc ... You just need to add them to the lookup table. The only fly in the ointment is you would probably have to add extra values to the 1 to 4 lookup... Still neither way is ideal, it's a choice between the devil and the deep blue sea I reckon....

I liked your "first thoughts" better. Obviously, it is somewhat an exercise in futility to find a solution of a problem if there is a huge design miscue. Even if you succeed in finding a solution in such a situation, you might only be encouraging mischief.

FWIW, (back to the original thread) - for my duplicate checking, I have devised a generalized method in a standard module which I use in the Form's Before_Update event. I don't use before_updates on individual fields because there are issues with such an approach plus having all my form's checks in one place makes designing and troubleshooting much easier.

Public Function IsDuplicated(ffield As String, ddomain As String, Criteria As String) As Boolean
     Dim thisForm As Form, i As Long
     
     Set thisForm = Screen.ActiveForm
     i = DCount("*", ddomain, Criteria)
     If i <> 0 Then
       If thisForm.NewRecord Then
          IsDuplicated = True
       ElseIf thisForm(ffield) <> DLookup(ffield, ddomain, Criteria) Then
           IsDuplicated = True
       End If
    End If
    Set thisForm = Nothing
End Function

You would call it by eg.:

IF IsDuplicated("ID", "MyTable", "Field1=" & Me!Field1 & " AND " & _ 
                "Field2 =" & Me!Field2) Then

where ID has to be a field with a unique value, eg the table's PK. The code tests if Field1 + Field2 are a unique combination. In the function IsDuplicated, two tests are made to find if a match is found. If the record is new then a non-zero DCount means outright there is a duplicate, if it is not new then the ID of the current record must be the same as that of stored recordset which answers to the Criteria.

Best,
Jiri

 

[Misterpiz]

Here's one I use on Social Security numbers for a pop up when a duplicate....
SS# is stored in table "tblApplicant"

Private Sub SocialSecurity_BeforeUpdate(Cancel As Integer)

 Dim Answer As Variant
 Answer = DLookup("[SocialSecurity]", "tblApplicant", "[SocialSecurity] = '" & Me.SocialSecurity & "'")
 If Not IsNull(Answer) Then
 MsgBox "Duplicate Social Security Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.SocialSecurity.Undo
 
 
 Else:
 End If
End Sub

This was awesome, not a direct use but it got me headed in the right direction.

 

[Mashermoto]

Here's one I use on Social Security numbers for a pop up when a duplicate....
SS# is stored in table "tblApplicant"

Private Sub SocialSecurity_BeforeUpdate(Cancel As Integer)

 Dim Answer As Variant
 Answer = DLookup("[SocialSecurity]", "tblApplicant", "[SocialSecurity] = '" & Me.SocialSecurity & "'")
 If Not IsNull(Answer) Then
 MsgBox "Duplicate Social Security Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.SocialSecurity.Undo
 
 
 Else:
 End If
End Sub

Wow, what am I doing wrong? This code is perfect for my application, but I simply can't get it to work :banghead: I'm trying to use this code to let me know if I have entered a duplicate invoice number on an input form. Here is my code below. I am using the code in a form labeled FrmTblTransLog. The table is labeled TblTransLog. The text box in FrmTblTransLog is labeled Invoice. I created a module labeled ModInvoiceDuplicate by entering my code into the module. I'm not sure how you actually make the code work as VBA as I have only used macros. My method was to create a Macro labeled MacDuplicateInvoice by choosing RunCode, and setting the function name as Invoice_BeforeUpdate(Cancel As Integer). BTW, I changed Private Sub to Function because I am running it as a macro...again...the only way I know how. Then I selected Invoice from FrmTblTransLog and chose MacDuplicateInvoice from the property sheet drop down box. When I enter an intentional duplicate value in the forms invoice field I get "The object doesn't contain the Automation object 'Cancel.' I also don't know what the rest of the error means by the 'component.'

It's too lengthy to describe all my various attempts and resulting error. At the end of the day, I think I just need some guidance on how others successfully made the code work.

It's late, so I will check back in later to see if anyone can help this poor pin head out.

Function Invoice_BeforeUpdate(Cancel As Integer)

Dim Answer As Variant
Answer = DLookup("[Invoice]", "tblTransLog", "[Invoice] = '" & Me.Invoice & "'")
If Not IsNull(Answer) Then
MsgBox "Duplicate Invoice Number Found." & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True
Me.Invoice.Undo


Else:
End If
End Function