how can i do this constrain

[grad2009]

hello guys,
i have a form that is called items, and it contains a field that is called itemNo and the type of this field is number. i want to do my own constraint which prevents the user to enter a number that is previously existed.
i need your help please


many thanks to you,
bye

[RuralGuy]

Check for a duplicate in the BeforeUpdate event of the control on your form that is bound to this field.

[grad2009]

first, thank you RuralGuy for your reply

Check for a duplicate in the BeforeUpdate event of the control on your form that is bound to this field.

really i didn't understand what do you mean exactly, do you mean that i have to go to the form that contanis itemNo field then go to the event BeforeUpdate of the itemNo field.
could you add more explains to understand what do you mean
thanks with regards,

[RuralGuy]

Are you entering the itemNo in a control on a form? If so, do a DCount or DLookup in the BeforeUpdate of that control or even a FindFirst would work.

[grad2009]

hello,
In the beforeUpdate i wrote this code

Private Sub itemNo_BeforeUpdate(Cancel As Integer)
numberfield = Me.itemNo
Dim a
a = DLookup("[itemNo]", "Items", "[itemNo]='" & Me.itemNo & "'")
If Not (IsNull(a)) Then
MsgBox "This number is previously existed", vbCritical, "Warning Message!!!"
Cancel = True
Me.undo
DoCmd.FindRecord numberfield
End If

is this right, when i test this code an error occur, in the attachment the image of this error
the itemNo is of string type

[RuralGuy]

Try this code instead:

Private Sub itemNo_BeforeUpdate(Cancel As Integer)
    If Not IsNull(DLookup("[itemNo]", "Items", "[itemNo]='" & Me.itemNo & "'")) Then
        MsgBox "This number is previously existed", vbCritical, "Warning Message!!!"
        Cancel = True
    End If
End Sub

[RuralGuy]

My bad, [itemNo] is numeric:

Private Sub itemNo_BeforeUpdate(Cancel As Integer)
    If Not IsNull(DLookup("[itemNo]", "Items", "[itemNo]=" & Me.itemNo)) Then
        MsgBox "This number is previously existed", vbCritical, "Warning Message!!!"
        Cancel = True
    End If
End Sub

[grad2009]

Thanks alot RuralGuy
i'm sorry for annoying you, in really i haven't a sufficient information about dlookup function so i'm sorry for this mistake.
could you give me a reference that explains how to use dlookup function in details.
thanks alot
regards,

[RuralGuy]

Have you looked in VBA Help yet? Here's a good link on the topic: http://www.mvps.org/access/general/gen0018.htm

[grad2009]

many thanks to you (RuralGuy)
regards,

[RuralGuy]

Sounds like you got it working. That's great! Glad we could help.