Hi all
I have just spent 2 hours on the search function trying to solve my problem and have tried 2 solutions that have half worked but still haven't worked properly.
I have a main form with a linked subform. On my sub form i have a field 'Date of first appointment'. Currently users can just exit the main form without entering data into this field if they don't fill out any part of the subform (dont create a record in the subform).
The first solution I used was this code on the BeforeUpdate of the main form.
This prevented the user exiting the main form without filling out the 'Date of first appointment' field but it also prevented the user from tabbing from the mainform to access the subform and update this field!
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.WR_Referrals_Subform.Form.Date_of_first_appointment) Then
MsgBox "Date of first appointment is a required field", vbCritical, "Field required "
Cancel = True
Me.WR_Referrals_Subform.Form.Date_of_first_appointment.SetFocus
Exit Sub
End If
End Sub
With the above solution it appears the SetFocus command fails to work and the focus won't stray from the main form.
The second solution was the Better Mouse Trap code but i experienced the same problem, it wouldn't let the user move from the main form to the required subform field once it had advised the user the subform field counldn't be left blank. Any attempt to do this was treated like the user was trying to close the main form.
Any help would be greatly appreciated. It is vital in my db that this field is filled out. Oh, and i'm a bit of a novice at this as well so the solution might be easy.
Thanks
I have just spent 2 hours on the search function trying to solve my problem and have tried 2 solutions that have half worked but still haven't worked properly.
I have a main form with a linked subform. On my sub form i have a field 'Date of first appointment'. Currently users can just exit the main form without entering data into this field if they don't fill out any part of the subform (dont create a record in the subform).
The first solution I used was this code on the BeforeUpdate of the main form.
This prevented the user exiting the main form without filling out the 'Date of first appointment' field but it also prevented the user from tabbing from the mainform to access the subform and update this field!
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.WR_Referrals_Subform.Form.Date_of_first_appointment) Then
MsgBox "Date of first appointment is a required field", vbCritical, "Field required "
Cancel = True
Me.WR_Referrals_Subform.Form.Date_of_first_appointment.SetFocus
Exit Sub
End If
End Sub
With the above solution it appears the SetFocus command fails to work and the focus won't stray from the main form.
The second solution was the Better Mouse Trap code but i experienced the same problem, it wouldn't let the user move from the main form to the required subform field once it had advised the user the subform field counldn't be left blank. Any attempt to do this was treated like the user was trying to close the main form.
Any help would be greatly appreciated. It is vital in my db that this field is filled out. Oh, and i'm a bit of a novice at this as well so the solution might be easy.
Thanks
