Prevent duplicate entries on an Input Form

[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