Validate value exists in another table

[Guest]

I have a field in a sub-form where I want to validate that the value they
enter is a valid record in a different table. I’m not using a drop down
because the list could grow considerably. I am enforcing referential
integrity through table relationships but I would like to cancel/undo the
transaction if this occurs. How can I accomplish this?

 

[Guest]

You could use the BeforeInsert and/or beforeupdate event in the subform.
Their you can check whether the value exists in the other table if not you
can cancel the update.

- Raoul

 

[Guest]

Thanks, I should have been more specific about my request. It's the code I
need for the BeforeUpdate event. My VB is pretty weak. I saw some sample code
in another application with one difference being that in my case it's a
subform and the following example is not:
Dim str As String
str = Me.ComponentID
If IsNull(DLookup("[PartID]", "tblMfg", "[PartID]= '" & str & "'")) Then
MsgBox "Component does not exist, check name"
DoCmd.RunCommand acCmdUndo
Cancel = True
End If