C
Carl Colijn
Hi group,
Just to share after getting some nice advice from this group.
My situation:
* I use Access 2003 (Dutch edition if that's of any use)
* I have a main form with a tab control on it which in turn holds subforms
* The tab control is named "oTab"
* The subforms are called "oFormX" where X is the number of the tab they're
on (1 based)
* The main form is unbound, the subforms's forms are bound and set to
display like a continuous form
* The subform's forms have BeforeUpdate event, Error event etc. validation
and error handling
When the user enters some invalid data in a record in the subforms and try
to save it, the saving is nicely cancelled by the BeforeUpdate and they stay
in the record. You can even put the focus on the control that is the
offender in this case.
Now when the user tries to close the main form by clicking the main form's
"X" or pressing Ctrl-F4, or for that matter even tries to close Access in
whatever way, the BeforeUpdate will again trigger nicely and give the user a
warning that his data is invalid. However, in this case Access steps in
afterwards with it's own error message: "There may possibly be an error
in... blah ... cannot close, data will get lost... blah... close anyway?
YES/NO" (I use the Dutch version, so the text may not be that similar to the
English error). Now how to continue? I do not want the users to see this
cryptic error message (they might get scared)...
For all of you who are in the same situation (and I did find some requests
on how to solve this in Google when I was still searching for an answer):
this is how I solved it.
Step one is to add an error event to your main form that intercepts error
2169. You basically have two options here to treat the error: "Display"
(what you do not want) and "Continue". Continue here means however to let
Access just close the form and discard the offending changes; not a nice
solution, but the best we have. Like:
Select Case eDataErr
Case 2169
eResponse = acDataErrContinue
End Select
Thus step two is to note that the form may not close when you intercept the
aboce error 2169. Just add a private flag to your form's module like:
Private m_bMayClose As Boolean
and set it to True by default in your form's Load event, and set it to False
when you intercept error 2169. Then, in your form's Unload event, cancel
the unload when m_bMayClose is not set and also reset it to true for future
(allowed) closes. What we have now is that Access will not show any errors
on it's own and the form stays open, but the changes to the offending record
are however lost (unlike when you would have pressed "No" on the standard
Access error message).
Step three is thus to remember what values were placed in the controls on
the offending record. This first requires us to recognise the selected
subform. In my case this can be achieved by using:
Set oSubform = oTabs.Pages(oTabs.Value).Controls("oForm" & (oTabs.Value
+ 1))
When you have the subform's form (oSubform.Form), you can iterate over all
it's controls (For Each In oSubform.Form.Controls) and note the control's
name and value (String and Variant) in a private array/collection in your
form. For good bookkepping also note the subform itself in another private
variable.
Having the values in itself is not enough: we need to restore them as well.
I found out the best place to do this is in the Unload event itself, where
we cancel the unload of the main form. Step four will be to iterate over
the stored control values and, using the also stored subform reference,
restore each control's value. You have to use an On Error Resume Next here,
since not all controls may be restored (like calculated controls). Now this
almost works; the form doesn't close, no Access errors and the values are
restored. But the focus is not on the offending record anymore, so when the
user clicks it again, the BeforeUpdate will again fire and the user
effectively receives the same error twice. Better than the Acces error, but
still not an optimal solution (and I like optimal solutions
).
The final step five then is to also restore the focus to the offending
record. I found out you have to do that right before you restore the
record's old (offending) values, otherwise you trigger the above mentioned
duplicate BeforeUpdate check. Since we have the subform stored in a private
form variable, we can call it's SetFocus. This will remedy the extra
BeforeUpdate, since the offending record now has the proper focus. But the
user will not see it has the focus; there is no visible cursor on the record
(apart from the pencil shape in the record selector). I therefore also put
the focus on a (known) control in the record (the first one in my case).
Now we're done! The user only sees your custom BeforeUpdate messages, sees
them only once and is brought back to the offending record. And no form is
closed in the meanwhile. They can still undo their changes in the usual
way, or ammend the error and continue. For completes sake, I have added all
code involved at the end. Since I might use other conventions than you, it
might be necessary to modify it slightly (like how to find your subform).
Also, the BeforeUpdate events in my case put the focus on the offending
control's value, but this focus is not maintained by my solution. This can
be fixed, but I myself am pleased enough with the current solution so for
now I leave it at this.
If anyone finds a flaw with this code, please let me know! The last thing I
want is to switch a cryptic but functional Access error message for a
serious bug.
I hope this helps anyone in building an application that behaves a bit nicer
to the end user!
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
******* Subform's code module *******
Just have a normal BeforeUpdate event handler that displays a message and
cancels the update in case of an error
******* Main form's code module *******
Option Compare Database
Option Explicit
' Information on a control on the form
Private Type SControlInfo
sName As String
uValue As Variant
End Type
' The previous control values
Private m_oPrevSubform As SubForm
Private m_auPrevControlValues() As SControlInfo
Private m_nPrevControlValuesCount As Long
' Whether the form may close
Private m_bFormMayClose As Boolean
' Gets the active subform
' WILL NEED SOME MODIFICATION
Public Function GetActiveSubform() As SubForm
Dim nActiveTabNr As Long
nActiveTabNr = oTabs.Value
Dim oActiveTab As Page
Set oActiveTab = oTabs.Pages(nActiveTabNr)
Set GetActiveSubform = oActiveTab.Controls("oForm" & (nActiveTabNr + 1))
End Function
Private Sub Form_Error(eDataErr As Integer, eResponse As Integer)
' Determine what's wrong
Select Case eDataErr
Case 2169
' Can't close because data is invalid -> user is already notified,
' so do not show the standard Access warning
eResponse = acDataErrContinue
' But the form may not be closed
m_bFormMayClose = False
' And remember the control's values because Access will
' throw them away
Set m_oPrevSubform = GetActiveSubform
Dim aoControls As Controls
Set aoControls = m_oPrevSubform.Form.Controls
ReDim m_auPrevControlValues(aoControls.Count)
m_nPrevControlValuesCount = 0
Dim oNextControl As Control
For Each oNextControl In aoControls
' Remeber this control's value
' (On Error Resume to automatically skip e.g. labels with no 'Value'
property)
On Error Resume Next
m_auPrevControlValues(m_nPrevControlValuesCount).sName =
oNextControl.Name
m_auPrevControlValues(m_nPrevControlValuesCount).uValue =
oNextControl.Value
If Err.Number = 0 Then
' Done -> another value added
m_nPrevControlValuesCount = m_nPrevControlValuesCount + 1
End If
On Error GoTo 0
Next
End Select
End Sub
Private Sub Form_Load()
' The form may close
m_bFormMayClose = True
End Sub
Private Sub Form_Unload(bCancel As Integer)
' Look if the form may close
If Not m_bFormMayClose Then
' No -> put the focus on the correct record
Call m_oPrevSubform.SetFocus
m_oPrevSubform.Form.Bookmark = m_oPrevSubform.Form.Recordset.Bookmark
' WILL NEED MODIFICATION; 'oPnr' is the first control
' on each subform's record in my case
Call m_oPrevSubform.Form.Controls("oPnr").SetFocus
' Restore the previous control values
On Error Resume Next
Dim aoControls As Controls
Set aoControls = m_oPrevSubform.Form.Controls
Dim nControlNr As Long
For nControlNr = 0 To m_nPrevControlValuesCount - 1
aoControls(m_auPrevControlValues(nControlNr).sName).Value =
m_auPrevControlValues(nControlNr).uValue
Next
On Error GoTo 0
' Throw away all old values from the cache
Set m_oPrevSubform = Nothing
Erase m_auPrevControlValues
m_nPrevControlValuesCount = 0
' We cannot close this time
bCancel = True
' But we may close again the next time
m_bFormMayClose = True
End If
End Sub
Just to share after getting some nice advice from this group.
My situation:
* I use Access 2003 (Dutch edition if that's of any use)
* I have a main form with a tab control on it which in turn holds subforms
* The tab control is named "oTab"
* The subforms are called "oFormX" where X is the number of the tab they're
on (1 based)
* The main form is unbound, the subforms's forms are bound and set to
display like a continuous form
* The subform's forms have BeforeUpdate event, Error event etc. validation
and error handling
When the user enters some invalid data in a record in the subforms and try
to save it, the saving is nicely cancelled by the BeforeUpdate and they stay
in the record. You can even put the focus on the control that is the
offender in this case.
Now when the user tries to close the main form by clicking the main form's
"X" or pressing Ctrl-F4, or for that matter even tries to close Access in
whatever way, the BeforeUpdate will again trigger nicely and give the user a
warning that his data is invalid. However, in this case Access steps in
afterwards with it's own error message: "There may possibly be an error
in... blah ... cannot close, data will get lost... blah... close anyway?
YES/NO" (I use the Dutch version, so the text may not be that similar to the
English error). Now how to continue? I do not want the users to see this
cryptic error message (they might get scared)...
For all of you who are in the same situation (and I did find some requests
on how to solve this in Google when I was still searching for an answer):
this is how I solved it.
Step one is to add an error event to your main form that intercepts error
2169. You basically have two options here to treat the error: "Display"
(what you do not want) and "Continue". Continue here means however to let
Access just close the form and discard the offending changes; not a nice
solution, but the best we have. Like:
Select Case eDataErr
Case 2169
eResponse = acDataErrContinue
End Select
Thus step two is to note that the form may not close when you intercept the
aboce error 2169. Just add a private flag to your form's module like:
Private m_bMayClose As Boolean
and set it to True by default in your form's Load event, and set it to False
when you intercept error 2169. Then, in your form's Unload event, cancel
the unload when m_bMayClose is not set and also reset it to true for future
(allowed) closes. What we have now is that Access will not show any errors
on it's own and the form stays open, but the changes to the offending record
are however lost (unlike when you would have pressed "No" on the standard
Access error message).
Step three is thus to remember what values were placed in the controls on
the offending record. This first requires us to recognise the selected
subform. In my case this can be achieved by using:
Set oSubform = oTabs.Pages(oTabs.Value).Controls("oForm" & (oTabs.Value
+ 1))
When you have the subform's form (oSubform.Form), you can iterate over all
it's controls (For Each In oSubform.Form.Controls) and note the control's
name and value (String and Variant) in a private array/collection in your
form. For good bookkepping also note the subform itself in another private
variable.
Having the values in itself is not enough: we need to restore them as well.
I found out the best place to do this is in the Unload event itself, where
we cancel the unload of the main form. Step four will be to iterate over
the stored control values and, using the also stored subform reference,
restore each control's value. You have to use an On Error Resume Next here,
since not all controls may be restored (like calculated controls). Now this
almost works; the form doesn't close, no Access errors and the values are
restored. But the focus is not on the offending record anymore, so when the
user clicks it again, the BeforeUpdate will again fire and the user
effectively receives the same error twice. Better than the Acces error, but
still not an optimal solution (and I like optimal solutions
).The final step five then is to also restore the focus to the offending
record. I found out you have to do that right before you restore the
record's old (offending) values, otherwise you trigger the above mentioned
duplicate BeforeUpdate check. Since we have the subform stored in a private
form variable, we can call it's SetFocus. This will remedy the extra
BeforeUpdate, since the offending record now has the proper focus. But the
user will not see it has the focus; there is no visible cursor on the record
(apart from the pencil shape in the record selector). I therefore also put
the focus on a (known) control in the record (the first one in my case).
Now we're done! The user only sees your custom BeforeUpdate messages, sees
them only once and is brought back to the offending record. And no form is
closed in the meanwhile. They can still undo their changes in the usual
way, or ammend the error and continue. For completes sake, I have added all
code involved at the end. Since I might use other conventions than you, it
might be necessary to modify it slightly (like how to find your subform).
Also, the BeforeUpdate events in my case put the focus on the offending
control's value, but this focus is not maintained by my solution. This can
be fixed, but I myself am pleased enough with the current solution so for
now I leave it at this.
If anyone finds a flaw with this code, please let me know! The last thing I
want is to switch a cryptic but functional Access error message for a
serious bug.
I hope this helps anyone in building an application that behaves a bit nicer
to the end user!
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
******* Subform's code module *******
Just have a normal BeforeUpdate event handler that displays a message and
cancels the update in case of an error
******* Main form's code module *******
Option Compare Database
Option Explicit
' Information on a control on the form
Private Type SControlInfo
sName As String
uValue As Variant
End Type
' The previous control values
Private m_oPrevSubform As SubForm
Private m_auPrevControlValues() As SControlInfo
Private m_nPrevControlValuesCount As Long
' Whether the form may close
Private m_bFormMayClose As Boolean
' Gets the active subform
' WILL NEED SOME MODIFICATION
Public Function GetActiveSubform() As SubForm
Dim nActiveTabNr As Long
nActiveTabNr = oTabs.Value
Dim oActiveTab As Page
Set oActiveTab = oTabs.Pages(nActiveTabNr)
Set GetActiveSubform = oActiveTab.Controls("oForm" & (nActiveTabNr + 1))
End Function
Private Sub Form_Error(eDataErr As Integer, eResponse As Integer)
' Determine what's wrong
Select Case eDataErr
Case 2169
' Can't close because data is invalid -> user is already notified,
' so do not show the standard Access warning
eResponse = acDataErrContinue
' But the form may not be closed
m_bFormMayClose = False
' And remember the control's values because Access will
' throw them away
Set m_oPrevSubform = GetActiveSubform
Dim aoControls As Controls
Set aoControls = m_oPrevSubform.Form.Controls
ReDim m_auPrevControlValues(aoControls.Count)
m_nPrevControlValuesCount = 0
Dim oNextControl As Control
For Each oNextControl In aoControls
' Remeber this control's value
' (On Error Resume to automatically skip e.g. labels with no 'Value'
property)
On Error Resume Next
m_auPrevControlValues(m_nPrevControlValuesCount).sName =
oNextControl.Name
m_auPrevControlValues(m_nPrevControlValuesCount).uValue =
oNextControl.Value
If Err.Number = 0 Then
' Done -> another value added
m_nPrevControlValuesCount = m_nPrevControlValuesCount + 1
End If
On Error GoTo 0
Next
End Select
End Sub
Private Sub Form_Load()
' The form may close
m_bFormMayClose = True
End Sub
Private Sub Form_Unload(bCancel As Integer)
' Look if the form may close
If Not m_bFormMayClose Then
' No -> put the focus on the correct record
Call m_oPrevSubform.SetFocus
m_oPrevSubform.Form.Bookmark = m_oPrevSubform.Form.Recordset.Bookmark
' WILL NEED MODIFICATION; 'oPnr' is the first control
' on each subform's record in my case
Call m_oPrevSubform.Form.Controls("oPnr").SetFocus
' Restore the previous control values
On Error Resume Next
Dim aoControls As Controls
Set aoControls = m_oPrevSubform.Form.Controls
Dim nControlNr As Long
For nControlNr = 0 To m_nPrevControlValuesCount - 1
aoControls(m_auPrevControlValues(nControlNr).sName).Value =
m_auPrevControlValues(nControlNr).uValue
Next
On Error GoTo 0
' Throw away all old values from the cache
Set m_oPrevSubform = Nothing
Erase m_auPrevControlValues
m_nPrevControlValuesCount = 0
' We cannot close this time
bCancel = True
' But we may close again the next time
m_bFormMayClose = True
End If
End Sub