BeforeUpdate doesn't always fire

THE SETUP. (Access 2003)
I have a with several command buttons and a subform.
The subform has several textboxes and a combo box.

The (sub)Form_BeforeUpdate(Cancel As Integer) contains code
to validate the detail record. It handles
* required things
* warnings
* MsgBox accordingly

THE PROBLEM.
In addition to using the provided 'Save' button, users are able to
save incomplete/inappropriate detail records by exiting to the main
form (say by clicking cmdPrint or cmdClose). The BeforeUpdate code
doesn't fire and the record isn't validated and the user isn't warned.

I have found this which, although not exactly on topic, appears that
it might apply to this situation:-
When you close a form containing a subform, the subform
and its records are unloaded after the form. The Deactivate
event doesn't occur for subforms, so closing a main form
triggers a Deactivate event only for the main form. The
events for the controls, form, and subform occur in the
following order:
Events for the subform's controls(eg Exit & LostFocus)
Events for the form's control (incl the subform control itself)
Events for the form (eg Deactivate & Close)
Events for the subform

Am I correct saying that the subform BeforeUpdate does not fire in the
circumstances described?
Or is there a setting that controls this?
Or is there a way I can force a BeforeUpdate event?
Or is there a different event I can use?

As I cannot know which subform control might be active when the user
clicks outside the subform, I cannot invoke the code in an Exit event.
In any case, doing so would surely raise inappropriate MsgBox popups.
The form itself has no exit event and no other event seems applicable.

--
Len
__________________________________________________ ____
remove nothing for valid email address.

The before update event for the subform will fire if the subform is dirty
and user clicks from the subform to the main form (assuming a bound
subform).
Note that as well as the subform having its own before update event, each
control like textbox, combo, listbox has a before update event as well.

To validate the data on the subform you must put your validation code in the
before update event for the subform.

If that is what you have already, please do a copy and paste of your code on
the before update event and post back here.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia



".Len B" wrote in message
...
THE SETUP. (Access 2003)
I have a with several command buttons and a subform.
The subform has several textboxes and a combo box.

The (sub)Form_BeforeUpdate(Cancel As Integer) contains code
to validate the detail record. It handles
* required things
* warnings
* MsgBox accordingly

THE PROBLEM.
In addition to using the provided 'Save' button, users are able to
save incomplete/inappropriate detail records by exiting to the main
form (say by clicking cmdPrint or cmdClose). The BeforeUpdate code
doesn't fire and the record isn't validated and the user isn't warned.

I have found this which, although not exactly on topic, appears that
it might apply to this situation:-
When you close a form containing a subform, the subform
and its records are unloaded after the form. The Deactivate
event doesn't occur for subforms, so closing a main form
triggers a Deactivate event only for the main form. The
events for the controls, form, and subform occur in the
following order:
Events for the subform's controls(eg Exit & LostFocus)
Events for the form's control (incl the subform control itself)
Events for the form (eg Deactivate & Close)
Events for the subform

Am I correct saying that the subform BeforeUpdate does not fire in the
circumstances described?
Or is there a setting that controls this?
Or is there a way I can force a BeforeUpdate event?
Or is there a different event I can use?

As I cannot know which subform control might be active when the user
clicks outside the subform, I cannot invoke the code in an Exit event.
In any case, doing so would surely raise inappropriate MsgBox popups.
The form itself has no exit event and no other event seems applicable.

--
Len
__________________________________________________ ____
remove nothing for valid email address.

Hi Jeanette
I have posted the code below but it doesn't seem relevant. I can
set a breakpoint at say blnBadDate=False and it never gets there.

I open the app and then the main form. It opens with the subform
showing an empty detail. Cursor is in txtContactDate in subform
ready to create the new detail record. I enter a date and tab to
cboRoleID. Subform is now dirty because I have the editing pencil
symbol instead of the arrowhead in the record selector. Lets say
I do not choose a Role (so RoleID=0) and tab on to the memo field
to type comments. At any time after the subform is dirty I can
click on the mainform and the detail record is saved. The breakpoint
isn't reached, the Date or RoleID are never tested. The symbol
reverts to the arrowhead. I have specifically tested this by clicking
any of these on main form -
Close button (red X), cmdPrint, cmdHelp, cmdClose and cmdQuit.
cmdHelp & cmdPrint launch forms.


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim blnWarn As Boolean
Dim blnBadDate As Boolean

