Prev: Setting a Combo box value from a previous form
Next: Listbox w/many columns, need update all columns to table
From: .Len B on 9 Feb 2010 04:15
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.


From: Jeanette Cunningham on 9 Feb 2010 04:39
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" <gonehome(a)internode0.on0.net> wrote in message
news:uBnExhWqKHA.5940(a)TK2MSFTNGP02.phx.gbl...
> 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.
>
>


From: .Len B on 9 Feb 2010 06:01
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" <nnn(a)discussions.microsoft.com> wrote in message
news:eN0WQvWqKHA.3408(a)TK2MSFTNGP06.phx.gbl...
| 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" <gonehome(a)internode0.on0.net> wrote in message
| news:uBnExhWqKHA.5940(a)TK2MSFTNGP02.phx.gbl...
| > 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.
| >
| >
|
|



From: BruceM via AccessMonster.com on 9 Feb 2010 07:30
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

From: .Len B on 9 Feb 2010 09:03
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(a)uwe> wrote in message
news:a35fdd797c8a2(a)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
|



 |  Next  |  Last
Pages: 1 2 3 4
Prev: Setting a Combo box value from a previous form
Next: Listbox w/many columns, need update all columns to table