Validation in child form

[joceychin]

Hi

I need help to validate a date field in the child form please.

Child form :

Autonumkey ID Date Visit1
10 A12 10-10-2012 V1

14 A12 13-10-2012 V2

17 A12 12-10-2012

When i enter Autonum 17, the date is earlier than the last available record (aka V2). May i know how can i program a validation rule to check that in this child form that the new record date cannot be earlier than the previous record ?

For some help please.
Thank you
BEst Regards
Jocey

 

[John W. Vinson]

Hi

I need help to validate a date field in the child form please.

Child form :

Autonumkey ID Date Visit1
10 A12 10-10-2012 V1

14 A12 13-10-2012 V2

17 A12 12-10-2012

When i enter Autonum 17, the date is earlier than the last available record (aka V2). May i know how can i program a validation rule to check that in this child form that the new record date cannot be earlier than the previous record ?

For some help please.
Thank you
BEst Regards
Jocey

Well, there is no such thing as "the previous record" in an Access table. A
table is an unordered "bag" of data.

What you could do to ensure that a newly entered date is greater than any
existing date is with code in the BeforeUpdate event of the Date field. Note:
Date is a reserved word (for the builtin Date() function) and should not be
used as a fieldname. Try changing the fieldname to VisitDate. I'm assuming
that the ID field is the child link field of the form and that you want this
date to be greater than any other date for this ID. If so, tryh code like

Private Sub VistiDate_BeforeUpdate(Cancel as Integer)
Dim vMaxdate As Variant
If IsDate(Me!VisitDate) Then ' only check actual date entries
vMaxdate = DMax("[VisitDate]", "ChildTableName", _
"[ID] = '" & Me![ID] & "'")
If Not IsNull(vMaxdate) Then ' don't check first record for this ID
If Me!VisitDate < vMaxdate Then
Cancel = True
MsgBox "Please enter a date after " & Format(vMaxdate, "Short Date")
End If
End If
End If
End Sub
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com