Reuse error handling routines with Form.Dirty = False

[Carl Colijn]

Hi group,

In my unbound form I have a tabbed control with bound subforms in it. When
the user adds a new or edits an existing record in one of the subforms and
then puts the focus on something else (next record, button in main form,
etc.) the record first saves and then the command the user invoked will
start.

However, when the user in stead selects some menu item, the modified record
will not save. This is of course desired behaviour (how else to implement
i.e. "Undo" in the menu), but this can lead to undesired situations in my
app.

In my custom menu I have put menu commands to open other forms via a VBA
function (called say "ShowForm"). One of these forms allows the user to
select another database and link that as a new backend to the frontend
(swapping the used backend database out). But when the user has a modified
record still hanging around, the following sequence of events occurs:
- User modifies record
- User selects menu command "Switch database"
- "Switch database" form opens, user selects database
- New database is attached to the frontend, menu command exits
- The BeforeUpdate / AfterUpdate event of the modified record fires

In my case the AfterUpdate event creates some accompanying records to the
input record from the "Input" table in the "Output" table. I have linked
these tables via a relationship enforcing relational integrity (sp? I use a
Dutch Access version...) linking the Output via a primary key to the Input,
so most of the time the AfterUpdate event bombs out in the above situation
because a record is missing from the "Input" table.

How can I fix this situation?

I thought of first calling another function in the "ShowForm" function,
which ensures any modified records are first saved. I can identify any
dirty record in one of the subforms perfectly. When I then force the record
to save via "oActiveForm.Dirty = False", the record will save OK when there
are no errors. When there are errors however, I get a trappable error on
the Dirty=False line. But I have my form's error handling code placed in
the form itself in the Field_BeforeUpdate events, the Form_Error event and
the Form_BeforeUpdate event; I can of course split out this functionality in
a public function and call this functionality instead, but can I use another
approach so I can still rely on my existing form events?

Another issue that arises with the above approach is the edits in the active
control on the subform get lost in the process. The control's "Text"
property holds the edited text, but the control's "Value" property does not
yet get updated. I can force the update by first setting the focus to
another (unbound) control on the subform, but that might give rise to yet
other (trappable) validation errors on the active control.

All in all, it's beginning to get quite a mess to get this right in an
efficient and aesthetic way. Is there some other way I can get this to work
without sacrificing my existing code to duplication and/or major reworking?
I though I'd better stop now and ask some expert advise before I make an
even bigger mess of my code...

Thanks in advance,
Carl Colijn

