Validate data by reference to a different table

[HeatherM]

My database has two tables [Projects] and [Transactions]
linked by a reference no. The Projects table includes a
field [Live] which shows whether the project is a live
project(-1) or not(0) Transactions are entered using an
input form. Can I get the form to check the [Live] field
in [Projects] once the reference no has been entered to
prevent transactions being entered to a non-live project.
The [Live] field does not appear in the [Transactions]
table or form

 

[Steve Schapel]

Heather,

A couple of related approaches...
I would probably make a Query which includes both the Projects and
Transactions tables, and base your data form on this. Than way, you
can include the Live field on the form, and refer to it in the Form's
BeforeUpdate event, something like...
If Me.Live = 0 Then
MsgBox "Invalid entry... Project " & Me.Reference_no & " not live!"
Cancel = True
Me.Undo
End If

Another way is to use a domain function to check the Live field, and
again use BeforeUpdate code similar to the above, with the first line
more like...
If DLookup("[Live]","Projects","[Reference no]=" & Me.Reference_no) =
0 Then

- Steve Schapel, Microsoft Access MVP