How to check if there is duplicate entry

[undee69]

hello again. I am trying to check if there's a way that when I entered a value in my form, it will tell me that it is already existing. For example, I entered 1001 in the text field name order number with control source of "orderNum", and this text box will be appended automatically by " / 2013", this will then look up all the values in the "orderNum" column of the table. If there is an existing number, it will display a messages box like "This Order Number is existing". I am trying to search for it over here and other sites but I can't seem to find an answer. Thanks for the usual help. Regards Undee

[RuralGuy]

Use a .FindFirst on the RecordSetClone in the BeforeUpdate event of the control.
If Not Me.RecordsetClone.NoMatch Then
...it already exist!

[undee69]

not sure how to do that ^_^

[RuralGuy]

I'm not sure where to start helping then. What is the name of your Control and the name of the Field to which it is bound?

[undee69]

Here you go.

Form Name: frmTransaction
Control Name: Delivery Note Number
Bound to Field: dlvryNoteNum
Table name: tblDeliveryTransaction

[RuralGuy]

Keeping in mind this is just a guess...it will look something like:

Private Sub Delivery_Note_Number_BeforeUpdate(Cancel As Integer)
   Me.RecordsetClone.FindFirst "[dlvryNoteNum] = " & Me.[Delivery Note Number]
   If Me.RecordsetClone.NoMatch Then
      '-- Not a duplicate
   Else
      MsgBox "This is a duplicate Number!"
      Cancel = True
   End If
End Sub

You will need to tweak this to your own specifications.

[RuralGuy]

BTW, embedded spaces in names will *always* be problematic. Better to use something like CamelFontNames.

[undee69]

Thanks for the assistance and tip ^_^

[RuralGuy]

Post back to this thread if you still can't get it working.