Validation Rule Help getting error

[JoeBlack]

Newbie

trying to make a small inventory control database but i am stuck once
again.

with this validation rule I am trying to check if the New Item Part Number
has been entered in the past and may already have stock and location.

this is the rule

=(IsNull(DLookUp("[stock added]","[Stock]","[Part Number] = " & [Part
Number])))

this seems to be working when New Item Part Number has already been entered
and shows the validation text

but when the New Item Part Number is a new unused before Part Number it
gives me this Access error

There is a(n)" in the form control's validation Rule Property

any help would be great

 

[Guest]

Why not just index on part number and set to unique (no duplicates)?

 

[JoeBlack]

I have Part Number indexed but it is the full parts list with prices and
description the other table is local stock with part number in relation
with the other table. I have the indexed Part Number set to unique (no
duplicates) but this only stops me from entering a part number that is
not in the parts price list.

why are there two Part Number tables for easy price change.This way i can
change the whole price list with a quick import.

looking for solution on the bad expression only or examples of similar
expressions.

any help would be great
Joe

Why not just index on part number and set to unique (no duplicates)?

Newbie

trying to make a small inventory control database but i am stuck once
again.

with this validation rule I am trying to check if the New Item Part
Number has been entered in the past and may already have stock and
location.

this is the rule

=(IsNull(DLookUp("[stock added]","[Stock]","[Part Number] = " & [Part
Number])))

this seems to be working when New Item Part Number has already been
entered and shows the validation text

but when the New Item Part Number is a new unused before Part Number
it gives me this Access error

There is a(n)" in the form control's validation Rule Property

any help would be great

 

[JoeBlack]

Found solution... screw validation rules

made a [Event Procedure]


Private Sub Part_Number_AfterUpdate()
If Not IsNull(DLookup("[Part Number]", "Stock", "[Part Number] = '" &
Me.Part_Number & "'")) Then
MsgBox ("Stock Item already entered . Please use Add stock to
increase value. This is for New Stock only. Please press OK to EXIT.")
DoCmd.Close
End If
End Sub


maybe this will help someone else

Thanks
Joe