Validation between fields in different tables in Access

[kiwipedia]

Hi everyone - here is my problem:

I've got an Access 2002 database with a table [demographics] and a field (not the primary key) called [dateofbirth]. This table has a one to many relationship with a table called [lines]. [Lines] includes a field called [insertiondate].

I've built a subform based on [lines] with parent form [demographics]. I want to validate [insertiondate] against [dateofbirth] ensuring that [insertiondate] is not before [dateofbirth].

Is this possible - can I validate one field against another in a different table?

Thanks - Michael

[kiwipedia]

Well I've got my DOB data onto the subform using DLookup to extract the appropriate DOB from the demographics table.
The code runs whenever the insertion date gets focus.

Private Sub Insertion_GotFocus()
Dim datDOB As Date
datDOB = DLookup("DOB", "demographics", "[NHI] = Form![NHI]")
If (Not IsNull(datDOB)) Then Me.DateofBirth = datDOB
End Sub 

Should be easy enough to now validate the Insertion date against the DOB - I'll hopefully finish it off tomorrow.

Michael.

[NeoPa]

If your [DateofBirth] control is on the main form and you have [Insertion] on the sub-form then try :

Private Sub Insertion_BeforeUpdate(Cancel As Integer)
  If Me.Insertion <= Me.Parent.DateofBirth Then
    'MsgBox() is optional
    Call MsgBox("Error Message", , "Title")
    Cancel = True
  End If
End Sub

[kiwipedia]

If your [DateofBirth] control is on the main form and you have [Insertion] on the sub-form then try :

Private Sub Insertion_BeforeUpdate(Cancel As Integer)
  If Me.Insertion <= Me.Parent.DateofBirth Then
    'MsgBox() is optional
    Call MsgBox("Error Message", , "Title")
    Cancel = True
  End If
End Sub

Thanks NeoPa - a much more efficient solution and works nicely.

Michael.

[NeoPa]

Thanks NeoPa - a much more efficient solution and works nicely.

Michael.

Not a problem Michael.
I'm glad it worked for you :)