dealing with a subform record when it's "dirty"

[Paul James]

I have a main form that contains a subform. When this form opens, there are
a number of records in the underlying recordset, but there are no records in
the subform. That is, none of the records in the main form have any
corresponding records in the subform. The purpose of this form is to create
those corresponding records in the subform.

I've removed the form's usual navigation buttons, and replaced them with
some custom navigation labels in the main form, so I can run my validation
checks before the user can either navigate to a new record (in the main
form) or close the form. If those conditions are not met, a message box
appears explaining the problem and requiring the user to either fix the
problem, or delete the record in the subform.

Everything works fine as long as the main form has the focus when the user
clicks on one of the navigation controls. In that case, the new record is
displayed in the main form, and the subform has no record. The problem
occurs when one of the subform controls has the focus, and the user clicks
on a navigation button. This is a problem because when the main form moves
to the new record, the record in the subform is what Access calls "dirty."
And if the user then tries to move to another record without making any
conscious entry in the subform, it violates the validation rules.

I've tried a number of things to overcome this, like returning the focus to
a control in the main form in the Mouse Down event of the navigation
control, thinking that if I get OUT of the subform before the main form
moves to the next record, surely the subform record won't be changed. But
no such luck.

I've also tried deleting the "dirty" record in the subform when no values
have been entered, but I get a message saying there's no record to delete.
I also tried using SendKeys {esc}{esc} to clear the subform, but that didn't
have any effect either.

Does anyone have any suggestions about how to deal with this probem that
occurs when you don't want to create empty records in the subform as the
user moves to another record in the main form when a control in the subform
has the focus?

Thanks in advance,

Paul

 

[Allen Browne]

Paul, you will need to understand how the events interact.

The focus cannot move from the subform to the main form without saving the
record. If you are in the subform, and click a button on the main form,
Access fires the BeforeUpdate event of the subform and saves the record
before the Click event of the button on the main form is processed.

 

[Paul James]

Well, you've wet my appetite, Allen, by pointing out the involvement of the
Before Update event, but I'm still at a loss.

If the Before Update event of the subform is fired when you click a
[navigation] button on the main form, and it saves the record in the
subform, it would seem that it would save the subform record that
corresponds to the main form record *before* the nagivation code moves to
the next record in the main form. That would be ok, and if that were the
case, it would seem that if you set the focus to the main form in the
subform's Before Update event, then when the main form moves to the next
record, there would be no record in the subform. But what I'm experiencing
is that even if I move the focus to the main form from the subform's Before
Update event, Access somehow creates a new record in the subform
corresponding to the *next* record in the main form.

This is what I'm trying to avoid. I don't want the user inadvertently
creating records in the subform unless they actually want to enter data in
the fields in the subform.

Any suggestions on how to accomplish this?

Paul

 

[Allen Browne]

Do not SetFocus to something on the main form in the subform's BeforeUpdate
event.

If you do, and the main form is forced to a different record before the
subform record saves, then of course the subform will relate to the wrong
record when it finally does save.

 

[Paul James]

You're right. This does cause a problem. And I'm still confronting the
problem I described in my original message.

Any suggestions on how I can handle this?

Paul

 

[Allen Browne]

Paul, the solution is to learn how the Access events work and work with
them, not against them.

 

[Paul James]

Paul, the solution is to learn how the Access events work and work with

them, not against them.

Before I post a question on these newsgroups, I first try to figure the
answer out on my own. I have several Access texts which I refer to, and I
almost always consult online help in Access. I sometimes go to the MS
Knowledge Base, and also do general searches in Google. I'm not saying I
always do an exhaustive search by consulting all those sources before I post
a question, but I almost always make a reasonable attempt to get an answer
before posting.

I would sure like to know how all Access events work, but I have not been
able to find a source that provides a coherent explanation of how they work
with respect to my original question in this thread. I don't know if it's
just me, but I often have trouble getting the information I need from the
online help in Access, and it's not for lack of trying.

 

[Allen Browne]

The basic idea is that you use a relational design.
Where the primary table has one record, the related table can have many.

The main form is bound to the primary table.
The subform is bound to the related table.

You enter a main form record first. When you enter the subform, the main
form saves to the primary table if it has not already done so.

When you enter something in the subform, it saves to the related table. If
the main form is at a new record, you need to prevent a subform record from
saving. To do that:
1. Open the related table in design view.
2. Select the foreign key field.
3. In the lower pane, set its Required property to Yes.
Now the subform cannot save a record unless the foreign key has inherited a
value from the main form. Since this happens at the end of the subform
record's entry, it is also helpful to cancel the subform's BeforeInsert
event if the main form is at a new record, so the user gets the message as
soon as they start to enter a subform record:
Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter the main form record first."
End If
End Sub

Use the BeforeUpdate event of each form for record-level validation. Do not
try to force the record to save in this event: the event fires because the
record is in the process of being saved. Do not shift focus to the
parent/child in this event: allow it to finish the save. Then use the form's
AfterUpdate event to switch if you have a good reason for doing so.

Note that the controls also have BeforeUpdate and AfterUpdate events, but we
are talking about the events of the Form here.

In summary, use Form_BeforeUpdate for record-level validation, and allow the
event to complete before trying to do something else.

HTH

 

[Paul James]

Thank you so much, Allen, for the detailed explanation of the sequence of
events in creating new records in subforms. This is very helpful to me,
because I've been able to modify my code to get this thing working, based on
your detailed explanation.

Until I read your explanation, I hadn't thought through the details of what
goes on between the underlying recordset of a main form and that of the
(related) subform recordset. But as I read your explanation, it made sense.

Thanks for taking the time to help me get through this.

Paul