Events between Forms and subForms

[Guest]

I have a form, which contains (among other controls) a text field and a
sub-form, containing one field displayed as a continuous form.

when I move from one record to the other, I want to check that the field has
a value and the subform holds at least one value.

I'm using the BeforeUpdate event on the form to do this, checking IsNull for
the field and Me.frm_SubForm.Form.Recordset.RecordCount = 0 for the subform.

This works fine for changing records, but the BeforeUpdate event fires when
I move from the form into the subform, spots that there's no data in the
subForm's recordset and plonks me back into the previous field of the main
form.

I've been trying to find a reference to the sequence of events that fires
when moving from a form to a subform, but with no luck.

Is this sequence documented anywhere? Does anyone have any suggestions as to
how I can get around this problem?

Thanks in advance,

Adrian

 

[strive4peace]

Current event
---

Hi Adrian,

instead of using BeforeUpdate, use the form Current event -- this fires
whenever a different record is made active

for learning more about the order of events, press F1 in the property
sheet on various events. Some of the Help does list the order

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi Crystal,

I'd tried the Current event, since the help says:

"When you move the focus to an existing record on a form, enter or change
data in the record, and then move the focus to another record, the following
sequence of events occurs for the form:

"Current (form) → BeforeUpdate (form) → AfterUpdate (form) → Current (form)"

....but it doesn't seem to fire *before* I leave the current record. (I have
both Current and BeforeUpdate events set for the form)

(This is Access 2003, BTW)

Thanks again.

Adrian

 

[strive4peace]

Hi Adrian,

what is the purpose for checking this?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi Crystal

I need to validate that the field contains a value and the subForm contains
at least one value,

The obvious solution seemed to be to use BeforeUpdate, which is what I've
used previously to validate the data in fields on a single form.

The wrinkle here, is the subform.

I start by clicking a checkbox on an existing record to enable the subform.
When I move focus to a field in the subform, the first event to fire on the
main form is BeforeUpdate. This validates as described above, finds the
subform empty (because it's only just been enabled) and cancels the event.

What I need to find is a way to validate the form's contents when switching
to another record, but to suspend the validation when the subform is
selected. I can't find a way to distinguish between the two occasions when a
BeforeUpdate is triggered.

If it's any help, I've uploaded a database with just a cut-down version of
the form and subform to:

http://www.listicath.co.uk/Access/TestingSubForm.mdb

For some background, the real form is used to define a clinic, which may
have a number of timeslots, or none (the checkbox). There can be any number
of timeslots, which would be entered in the subform. The numerical field is
the maximum number of clients who can be booked in to a timeslot.

I'm sure there must be a way to do this, but for the moment, I'm absolutely
stumped.

Thanks again

Adrian

 

[strive4peace]

Hi Adrian,

the problem is that when you click into a subform, the main form is
saved. Forms should be set up such that the main record is created
before related records

if you wish to ensure there will be certain data in the subform, you
could put unbound controls on the main form and, if they are not filled,
do not allow the main form record to be updated or saved.

If they are filled, you can

1.use SQL to append the record to the subform
or
2. use the subform BeforeInsert event to assign the relevant data


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi Crystal

I'd realised that the form was being saved when the subform was entered.
That made sense, since the table behind the subform is linked to the table
behind the main form.

I just hoped that there would be an event triggered before the BeforeUpdate
event so I could set a flag to show that it wasn't moving off this record to
another.

As I mentioned, the order of events triggered when clicking on field in a
subform doesn't appear to be documented anywhere. I've trapped just about
every event in the form now, and come to the conclusion that it'll take a
*lot* more work than it's probably worth.

Perhaps standing over my users with a big stick would be a more effective
validation technique.

Thanks again, anyway,

Adrian

 

[strive4peace]

Hi Adrian,

"standing over my users with a big stick"

LOL! But if it works...

Another thing you could do is record the primary key value on the
Current event using a global variable or a hidden form control. Then,
when the Current event fires again, you can compare this value to the
value in the current record. If it is different, you could use use a
dLookup to see if there is a related record and, if not, delete the main
record using SQL

Assuming you have an autonumber or Long Integer primary key:

'do this in the module heading before any procedures so it will be global

dim gIDvar as long

on the form Open event:
'initialize global variable
gIDvar = 0


form Current event:
dim s as string

if me.ID_controlname <> gIDvar then
'test to see if there are records in related table
'if not, delete main record if desired

s = "DELETE * FROM MainTablename WHERE IDfield = " & gIDvar
currentdb.execute s
end if

if me.newrecord then
gIDvar =0
else
gIDvar = me.ID_controlname
endif


form AfterInsert event:
gIDvar = me.ID_controlname


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*