Force entry on subform before main form will save

[Tracey]

I am using BeforeUpdate events (thanks to previous posts on this forum) to
ensure users complete all the required fields on forms before the form will
save. It works fine except on one form where there is a subform. When I
complete entry on the main form and want to move to the subform to complete
the required entry, my message that "the record won't save until all items on
the form are completed" fires and the focus won't move to the subform. I
don't want the message to fire unless I haven't completed entry on the form
AND the subform. The name of my form is fHDHPAdd and the name of my subform
is fHDHPSalaryBand_subfrm. The control that must be completed on the subform
(in addition to controls on the main form) is SalaryBand. Using Access 2003.

Here is my code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me![fHDHPSalaryBand subfrm].Form!SalaryBand) Then
Cancel = True
strMsg = strMsg & "Must complete data entry on this form before
record will be saved." & vbCrLf
End If

If Cancel Then
strMsg = strMsg & "Complete entry on this record or it will not be
saved."
MsgBox strMsg, vbExclamation, "Invalid data"
End If
End Sub

Can someone point out my error and a fix? Thanks.

[Steve Schapel]

Tracey,

It is not possible to move your focus to the subform, without the main
form record being saved. You will need to validate the main form record
on the main form's Before Update event, and validate the subform's
record on the subform's Before Update event.

--
Steve Schapel, Microsoft Access MVP

[Tracey]

Thank you. Is there something I can do after the record on the main form is
saved to force the user to enter data on the subform?

[George Nicholson]

One approach: 1) Set the focus to a control on the subform, 2) set the
subform's Dirty property to True. 3) Put a validation routine in the
subform's BeforeUpdate event.

The user won't be able to exit the subform or move to a new record in the
subform until the BeforeUpdate validation is successful and the current
record is saved (which sets Dirty to False).

HTH,
--
George Nicholson

Remove 'Junk' from return address.

"Tracey" <Tra...@discussions.microsoft.com> wrote in message
news:A0B7994F-7C06-4477...@microsoft.com...

[Steve Schapel]

I can't think of a better idea than that, George. The only time it
might need some refinement is if the subform data is already complete,
at the time the main form record is updated. If this is a possible
scenario, maybe a messagebox confirmation of the forcing of the subform?

--
Steve Schapel, Microsoft Access MVP

[George Nicholson]

But if the subform already had a complete set of data, it would fly through
any Before Update validation check with flying colors, wouldn't it? Worst
case, you'd just be replacing the "old" subform record with identical data
(except for any UpdatedBy or UpdatedOn fields).

--
George Nicholson

Remove 'Junk' from return address.

"Steve Schapel" <sch...@mvps.org.ns> wrote in message
news:Os5L1fIG...@TK2MSFTNGP12.phx.gbl...

[Steve Schapel]

That's right, George. I was getting confused thinking there wouldn't be
a Before Update event, but no problem, you've already got that covered! :-)