The design of the GUI:
* MainForm contains a tab control named oTab
* The pages in the oTab tab control each contain only one
subform named oFormX, with X being (the tab's index + 1)
* The input forms that get shown in the subforms are shown
as a continuous form, and have the 'invisible' non-tabstop
control "oParkingSpot"
* Each input form has a function called CorrectAndValidate,
which tests each field for validity and tests it against some
constraints; it returns whether the tests have passed
* The custom menu holds a command called "Switch database"
that directly calls my ShowSwitchDBForm function

The code I have so far:

* UIGlobals; a module with all sorts of reusable UI routines *

' Shows the "Switch database" form
Public Function ShowSwitchDBForm() As Boolean
' Ensure any input edits are saved first
If EnsureInputSaved() Then
' Done -> show the form
Call DoCmd.OpenForm("SwitchDB", acNormal, , , , acDialog)
End If
ShowSwitchDBForm = True
End Function

' Tries to first save any edited records in any input form
Public Function EnsureInputSaved() As Boolean
' Get the active input form
Dim oInputForm As Form
Set oInputForm = Form_FormMain.GetActiveForm()

' Look if there are any unsaved changes
Dim bSaved As Boolean
bSaved = True
If oInputForm.Dirty Then
' Yes -> ensure the latest field edits get used
bSaved = False
Call oInputForm.Controls("oParkingSpot").SetFocus
' This doesn't work with validation errors...

' Validate the record
If oInputForm.CorrectAndValidate() Then
' Done -> save it
On Error Resume Next
oInputForm.Dirty = False
bSaved = Err.Number = 0
' I'd rather wish not to have to call all the error
' routines in the input forms from here...
End If
End If

' And return if any changes got saved
EnsureInputSaved = bSaved
End Function


* MainForm; the main form containing the tab control *

' Gets the active input form
Public Function GetActiveForm() As Form
Dim nActiveTabNr As Long
nActiveTabNr = oTabs.Value
Dim oActiveTab As Page
Set oActiveTab = oTabs.Pages(nActiveTabNr)
Set GetActiveForm = oActiveTab.Controls("oForm" & (nActiveTabNr + 1)).Form
End Function

 

[Rick Brandt]

Hi group,

In my unbound form I have a tabbed control with bound subforms in it. When
the user adds a new or edits an existing record in one of the subforms and
then puts the focus on something else (next record, button in main form, etc.)
the record first saves and then the command the user invoked will start.

However, when the user in stead selects some menu item, the modified record
will not save. This is of course desired behaviour (how else to implement
i.e. "Undo" in the menu), but this can lead to undesired situations in my app.

In my custom menu I have put menu commands to open other forms via a VBA
function (called say "ShowForm"). One of these forms allows the user to
select another database and link that as a new backend to the frontend
(swapping the used backend database out). But when the user has a modified
record still hanging around, the following sequence of events occurs:
- User modifies record
- User selects menu command "Switch database"
- "Switch database" form opens, user selects database
- New database is attached to the frontend, menu command exits
- The BeforeUpdate / AfterUpdate event of the modified record fires

In my case the AfterUpdate event creates some accompanying records to the
input record from the "Input" table in the "Output" table. I have linked
these tables via a relationship enforcing relational integrity (sp? I use a
Dutch Access version...) linking the Output via a primary key to the Input, so
most of the time the AfterUpdate event bombs out in the above situation
because a record is missing from the "Input" table.

How can I fix this situation?[snip]

This is a case where providing an explicit [Save] button on your forms is
useful. You could disable the items on your custom menu in the Dirty event of
your forms and then re-enable them in the AfterUpdate event. That way the user
will be forced to save all records before they can use those menu items.

 

[Carl Colijn]

Hi group,

In my unbound form I have a tabbed control with bound subforms in
it. When the user adds a new or edits an existing record in one of
the subforms and then puts the focus on something else (next record,
button in main form, etc.) the record first saves and then the
command the user invoked will start. However, when the user in stead
selects some menu item, the modified
record will not save. This is of course desired behaviour (how else
to implement i.e. "Undo" in the menu), but this can lead to
undesired situations in my app. In my custom menu I have put menu
commands to open other forms via a
VBA function (called say "ShowForm"). One of these forms allows the
user to select another database and link that as a new backend to
the frontend (swapping the used backend database out). But when the
user has a modified record still hanging around, the following
sequence of events occurs: - User modifies record
- User selects menu command "Switch database"
- "Switch database" form opens, user selects database
- New database is attached to the frontend, menu command exits
- The BeforeUpdate / AfterUpdate event of the modified record fires

In my case the AfterUpdate event creates some accompanying records
to the input record from the "Input" table in the "Output" table. I
have linked these tables via a relationship enforcing relational
integrity (sp? I use a Dutch Access version...) linking the Output
via a primary key to the Input, so most of the time the AfterUpdate
event bombs out in the above situation because a record is missing
from the "Input" table. How can I fix this situation?[snip]

This is a case where providing an explicit [Save] button on your
forms is useful. You could disable the items on your custom menu in
the Dirty event of your forms and then re-enable them in the
AfterUpdate event. That way the user will be forced to save all
records before they can use those menu items.

Hi Rick,

Thanks for the suggestion! However, I do not think an explicit [Save]
button will be accepted by my clients; they are already used to just
entering data and not having to 'save' anything; I'd like to keep the save
implicit.

Since I can perfectly detect which record is still dirty (if any) when my
custom menu's code runs, I wanted to just save that record before commencing
with the rest of the menu's code (with e.g. Form.Dirty = False). However,
it seems like that requires me to duplicate all my existing error handling
and field validation calls (code I have in the form's Error and BeforeUpdate
events and the field's BeforeUpdate events). But I hoped I wouldn't have to
go that route (I hate code duplication).

Kind regards,
Carl Colijn

 

[Carl Colijn]

Hi group,

In my unbound form I have a tabbed control with bound subforms in it.
When the user adds a new or edits an existing record in one of the
subforms and then puts the focus on something else (next record,
button in main form, etc.) the record first saves and then the
command the user invoked will start.

However, when the user in stead selects some menu item, the modified
record will not save. This is of course desired behaviour (how else
to implement i.e. "Undo" in the menu), but this can lead to undesired
situations in my app.

In my custom menu I have put menu commands to open other forms via a
VBA function (called say "ShowForm"). One of these forms allows the
user to select another database and link that as a new backend to the
frontend (swapping the used backend database out). But when the user
has a modified record still hanging around, the following sequence of
events occurs: - User modifies record
- User selects menu command "Switch database"
- "Switch database" form opens, user selects database
- New database is attached to the frontend, menu command exits
- The BeforeUpdate / AfterUpdate event of the modified record fires

In my case the AfterUpdate event creates some accompanying records to
the input record from the "Input" table in the "Output" table. I
have linked these tables via a relationship enforcing relational
integrity (sp? I use a Dutch Access version...) linking the Output
via a primary key to the Input, so most of the time the AfterUpdate
event bombs out in the above situation because a record is missing
from the "Input" table.
How can I fix this situation?

I thought of first calling another function in the "ShowForm"
function, which ensures any modified records are first saved. I can
identify any dirty record in one of the subforms perfectly. When I
then force the record to save via "oActiveForm.Dirty = False", the
record will save OK when there are no errors. When there are errors
however, I get a trappable error on the Dirty=False line. But I have
my form's error handling code placed in the form itself in the
Field_BeforeUpdate events, the Form_Error event and the
Form_BeforeUpdate event; I can of course split out this functionality
in a public function and call this functionality instead, but can I
use another approach so I can still rely on my existing form events?
Another issue that arises with the above approach is the edits in the
active control on the subform get lost in the process. The control's
"Text" property holds the edited text, but the control's "Value"
property does not yet get updated. I can force the update by first
setting the focus to another (unbound) control on the subform, but
that might give rise to yet other (trappable) validation errors on
the active control.
All in all, it's beginning to get quite a mess to get this right in an
efficient and aesthetic way. Is there some other way I can get this
to work without sacrificing my existing code to duplication and/or
major reworking? I though I'd better stop now and ask some expert
advise before I make an even bigger mess of my code...

Thanks in advance,
Carl Colijn

Fixed. Unlike the simplified example code mentioned, the call to the
"Form.Dirty = False" did not occur before the other (popup) form was opened,
but during (in it's Load event). This must have affected it's
functionality, since now that I followed my own simplified example, it all
works... That shows again to post exactly the code you use in stead of
making a simplified study case out of it without first testing it
properly...

Anyway, the code I have now is quite simple. I defined a function called
SaveAnyOpenRecordChanges in a global module, and I defined a public function
called GetActiveSubform in my main form's code module which returns the
subform that is open (you will have to make this function up yourself since
it depends heavily on your situation). I call the SaveAnyOpenRecordChanges
function in the VBA code that is triggered by certain menu items in my
custom menu. If it returns True, it is safe to commence with operations
like switching the backend database.

' Saves any open record changes on the input forms
Public Function SaveAnyOpenRecordChanges() As Boolean
' Get the active input form
Dim oInputSubform As SubForm
Set oInputSubform = Form_Main.GetActiveSubform
Dim oInputForm As Form
Set oInputForm = oInputSubform.Form

' Look if there are any changes on the form
Dim bAllSaved As Boolean
bAllSaved = True
If oInputForm.Dirty Then
' Yes -> save them first
On Error Resume Next
oInputForm.Dirty = False
bAllSaved = Err.Number = 0
On Error GoTo 0
End If

' And return whether any open changes got saved
SaveAnyOpenRecordChanges = bAllSaved
End Function

Hope this is of any use to anyone,

--
Kind regards,
Carl Colijn

TwoLogs - IT Services and Product Development
A natural choice!
http://www.twologs.com
TimeTraces: the powerfull and versatile time registration system!
http://www.twologs.com/en/products/timetraces.asp