Verification of entry duplication on a large scale

[bcooler]

Thanks for reading.

I have been very successful with the bit of code shown below that verifies no duplicates as data is being entered.

Private Sub txtShelter_Serno_BeforeUpdate(Cancel As Integer)
    Dim Answer As Variant
    Answer = DLookup("Shelter_Serno", "tblBSEShelter", "Shelter_Serno = '" & Me.txtShelter_Serno & "'")
    If Not IsNull(Answer) Then
        MsgBox "Serial number has already been entered in the database."
        Cancel = True
        Me.txtShelter_Serno.Undo
    Else:
    End If
End Sub

The problem is some of my larger forms require this code to be written as a "BeforeUpdate" for each field, causing a lot of duplicate code. I'd like to reduce the code by using more generic terms for the fields and textbox name as shown below.

Private Sub txtJCA_Mfr_SN_BeforeUpdate(Cancel As Integer)
    Dim Answer As Variant
    Answer = DLookup(Me.ActiveControl.ControlSource, Me.RecordSource, Me.ActiveControl.ControlSource & " = '" & Me.ActiveControl & "'")
    If Not IsNull(Answer) Then
        MsgBox "Serial number has already been entered in the database."
        Cancel = True
        Me.ActiveControl.Undo
    Else:
    End If
End Sub

I'd like to move this code to a module and call it from a few different forms, but I get an "Object Required" error when I call its associated subroutine from the "BeforeUpdate" event.

I think it might be because I am using the "Me." form reference and it loses its capabilites when residing in a module? I also tried "Forms!" but also got nowhere.

Thanks for the help!

 

[bcooler]

OK, I've lucked into some of the solution, but still need help.

If I use this code in the BeforeUpdate:

Private Sub txtShelter_Serno_BeforeUpdate(Cancel As Integer)
    Call NoDuplicateSerial(Me)
End Sub

and then use this in the module:

Sub NoDuplicateSerial(FormName1 As Form)
    Dim Answer As Variant
    Answer = DLookup(FormName1.ActiveControl.ControlSource, FormName1.RecordSource, FormName1.ActiveControl.ControlSource & " = '" & FormName1.ActiveControl & "'")
    If Not IsNull(Answer) Then
        MsgBox "Serial number has already been entered in the database."
        Cancel = True
        FormName1.ActiveControl.Undo
    Else:
    End If
End Sub

then everything works except the "undo" doesn't undo. The module does understand the "FormName1.ActiveControl" because the intermediate window gives the correct value of the field.

How do I get an undo to prevent duplicate entry?

 

[bcooler]

My mistake. I was setting the "Cancel" to true without passing it back to the BeforeUpdate event, causing the undo to be useless.

Thanks to anyone who may have been looking into this.