Error Checking and Error Messages in Forms

  • Thread starter Thread starter Boris Nogoodnik
  • Start date Start date
B

Boris Nogoodnik

I am making relatively simple DB in Access 2002. Many fields in
tables do not allow duplicates, so I declared them as Indexed(No
Duplicates) in properties.

If you try to enter a duplicate value in the form, Access gives
you an error box with rather confusing suggestions that scares
the users. I would like this message to display something like
"<value> in field <X> already exists, please reenter".

In case if you use custom record navigation buttons to go to
another record, created by the wizard, it's even worse. It just
says "unable to navigate to the record you selected". It's done
by the wizard. It displays this message on any error in VB code
and there is no check for any specific condition.

So what I'd like to do is to make my own error handlers, giving
more specific information about the cause with the messages that
are clear to the users, who are not quite computer literate.

Anfotunately I couldn't find any error codes or the ways to
check them.

Any help?
 
Hi Boris,

You can do the majority of this type of error checking in the BeforeUpdate
event of the form or the BeforeUpdate event of the control if you are
validating that the value in a single control is unique. As an example of
the latter, look at the Customer form Northwind database. It uses a macro to
validate that the Customerid is unique. This could be rewritten in VBA as
follows:

Private Sub CustomerID_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[CustomerID]", "[Customers]", "[CustomerID] = """ &
Forms!Customers!CustomerID & """")) Then
MsgBox "There is already a Customer with this CustomerID."
me.Customerid.setfocus
Cancel = True
End If
End Sub

The BeforeUpdate event of the form is similar - it just allows you to do
more extensive validation which may include several fields. The principal is
the same - if the record is not valid, set Cancel=True and tell the user
what is wrong. Then it's usually helpful to the user to put the cursor at or
near the problem.
 
Sandra, thanks for your help. I will try it. However I am a
"general" programmer. Mainly C, C++ and various assemblers. I
got used to getting a specific error code from some system
interfaces and then take action on my own. Is there a way to
check for error code in access? Are there any error codes at
all? Is there a way to find out what is going wrong in VB?

Sandra Daigle said:
Hi Boris,

You can do the majority of this type of error checking in the BeforeUpdate
event of the form or the BeforeUpdate event of the control if you are
validating that the value in a single control is unique. As an example of
the latter, look at the Customer form Northwind database. It uses a macro to
validate that the Customerid is unique. This could be rewritten in VBA as
follows:

Private Sub CustomerID_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("[CustomerID]", "[Customers]", "[CustomerID] = """ &
Forms!Customers!CustomerID & """")) Then
MsgBox "There is already a Customer with this CustomerID."
me.Customerid.setfocus
Cancel = True
End If
End Sub

The BeforeUpdate event of the form is similar - it just allows you to do
more extensive validation which may include several fields. The principal is
the same - if the record is not valid, set Cancel=True and tell the user
what is wrong. Then it's usually helpful to the user to put the cursor at or
near the problem.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Boris said:
I am making relatively simple DB in Access 2002. Many fields in
tables do not allow duplicates, so I declared them as Indexed(No
Duplicates) in properties.

If you try to enter a duplicate value in the form, Access gives
you an error box with rather confusing suggestions that scares
the users. I would like this message to display something like
"<value> in field <X> already exists, please reenter".

In case if you use custom record navigation buttons to go to
another record, created by the wizard, it's even worse. It just
says "unable to navigate to the record you selected". It's done
by the wizard. It displays this message on any error in VB code
and there is no check for any specific condition.

So what I'd like to do is to make my own error handlers, giving
more specific information about the cause with the messages that
are clear to the users, who are not quite computer literate.

Anfotunately I couldn't find any error codes or the ways to
check them.

Any help?
Click to expand...
Click to expand...
 
Back
Top