Validate value exists in another table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

J - 14 Mar 2005 23:51 GMT

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?

Reply to this Message

JaRa - 15 Mar 2005 00:13 GMT

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

> 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?

Reply to this Message

J - 15 Mar 2005 16:29 GMT

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

> 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
[quoted text clipped - 7 lines]
> > integrity through table relationships but I would like to cancel/undo the
> > transaction if this occurs. How can I accomplish this?

Reply to this Message