Form Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rodney - 12 Jul 2006 11:39 GMT

I have a form which has the following fields, CourseName, courseStartDate,
CourseFinishDate and a subform with the fields ExamName, ExamDate.
I would like to put an after update event procedure on ExamDate to restrict
entry to greater then coursestartdate and less then coursefinishdate also
with an error message if the ExamDate is outside the course Start and finish
date.
Is this possible

Thank you
Rodney

Reply to this Message

Allen Browne - 12 Jul 2006 12:49 GMT

To compare fields, you need to use the BeforeUpdate event of the form,
rather than the AfterUpdate of the text box. That way, it catches the error
and prevents the save even if the user goes back and changes one of the
other text boxes after entering the ExamDate.

This kind of thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Me.ExamDate < Me.CourseStartDate) OR _
(Me.ExamDate > Me.CourseFinishDate) Then
Cancel = True
MsgBox "Exam must be during the course." & vbcrlf & _
"Correct the entry, or press <Esc> to undo."
End If
End Sub

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a form which has the following fields, CourseName, courseStartDate,
> CourseFinishDate and a subform with the fields ExamName, ExamDate.
[quoted text clipped - 8 lines]
> Thank you
> Rodney

Reply to this Message

Rodney - 12 Jul 2006 13:01 GMT

Allen,
Thank you so much for this and I see what you mean with the BeforeUpdate.
Thank's again
Use guys are so good
Rodney

> To compare fields, you need to use the BeforeUpdate event of the form,
> rather than the AfterUpdate of the text box. That way, it catches the error
[quoted text clipped - 24 lines]
> > Thank you
> > Rodney

Reply to this Message

Rodney - 13 Jul 2006 00:38 GMT

Allen
I have tryed your suggestion but can't seen to get it to work. This is what
I have

I have a Form called frmAddExamToDivision this form has 4 fields called
CourseName, DivisionName, StartDate, FinishDate and a Subform called
frmAddExamToDivisionSubform.

The frmAddExamToDivisionSubform has 3 field called ExamName, ExamDate,
InstructorDetails.

I have tryed this but no luck

Private Sub Form_BeforeUpdate(Cancel As Integer)

If (Me.ExamDate < Me.StartDate) Or _
(Me.ExamDate > Me.FinishDate) Then
Cancel = True
MsgBox "Exam must be during the course." & vbCrLf & _
"Correct the entry, or press <Esc> to undo."
End If
End Sub
and this
Private Sub Form_BeforeUpdate(Cancel As Integer)

If
(Me.Forms.frmAddExamToDivision.frmAddExamtoDivisionSubform.Form.ExamDate
< Me.Forms.frmAddExamToDivision.StartDate) Or _
(Me.Forms.frmAddExamToDivision.frmAddExamtoDivisionSubform.Form.ExamDate

> Me.Forms.frmAddExamToDivisionFinishDate) Then

Cancel = True
MsgBox "Exam must be during the course." & vbCrLf & _
"Correct the entry, or press <Esc> to undo."
End If
End Sub

and still no luck
Can you please lend some more assistance
Thank you
Rodney

> Allen,
> Thank you so much for this and I see what you mean with the BeforeUpdate.
[quoted text clipped - 30 lines]
> > > Thank you
> > > Rodney

Reply to this Message

Allen Browne - 13 Jul 2006 05:23 GMT

In the subform's module, refer to the text boxes on the parent form as:
Me.Parent.ExamDate
Me.Parent.StartDate

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen
> I have tryed your suggestion but can't seen to get it to work. This is
[quoted text clipped - 76 lines]
>> > > Thank you
>> > > Rodney

Reply to this Message