Trapping Error Messages

  • Thread starter Thread starter Ed Carron
  • Start date Start date
E

Ed Carron

My form allows the user to select data from drop-down lists to enter into a
table. The table has a 3-field primary key. When the user presses the
button to save the selected data, the button executes an Insert query. If
the data is a duplicate of a row already existing, Access returns the Access
error message. I want to trap that message and display my own message. How
can I do that?

Thanks in advance.

Ed
 
Hi,


You can trap the error in the onError form event (assuming you append
the data through the form) having the error reported as per the supplied
argument, or through the standard error handling if you use

CurrentDb.Execute StrSql, dbFailOnError

statement.


Hoping it may help,
Vanderghast, Access MVP
 
Thanks,

Wrote following code code:

Dim StrSql As String
curdbs = CurrentDb
StrSql = "insert_admin_role" '
insert_admin_role is an Insert query stored in the database
CurrentDb.Execute StrSql, dbFailOnError



I get the following error message: "Too few parameters. Expected 3."

Do you happen to know why I get that?

Thanks again.

Ed
 
Hi,


Probably you use something like Forms!FormName!ControlName? When using
CurrentDb, you have to specify the values for those arguments, something
like:

Dim qdf As QueryDef
Dim param As DAO.Parameter
Dim db As Database : Set db=CurrentDb
Set qdf=db.QueryDefs("insert_admin_role")

For each param in qdf.Parameters ' specify each parameter
param.Value = eval(param.Name) ' or something else
Next param

' on error ....
qdf.Execute dbFailOnError



I assume you use Jet (not MS SQL Server) and DAO.

If not, maybe there are typos (wrongly spelled field name) in the sql
statement: try to run the query in the query designer... three prompt should
appear, each revealing the "parameter" (wrongly spelled field) name.


Hoping it may help,
Vanderghast, Access MVP
 
Hi,

I do have something like Forms!FormName!ControlName in my stored query. I
need the values of three controls from the active form.. No typos, the
query works fine when I test it.

How do I specify the values for those arguments?? The values are what the
user highlighted in the drop-down list.

BTW, I am new to VBA, using Access 2000. Can you recommend a book that I
can purchase so I can really learn VBA & ADO, and not just hack my way
through my project while leaning on you for help?

Thanks again.

Ed
 
Hi,


The code I supplied can be a start for your experimentations.

I assume you already know if-then-else, procedures and functions. If such is
the case, then "Microsoft Access 200x Developer's Handbook" can be a good
reference book.. but it is a little bit too technical if you first need a
good refresh (or introduction) on Visual Basic... the best suggestion I can
make is to go to a bookstore and open VBA-Access books, read about a topic
you already know, and read on a topic you are interested in, and see which
book "speak to you" most.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top