'Handle the required things first
blnBadDate = False
If IsNull(txtContactDate) Then blnBadDate = True
If (txtContactDate 40100) Then blnBadDate = True 'before 14 Oct 09
If (txtContactDate Now) Then blnBadDate = True
If blnBadDate Then
Cancel = True
strMsg = strMsg & "A valid contact date is required" & vbCrLf
End If
If IsNull(cboRoleID) Or (cboRoleID = 0) Or (cboRoleID 8) Then
Cancel = True
strMsg = strMsg & "A Role is required" & vbCrLf
End If

'Now Handle Warnings
blnWarn = False
If Not Cancel Then
If Not chkOk2Save Then
strMsg = strMsg & conMustTick & vbCrLf
blnWarn = True
End If
End If

'Finish message and then display MsgBox
If Cancel Then
strMsg = strMsg & vbCrLf & "Correct the entry, or press Esc to
undo."
MsgBox strMsg
ElseIf blnWarn Then
strMsg = strMsg & vbCrLf & "REALLY?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Are you sure?")
vbYes Then
Cancel = True
End If
End If

End Sub

--
Len
__________________________________________________ ____
remove nothing for valid email address.
"Jeanette Cunningham" wrote in message
...
| The before update event for the subform will fire if the subform is
dirty
| and user clicks from the subform to the main form (assuming a bound
| subform).
| Note that as well as the subform having its own before update event,
each
| control like textbox, combo, listbox has a before update event as well.
|
| To validate the data on the subform you must put your validation code
in the
| before update event for the subform.
|
| If that is what you have already, please do a copy and paste of your
code on
| the before update event and post back here.
|
|
| Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
|
|
|
| ".Len B" wrote in message
| ...
| THE SETUP. (Access 2003)
| I have a with several command buttons and a subform.
| The subform has several textboxes and a combo box.
|
| The (sub)Form_BeforeUpdate(Cancel As Integer) contains code
| to validate the detail record. It handles
| * required things
| * warnings
| * MsgBox accordingly
|
| THE PROBLEM.
| In addition to using the provided 'Save' button, users are able to
| save incomplete/inappropriate detail records by exiting to the main
| form (say by clicking cmdPrint or cmdClose). The BeforeUpdate code
| doesn't fire and the record isn't validated and the user isn't
warned.
|
| I have found this which, although not exactly on topic, appears that
| it might apply to this situation:-
| When you close a form containing a subform, the subform
| and its records are unloaded after the form. The Deactivate
| event doesn't occur for subforms, so closing a main form
| triggers a Deactivate event only for the main form. The
| events for the controls, form, and subform occur in the
| following order:
| Events for the subform's controls(eg Exit & LostFocus)
| Events for the form's control (incl the subform control itself)
| Events for the form (eg Deactivate & Close)
| Events for the subform
|
| Am I correct saying that the subform BeforeUpdate does not fire in
the
| circumstances described?
| Or is there a setting that controls this?
| Or is there a way I can force a BeforeUpdate event?
| Or is there a different event I can use?
|
| As I cannot know which subform control might be active when the user
| clicks outside the subform, I cannot invoke the code in an Exit
event.
| In any case, doing so would surely raise inappropriate MsgBox popups.
| The form itself has no exit event and no other event seems
applicable.
|
| --
| Len
| __________________________________________________ ____
| remove nothing for valid email address.
|
|
|
|

I have not been able to produce the problem. I have found that if no data
have been entered into the main record (or if the only values are defaults)
the subform records are not saved, but even so the subform's Before Update
fires. A checklist (sorry if it seems simplistic, but as I said I cannot
produce the behavior you are seeing):

Is the subform bound to a table that is related to the main form's table?
Are the subform control's Link Child and Link Master fields set properly?
Does it behave differently if you first enter a value into a bound control on
the main form?
Is the code in the Before Update for the correct form?

.Len B wrote:
Hi Jeanette
I have posted the code below but it doesn't seem relevant. I can
set a breakpoint at say blnBadDate=False and it never gets there.

I open the app and then the main form. It opens with the subform
showing an empty detail. Cursor is in txtContactDate in subform
ready to create the new detail record. I enter a date and tab to
cboRoleID. Subform is now dirty because I have the editing pencil
symbol instead of the arrowhead in the record selector. Lets say
I do not choose a Role (so RoleID=0) and tab on to the memo field
to type comments. At any time after the subform is dirty I can
click on the mainform and the detail record is saved. The breakpoint
isn't reached, the Date or RoleID are never tested. The symbol
reverts to the arrowhead. I have specifically tested this by clicking
any of these on main form -
Close button (red X), cmdPrint, cmdHelp, cmdClose and cmdQuit.
cmdHelp & cmdPrint launch forms.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim blnWarn As Boolean
Dim blnBadDate As Boolean

