Validation on a form/subform

[SUZYQ]

In Access 2002, I have a main form that is bound to a table. On that
form there also two other subforms that are bound to tables.

If the user enters a certain document type on the main form, I want to
ensure that there are entries in one of the subforms before the user
can move to another record or create a new one.

What event should I put this code on? I've been trying to do it on the
Before Update event of the main form, which seems to work pretty well.
Is this the best choice? If I put in code Cancel = True then I'm not
able to get into the subform to actually enter the missing data. If I
don't put it in, then I think the record is being committed to the
table which isn't a problem as long as the user then enters the missing
data in the subform.

Also, when the user gets a message box I want to tell them that they
have to enter information in the subform. I then try to set the focus
to a field on the subform, but it's not working. My syntax is
Me.SubformName!FieldName.setfocus.

Any ideas are appreciated.

 

[Wayne Morgan]

The problem you're running into is that when you move to the subform from
the main form, the main form will try to save the record, yet you probably
can't add a record to the subform until you have one on the main form.

Right off-hand, all I can think of would be to get the value of the unique
ID field of the record in the main form in the form's Before or After Update
event. Store it in a Form Level variable. In the form's Current event, check
that one of the subform tables has at least one record for that ID (you
could use the DCount() function for this), if not, return to the previous
record using the remembered ID field value for the move. You could possibly
turn Echo off in the After Update event and turn it on again in the Current
event after doing your check so that the user wouldn't see what is
happening. In the Current event, you would also have to check to see if the
record you're at is the record you're looking for because the event would
fire again when you moved back to that record. If you return to the previous
record, after turning Echo on again, you would pop-up a message advising the
user of the problem. You would also have to check for a record in one of the
subforms in the main form's Unload event and cancel the unload if a record
doesn't exist.

To set the focus to a control on a subform using code, you have to go
through a two step process. First, set the focus to the subform control on
the main form, then to the control on the subform.

Example:
Me.ctlSubformControl.SetFocus
Me.ctlSubformControl.Form.ctlMyControl.SetFocus

Going the other way, subform to main form, is a one step process.