What is best way to display error message on form?

[Dennis]

Hi,

I'm on Access via XP Office Pro w sp3 on XP Pro w sp3.

I’m new to Access and still learning by leaps and bounds. (Unfortunately,
sometimes those bounds cause me to inadvertently leap off a cliff.)

Anyhow, I’m now to the point where I’m having to edit the form to make sure
all of the required fields have been entered, which I do in either the Before
Update event or on the double click on the Save button event or both.

When I edit the fields, there could be multiple errors. The question is
what is the “best†or “most appropriate†way to display the errors? I
realize that this question might off a little from the purposes of this
forum, but I would greatly appreciate some help in this area.

I could display one error using MSGBOX, have the user fix the error, press
the save button again, and re-edit and then display the next error. This
seems to be rather slow and clumsy. Also, in order to fix the error, they
have to close the msgbox which means they no longer have the error message in
front of them. Not to mention, they have to repeat this process one error at
a time.

I’m thinking about opening another form (error form) and display all of the
error messages in that form. In addition, I will either change the color of
the control’s label or I’ll make the label’s asterisk visible for each
control that is error. Or I can display a number next to the label where the
number corresponds to the number of the error in the error form.

When the Save edit process starts again, it will make the error form
invisible, clear the error message, and then re-edit everything.

Is that a “normal†or more appropriate way to display the Before Update edit
error messages?

Thanks,


Dennis

 

[tina]

if your goal is to check that all *required* data is entered before a record
is saved, you can set the Required property of the fields, in the underlying
table, to Yes.

i have a working database that allows the users to enter and save records in
a queue, whether finished or not; but each record must be completed before
it can be submitted to management for approval. so in this case i couldn't
use the Required field property. instead, for each control bound to a field
that must have data entered, i set the control's Tag property to "r" (but
without the quote marks).

then i wrote a custom function to loop through the controls in the form,
highlighting required controls with no data entered, and returning a True
value to the calling code, which then opens a message box that directs the
user to enter missing data in the highlighted controls. i'll post the code
below, but this is a somewhat unusual situation requiring the special
handling; if you're simply trying to ensure data is entered before saving a
record, i'd stick with setting the fields' Required property in the table.

hth


Private Function isMissingData() As Boolean

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.Tag = "r" Then
If IsNull(ctl) Then
ctl.BackColor = yello
isMissingData = True
Else
ctl.BackColor = wite
End If
End If
Next

End Function

Private Sub cmdSubmit_Click()

Dim dt As Date

If isMissingData = True Then
MsgBox "Enter the missing information in the " _
& "highlighted fields, please.", vbExclamation, _
"RECORD SUBMISSION CANCELLED"
Else
<process the record submission>
End If

Exit Sub

 

[Allen Browne]

Dennis, my preference is to use the BeforeUpdate event procedure of the form
to check everything, and then show just the one message for the whole
record.

The public sub goes in a standard module, so you can call it from each form.
The private sub illustrates how it's called in a particular form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim bWarn As Boolean 'Flag to warn user.
Dim strMsg As String 'MsgBox message.

'Handle required fields.
If IsNull(Me.Surname) Then
Cancel = True
strMsg = strMsg & "Surname required." & vbCrLf
End If
If IsNull(Me.City) Then
Cancel = True
strmsg = strmsg & "City required." & vbCrLf
End If

If Not Cancel Then
'Warning fields
If Me.Surname <> Me.Surname.OldValue Then
bWarn = True
strMsg = strMsg & "Surname changed?" & vbCrLf
End If
'etc for other warnings.
End If

Call CancelOrWarn(Cancel, bWarn, strMsg)
End Sub

