Error info not available

[Lars Brownies]

I use the code beneath to trap for an error. I tested it with a required
field error, but the Err.number nor the Err.description is availabe when the
msgbox is fired. What am I doing wrong?

Private Sub Form_Error(DataErr As Integer, Response As Integer)
MsgBox "An unexpected error has occurred in the form. " & _
"The error description is " & Err.Number & " " & Err.Description
Response = acDataErrContinue
End Sub

Thanks,
Lars

 

[Richard]

Hi Lars,

Wouldn't you need the "Response = acDataErrDisplay" to display the error?

The Response parameter is used by MS Access to determine weather or not to
display the default error message. There are three values the Response
parameter can have.

Response = acDataErrDisplay

Response = acDataErrContinue

Response = acDataErrAdded


Just a guess
Richard

 

[Lars Brownies]

Wouldn't you need the "Response = acDataErrDisplay" to display the

error?

I want to display a custom message. acDataErrDisplay shows the standard
message.

Lars

 

[Richard]

Hi Lars,

Most the of the converstions about your type of problem, are about Combo Box
NotInList Event. But I was able to find this, displaying a custom message.
Take a look at this page about half way down.

http://www.fontstuff.com/access/acctut20.htm

Richard

 

[Lars Brownies]

Thanks for the link, Richard.

With a combobox you have the notInList event firing when you are in that
combobox. But a 'Required field' error gets triggered when you move to
another record, and at that time the focus can be on any other field. It
seems to me you can only trap that error at form level, but then you miss
the field name unless you show the standard Access message.

Lars

 

[Tom Wickerath]

Hi Lars,

Try referencing the DataErr. Displaying the correct error message may be a
bit more tricky. I was able to display the correct error text for error 3022
(Unique index violation):

Private Sub Form_Error(DataErr As Integer, Response As Integer)

MsgBox "An unexpected error has occurred in the form. " & _
"The error description is " & DataErr _
& " " & AccessError(DataErr)

Response = acDataErrContinue

End Sub


You could also set up a Select Case ....End Select block to test for various
error numbers, and display more user-friendly error text. For example:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 3022
MsgBox "This item has already been added." & vbCrLf & _
"You cannot create duplicates.", _
vbOKOnly + vbInformation, "Item Already Added..."
Me.Undo
Response = acDataErrContinue
Case Else
Response = acDataErrDisplay
End Select

End Sub


However, I'm not sure that you will have success passing the name of the
field. Perhaps you would be better off to do more of the error checking in
the Form's Before_Update procedure. You can check and report on individual
fields, and return focus to the field the user needs to pay attention to.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[David W. Fenton]

With a combobox you have the notInList event firing when you are
in that combobox. But a 'Required field' error gets triggered when
you move to another record, and at that time the focus can be on
any other field. It seems to me you can only trap that error at
form level, but then you miss the field name unless you show the
standard Access message.

I entirely avoid the problem by not allowing a record to be inserted
until the required fields are filled out. I do this with an unbound
form that does nothing more than collect the required field data,
then insert the record via DAO, pick up the PK with SELECT
@@IDENTITY and then open the data editing form filtered to that one
record. That way I don't have to muck around with the interaction of
form and control events. It also means that you don't lead a user
down a path that can't be completed (i.e., they put in a bunch of
data, but because they lack all the required fields, they can never
save the record).

 

[Lars Brownies]

David,
Do I understand correctly that you have an unbound form on which only the
required fields are present?

That way I don't have to muck around with the interaction of
form and control events.

But you do have to check whether the unbound fields have been filled, right?
You do that in code?

It also means that you don't lead a user
down a path that can't be completed

In what way do you make clear to the user that the fields in the form are
all required?

(i.e., they put in a bunch of
data, but because they lack all the required fields, they can never
save the record).

I can also imagine that in your main form you give the required fields a
specific color, so that users know those are required fields which would
prevent them from entering any value before they know all values for the
required fields.

Lars

 

[David W. Fenton]

Do I understand correctly that you have an unbound form on which
only the required fields are present?

Correct. No record can be created until all the controls are
appropriately populated.

But you do have to check whether the unbound fields have been
filled, right? You do that in code?

Yes. I generally write a function that checks the field values that
returns a Boolean, and then call that in a function that sets the
CREATE NEW RECORD button's .Enabled property to the value returned
by the first function. Then I place the second function as the
AfterUpdate of all the controls. Like this:

Private Function CheckFields() As Boolean
CheckFields = Not IsNull(Me!txtField1) _
And Not IsNull(Me!txtField2)
End Function

Private Function SetSaveButton() As Boolean
Me!cmbSave.Enabled = CheckFields()
SetSaveButton = Me!cmbSave.Enabled
End Function

I then place "=SetSaveButtons()" in the AfterUpdate property of all
the controls.

The CheckFields() function can be as complex as necessary, e.g.,
checking that different fields have logical values (e.g., Birthdate
is not greater than GraduationDate). Some of that checking should be
in the BeforeUpdate events of the individual controls, but some of
it could be in the CheckFields() function, as well.

In what way do you make clear to the user that the fields in the
form are all required?

They can't save it until it's properly filled out.

I can also imagine that in your main form you give the required
fields a specific color, so that users know those are required
fields which would prevent them from entering any value before
they know all values for the required fields.

Generally I don't. Indeed, I don't use a lot of required fields,
actually. I've found that what I think is required data from a
schema point of view turns out in the real world to simply not be
available. Rather than populate a bunch of fields with default
values, I simply compromise and limit the number of required fields
to the bare minimum. Indeed, I'll often have a minimum set of fields
required to create a record, and then allow the deletion of data in
those particular fields after the record is created.

It's probably sloppy of me to do so, but I'm trying to gently nudge
the users into creating good data on the front end, but then
allowing them freedom to do with it what they need to do after the
fact.

 

[Lars Brownies]

Thanks.
I agree to limit the number of required fields to the bare minimum. Main
reason is if users are forced to enter data they don't know, they
'temporarily' enter a phony value which they forget to change afterwards.

Lars

 

[David W. Fenton]

I agree to limit the number of required fields to the bare
minimum. Main reason is if users are forced to enter data they
don't know, they 'temporarily' enter a phony value which they
forget to change afterwards.

Exactly! Building an app in such a way that you guarantee the entry
of bad data seems inadvisable to me!