Prevent Duplicate in table -Before Update of Textbox (1 Viewer)

[eka24]

Please I have a Table "tblSubmission" with Field "ClNo"
I found the code on this platform which works fine when make manual entry into the textfield "ClNo" which prevent duplicate in the table "tblsubmission".

My Problem: ClNo is a concateated field which derives from three other textboxes. Because of this, the before update of ClNo doesnt work to prevent duplicate in the table


Private Sub ClNo_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
Answer = DLookup("[ClNo]", "tblSubmission", "[ClNo] = '" & Me.ClNo & "'")
If Not IsNull(Answer) Then
MsgBox "Duplicate Record, Already exist" & vbCrLf & "Please Check and try again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

'Cancel = True
Me.Undo


Else:
End If
End Sub

 

[June7]

Would have to use event of the 3 textboxes which I presume are user input or form BeforeUpdate.

 

[eka24]

OK I will try and get back

 

[moke123]

ClNo is a concateated field which derives from three other textboxes. Because of this, the before update of ClNo doesnt work to prevent duplicate in the table

Is the data in the 3 other text boxes being saved to the table?
Is there a specific reason to save a concatenated field?
Normally you would not save it but would assemble it as needed for display. For instance you would not have a table with First Name, Last Name and Full Name. You would concatenate the Full Name when needed for display.

 

[missinglinq]

Assuming that the three Fields are being saved to the Table...you just need to check the three Fields, rather than ClNo, which, has been said, shouldn't really be saved in the Table...like this

Private Sub Form_BeforeUpdate(Cancel As Integer)

 If DCount("*", "tblSubmission", "[Field1] & [Field2] & [Field3] = '" & Me.Field1 & Me.Field2 & Me.Field3 & "'") > 0 Then

   MsgBox "Duplicate Record, Already exist" & vbCrLf & "Please Check and try again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

   Cancel = True
 End If

End Sub

Replacing Field1, Field2, Field3 with your actual names.

Linq ;0)>

 

[eka24]

thank very much all of you. Richmond's helped me