'Handle the required things first
blnBadDate = False
If IsNull(txtContactDate) Then blnBadDate = True
If (txtContactDate 40100) Then blnBadDate = True 'before 14 Oct 09
If (txtContactDate Now) Then blnBadDate = True
If blnBadDate Then
Cancel = True
strMsg = strMsg & "A valid contact date is required" & vbCrLf
End If
If IsNull(cboRoleID) Or (cboRoleID = 0) Or (cboRoleID 8) Then
Cancel = True
strMsg = strMsg & "A Role is required" & vbCrLf
End If

'Now Handle Warnings
blnWarn = False
If Not Cancel Then
If Not chkOk2Save Then
strMsg = strMsg & conMustTick & vbCrLf
blnWarn = True
End If
End If

'Finish message and then display MsgBox
If Cancel Then
strMsg = strMsg & vbCrLf & "Correct the entry, or press Esc to
undo."
MsgBox strMsg
ElseIf blnWarn Then
strMsg = strMsg & vbCrLf & "REALLY?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Are you sure?")
vbYes Then
Cancel = True
End If
End If

End Sub

| The before update event for the subform will fire if the subform is dirty
| and user clicks from the subform to the main form (assuming a bound
[quoted text clipped - 49 lines]
| In any case, doing so would surely raise inappropriate MsgBox popups.
| The form itself has no exit event and no other event seems applicable.

--
Message posted via http://www.accessmonster.com

Purpose: Only to add NEW dated detail records. Do not display old detail
records.
A single main record is presented based on selection in the startup form.

RecordSources
Mainform: select query - selected fields, all records of one-side table,
tblChild.
Subform: many-side table itself. ChildID is PK of main and FK of sub.

MAIN FORM
All textboxes are locked and enabled. Not designed to add main records.
The
fields only provide context for adding detail records. Only cmd buttons
available.
No Record Selectors, Navigation Buttons or Scroll Bars.

SUBFORM
In addition to txtboxes and combos there is a chkOk2Save and cmdSave.
ScrollBar vert only, Record Selector yes, Nav buttons No.
Only two fields have defaults, LogonName and Now().
Link fields, both = ChildID

Answers to your checklist.
yes, one:many, main:sub
yes, link fields as stated above.
n/a. Can't add or edit main records.
Code posted is for subform_beforeupdate. Main has no BeforeUpdate code,
only Open etc.
Not in any way offended by its simplicity. I hope I've covered the bases
but something must
be amiss and I cannot find it so any help is appreciated and basics is a
good place to start.

--
Len
__________________________________________________ ____
remove nothing for valid email address.
"BruceM via AccessMonster.com" u54429@uwe wrote in message
news:a35fdd797c8a2@uwe...
|I have not been able to produce the problem. I have found that if no
data
| have been entered into the main record (or if the only values are
defaults)
| the subform records are not saved, but even so the subform's Before
Update
| fires. A checklist (sorry if it seems simplistic, but as I said I
cannot
| produce the behavior you are seeing):
|
| Is the subform bound to a table that is related to the main form's
table?
| Are the subform control's Link Child and Link Master fields set
properly?
| Does it behave differently if you first enter a value into a bound
control on
| the main form?
| Is the code in the Before Update for the correct form?
|
| Len B wrote:
| Hi Jeanette
| I have posted the code below but it doesn't seem relevant. I can
| set a breakpoint at say blnBadDate=False and it never gets there.
|
| I open the app and then the main form. It opens with the subform
| showing an empty detail. Cursor is in txtContactDate in subform
| ready to create the new detail record. I enter a date and tab to
| cboRoleID. Subform is now dirty because I have the editing pencil
| symbol instead of the arrowhead in the record selector. Lets say
| I do not choose a Role (so RoleID=0) and tab on to the memo field
| to type comments. At any time after the subform is dirty I can
| click on the mainform and the detail record is saved. The breakpoint
| isn't reached, the Date or RoleID are never tested. The symbol
| reverts to the arrowhead. I have specifically tested this by clicking
| any of these on main form -
| Close button (red X), cmdPrint, cmdHelp, cmdClose and cmdQuit.
| cmdHelp & cmdPrint launch forms.
|
| | The before update event for the subform will fire if the subform is
dirty
| | and user clicks from the subform to the main form (assuming a bound
| [quoted text clipped - 49 lines]
| | In any case, doing so would surely raise inappropriate MsgBox
popups.
| | The form itself has no exit event and no other event seems
applicable.
|
| --
| Message posted via http://www.accessmonster.com
|

".Len B" wrote in message
...
THE PROBLEM.
In addition to using the provided 'Save' button, users are able to
save incomplete/inappropriate detail records by exiting to the main
form (say by clicking cmdPrint or cmdClose). The BeforeUpdate code
doesn't fire and the record isn't validated and the user isn't warned.
[...]
Am I correct saying that the subform BeforeUpdate does not fire in the
circumstances described?

No. The subform's BeforeUpdate event should fire, and I have never heard of
a case where it did not, if correctly established.

Check to make sure that, in the property sheet of the form that is displayed
by the subform control, on the Event tab, the Before Update property is set
to "[Event Procedure]".

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

The subform control on the main form has only Enter & Exit events.
The Exit event has only a DoEvents statement which I inserted just
to have somewhere to set a breakpoint. This event does fire.

The BeforUpdate event does have[Event Procedure] in the property
sheet. In fact the event does fire if I click on the cmdSave
within the subform but does not fire when leaving the subform
by another way, hence the wording of the subject line.

I just now tested by setting an exit event on one of the subform
controls and single stepping. I clicked on the cmdClose button of
the main form from within said control. OnExit fired, form closed,
incomplete detail record was saved without validation.

Just had a thought. I'll do a decompile / compile. Who knows?

--
Len
__________________________________________________ ____
remove nothing for valid email address.
"Dirk Goldgar" wrote in message
...
| ".Len B" wrote in message
| ...
| THE PROBLEM.
| In addition to using the provided 'Save' button, users are able to
| save incomplete/inappropriate detail records by exiting to the main
| form (say by clicking cmdPrint or cmdClose). The BeforeUpdate code
| doesn't fire and the record isn't validated and the user isn't
warned.
| [...]
| Am I correct saying that the subform BeforeUpdate does not fire in
the
| circumstances described?
|
| No. The subform's BeforeUpdate event should fire, and I have never
heard of
| a case where it did not, if correctly established.
|
| Check to make sure that, in the property sheet of the form that is
displayed
| by the subform control, on the Event tab, the Before Update property is
set
| to "[Event Procedure]".
|
| --
| Dirk Goldgar, MS Access MVP
| Access tips: www.datagnostics.com/tips.html
|
| (please reply to the newsgroup)
|

Ah Hah! Use the before update event on the form that you put inside the
subform control.
This explains the strange results you were getting.
It's not the subform control where you validate data, it's the form
(subform) inside the subform control.
Sometimes both the subform and the subform control have the same name,
sometimes they don't.

By the way, it's best not to use exit events of controls to validate data -
causes problems and is best avoided.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia




".Len B" wrote in message
...
The subform control on the main form has only Enter & Exit events.
The Exit event has only a DoEvents statement which I inserted just
to have somewhere to set a breakpoint. This event does fire.

The BeforUpdate event does have[Event Procedure] in the property
sheet. In fact the event does fire if I click on the cmdSave
within the subform but does not fire when leaving the subform
by another way, hence the wording of the subject line.

I just now tested by setting an exit event on one of the subform
controls and single stepping. I clicked on the cmdClose button of
the main form from within said control. OnExit fired, form closed,
incomplete detail record was saved without validation.

Just had a thought. I'll do a decompile / compile. Who knows?

--
Len
__________________________________________________ ____
remove nothing for valid email address.
"Dirk Goldgar" wrote in message
...
| ".Len B" wrote in message
| ...
| THE PROBLEM.
| In addition to using the provided 'Save' button, users are able to
| save incomplete/inappropriate detail records by exiting to the main
| form (say by clicking cmdPrint or cmdClose). The BeforeUpdate code
| doesn't fire and the record isn't validated and the user isn't
warned.
| [...]
| Am I correct saying that the subform BeforeUpdate does not fire in
the
| circumstances described?
|
| No. The subform's BeforeUpdate event should fire, and I have never
heard of
| a case where it did not, if correctly established.
|
| Check to make sure that, in the property sheet of the form that is
displayed
| by the subform control, on the Event tab, the Before Update property is
set
| to "[Event Procedure]".
|
| --
| Dirk Goldgar, MS Access MVP
| Access tips: www.datagnostics.com/tips.html
|
| (please reply to the newsgroup)
|

I hadn't done a decompile before so I didn't know what to expect.
It appears similar to a Compact but when I open the VB code,
Clicking Debug shows Compile greyed out. Did the decompie work?
I did it twice.

In any case, I can still save an unvalidated detail record.

--
Len
__________________________________________________ ____
remove nothing for valid email address.
".Len B" wrote in message
...
| The subform control on the main form has only Enter & Exit events.
| The Exit event has only a DoEvents statement which I inserted just
| to have somewhere to set a breakpoint. This event does fire.
|
| The BeforUpdate event does have[Event Procedure] in the property
| sheet. In fact the event does fire if I click on the cmdSave
| within the subform but does not fire when leaving the subform
| by another way, hence the wording of the subject line.
|
| I just now tested by setting an exit event on one of the subform
| controls and single stepping. I clicked on the cmdClose button of
| the main form from within said control. OnExit fired, form closed,
| incomplete detail record was saved without validation.
|
| Just had a thought. I'll do a decompile / compile. Who knows?
|
| --
| Len
| __________________________________________________ ____
| remove nothing for valid email address.
| "Dirk Goldgar" wrote in message
| ...
|| ".Len B" wrote in message
|| ...
|| THE PROBLEM.
|| In addition to using the provided 'Save' button, users are able to
|| save incomplete/inappropriate detail records by exiting to the main
|| form (say by clicking cmdPrint or cmdClose). The BeforeUpdate code
|| doesn't fire and the record isn't validated and the user isn't
| warned.
|| [...]
|| Am I correct saying that the subform BeforeUpdate does not fire in
| the
|| circumstances described?
||
|| No. The subform's BeforeUpdate event should fire, and I have never
| heard of
|| a case where it did not, if correctly established.
||
|| Check to make sure that, in the property sheet of the form that is
| displayed
|| by the subform control, on the Event tab, the Before Update property
is
| set
|| to "[Event Procedure]".
||
|| --
|| Dirk Goldgar, MS Access MVP
|| Access tips: www.datagnostics.com/tips.html
||
|| (please reply to the newsgroup)
||

When Compile is greyed out, all the code is compiled.
Once you start to edit some of the code, it will become uncompiled.
We usually click Debug | Compile every time we make a change to some code.
Then we always click Save to save the code.
Or you can do it in the reverse order.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


".Len B" wrote in message
...
I hadn't done a decompile before so I didn't know what to expect.
It appears similar to a Compact but when I open the VB code,
Clicking Debug shows Compile greyed out. Did the decompie work?
I did it twice.

In any case, I can still save an unvalidated detail record.

--
Len
__________________________________________________ ____
remove nothing for valid email address.
".Len B" wrote in message
...
| The subform control on the main form has only Enter & Exit events.
| The Exit event has only a DoEvents statement which I inserted just
| to have somewhere to set a breakpoint. This event does fire.
|
| The BeforUpdate event does have[Event Procedure] in the property
| sheet. In fact the event does fire if I click on the cmdSave
| within the subform but does not fire when leaving the subform
| by another way, hence the wording of the subject line.
|
| I just now tested by setting an exit event on one of the subform
| controls and single stepping. I clicked on the cmdClose button of
| the main form from within said control. OnExit fired, form closed,
| incomplete detail record was saved without validation.
|
| Just had a thought. I'll do a decompile / compile. Who knows?
|
| --
| Len
| __________________________________________________ ____
| remove nothing for valid email address.
| "Dirk Goldgar" wrote in message
| ...
|| ".Len B" wrote in message
|| ...
|| THE PROBLEM.
|| In addition to using the provided 'Save' button, users are able to
|| save incomplete/inappropriate detail records by exiting to the main
|| form (say by clicking cmdPrint or cmdClose). The BeforeUpdate code
|| doesn't fire and the record isn't validated and the user isn't
| warned.
|| [...]
|| Am I correct saying that the subform BeforeUpdate does not fire in
| the
|| circumstances described?
||
|| No. The subform's BeforeUpdate event should fire, and I have never
| heard of
|| a case where it did not, if correctly established.
||
|| Check to make sure that, in the property sheet of the form that is
| displayed
|| by the subform control, on the Event tab, the Before Update property
is
| set
|| to "[Event Procedure]".
||
|| --
|| Dirk Goldgar, MS Access MVP
|| Access tips: www.datagnostics.com/tips.html
||
|| (please reply to the newsgroup)
||