Using BeforeUpdate Event to set the focus onto the subform

[Joe Joplin]

I have a form to enter invoices. The form is similar to the Service Call
Management sample db "Work Orders" form (included with ACC2000). My invoice
form has a subform that has labor information about the invoice. I am using
the BeforeUpdate event on the main form to make sure there is data in the
labor subform before moving on. I am using IsNull (DLookup("subfrmID",
"tblSubFormData","Customer ID = " & Me.cmbCustomerID)) to check for subform
data. This part seems to work fine. After I catch no data in the sub form
I am trying to use the DoCmd.CancelEvent followed by setting the focus to a
control in the subform, but it won't allow me to set focus to the control in
subform. I looked at ACC2000 How to Set Focus to a Subform Control Using
GoToControl (Q209689) and used that technique, but it still would not work.
Without setting the focus on the subform I am getting caught in a loop based
on the Before Update event.

Any suggestions?

TIA,

Joe

 

[Reggie]

Joe, Anytime you move the focus from the current record to another
control(i.e.. your subform) then access will try to save the current record
before doing so. Therefore each time you are moving the focus from the
record on your main form to the subform the before update event is fired.
Having said that you need to not cancel the update, but rather let it update
then move the focus to the subform using the after update event, or
explicitly save or undo the current record on the main form then move the
focus.