Public Sub CancelOrWarn(Cancel As Integer, bWarn As Boolean, strMsg As
String)
'Purpose: Display the message passed in if Cancel or bWarn is true.
'Arguments: Cancel = the argument to say the event is cancelled.
' bWarn = flag to warn use and ask whether to cancel.
' strMsg = the message to display to the user.
'Note: Cancel is changed to True if the user heeds the warning.
'Usage: In a BeforeUpdate event (e.g. of a form):
' Call CancelOrWarn(Cancel, False, "You can't do that!")
If Cancel Then
strMsg = strMsg & vbCrLf & "Correct the entry, or press <Esc> to
undo."
MsgBox strMsg, vbExclamation, "Invalid data."
ElseIf bWarn Then
strMsg = strMsg & vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo + vbDefaultButton2 + vbExclamation, "Are
you sure?") <> vbYes Then
Cancel = True
End If
End If
End Sub

Tina, rather than setting the Tag property for the required controls, you
could read the Required property of the field itself.

Here's an example lifted from the 'Highlight required fields' sample
database available here:
http://allenbrowne.com/highlight.html

Dim rs As DAO.Recordset 'Recordset of the form.
Dim ctl As Access.Control 'Each control on the form.
Dim strField As String 'Name of the field a control is bound to.
Set rs = frm.Recordset
For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox
'Ignore unbound, or bound to an expression.
strField = ctl.ControlSource
If (strField <> vbNullString) And Not (strField Like "=*") Then
With rs(strField)
If (.Required) Or (.ValidationRule Like "*Is Not Null*")
Then
ctl.BackColor = mlngcRequiredBackColor
Call MarkAttachedLabel(ctl)
End If
End With
End If
End Select
Next

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

if your goal is to check that all *required* data is entered before a
record
is saved, you can set the Required property of the fields, in the
underlying
table, to Yes. [snip]

I'm on Access via XP Office Pro w sp3 on XP Pro w sp3.

I'm new to Access and still learning by leaps and bounds. (Unfortunately,
sometimes those bounds cause me to inadvertently leap off a cliff.)

Anyhow, I'm now to the point where I'm having to edit the form to make
sure all of the required fields have been entered, which I do in either
the
BeforeUpdate event or on the double click on the Save button event or
both.

[snip]

 

[Dennis]

Tina, Allen,

Yes, I am trying to edit the required fields. Sorry I did not mention that.

Once again, thank you for your assitances. It is of GREAT help.

 

[tina]

certainly that would work, Allen, but i think you missed my point. i use the
Tag property in specific situations where the user must have the flexibility
to *save* incomplete records, but be prevented from "passing them down the
line" before completion. i did suggest to the op that using the Required
property makes more sense in most situations.

hth

Tina, rather than setting the Tag property for the required controls, you
could read the Required property of the field itself.

Here's an example lifted from the 'Highlight required fields' sample
database available here:
http://allenbrowne.com/highlight.html

Dim rs As DAO.Recordset 'Recordset of the form.
Dim ctl As Access.Control 'Each control on the form.
Dim strField As String 'Name of the field a control is bound to.
Set rs = frm.Recordset
For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox
'Ignore unbound, or bound to an expression.
strField = ctl.ControlSource
If (strField <> vbNullString) And Not (strField Like "=*") Then
With rs(strField)
If (.Required) Or (.ValidationRule Like "*Is Not Null*")
Then
ctl.BackColor = mlngcRequiredBackColor
Call MarkAttachedLabel(ctl)
End If
End With
End If
End Select
Next

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

if your goal is to check that all *required* data is entered before a
record
is saved, you can set the Required property of the fields, in the
underlying
table, to Yes. [snip]

I'm on Access via XP Office Pro w sp3 on XP Pro w sp3.

I'm new to Access and still learning by leaps and bounds. (Unfortunately,
sometimes those bounds cause me to inadvertently leap off a cliff.)

Anyhow, I'm now to the point where I'm having to edit the form to make
sure all of the required fields have been entered, which I do in either
the
BeforeUpdate event or on the double click on the Save button event or
both.

[snip]

 

[Allen Browne]

Yes, you did say that, Tina. Your suggestion makes good sense.