Sidestepping validation code on entering subform

[Guest]

This must be quite a common problem but I can't find a solution so any help
greatly appreciated...

I have some detailed validation code in a main form's Before Update event.
Half way through entering a new record into the form, the user must enter
some data in a subform. The problem is that moving into the subform forces
the main form's new record to update which triggers the Before Update
validation code: something I don't want to happen till the user has had the
chance to fill in the rest of the main form. Is there any way to capture
this? I'm happy for the Before Update to run (if it must!) but I'd like
another event that happens before it or some trappable property that I can
perhaps put into an If to avoid my detailed validation code running until the
user has finished inputting the whole record - subform and main form.

Thanks

 

[Allen Browne]

No. Access saves each form's record independently of each other, so you
cannot have a combined rule like that.

In a typical scenario, the main form is bound to a table, and the subform is
bound to a related table. It primary record must be saved in the main table
before you can create a related record in the related table. It follows that
the main form must complete its save before you can start entering the
record in the subform.

You will need to revise your validation of the main form's record so that it
is not dependent on anything in the subform.

 

[Guest]

Thanks Allen - I understand what you're saying: my main form's Before Update
event must run before entering the subform. However, the validation code I'm
talking about is only concerned with main form data and I'm just trying to
find a way to identify that the user is putting the cursor into the subform -
that way, I can then use an If in my Before Update to skip the validation
code that isn't relevant at this stage (the user has plenty more to get to on
the rest of the main form after they've finished with the subform and they
don't need to hear they haven't filled-in some boxes yet!).

Can you - or anyone else - think of a property or perhaps another event that
sets a public variable that could identify that the user has entered the
subform BEFORE the main form's Before_Update event runs? The subform's
events all happen after Before_Update and I've tried Screen.ActiveControl but
that only talks about the control the cursor was on before the user clicks
on/tabs to the subform.

 

[Allen Browne]

I guess I don't follow the logic of this.

You want to have validation code that prevents Access from accepting data in
the main form, but allow the record to be saved without executing that code
when the user is going into the subform, with no guarantee they will ever
come back and finish the main form record which is already saved in an
incomplete state?

Sorry: I don't follow that at all.

 

[Guest]

Yes - obviously I'll want to re-invoke the validation code once the user
clicks back into the main form or moves to another main form record (I'll
probably use the subform's On Exit for this). You see, there's a natural
order for this information to be entered - the subform is just one part of a
middle page in a big tab control - and I don't want most of my validation
code to run till the user feels they've filled-in the whole main form record.

 

[Guest]

Martin,

Allen is trying to tell you how it works. The main issue here is your
design. You need to reconsider how the data entry flow is done. There are
some things you cannot get around in Access or any other relational database.
1. You cannot create a child record until there is a parent record.
2. When you move from the main form to the sub form, Access will update the
main form record. Saving the record at this time without validation may mean
incorrect data being saved.

The correct approach it to complete a parent record before adding the first
child record. I can't think of a reason why you would need to enter any
child data before the parent record is complete.

 

[Guest]

OK, thanks you two: I'll change it around so that the subform is last (though
the users won't like it). Luckily there isn't more than one subform needed
for the design - else it would still be a problem. I'm still sure there must
be a way to capture moving to a subform before the main form's Before_Update
event (I'm not talking about stopping the primary key being updated, just a
lot of text fields I've been asked to make mandatory).

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George