Catching error on insert statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a piece of code that queries an existing table to retrieve the last
number used, adds one to that number to generate the next number and inserts
it as the key value for a new record. I store this number and use it
throughtout the rest of the transactions on the form. Obviously the flaw in
that procedure is that someone else could grab the same number and use it
before the record saves, in which case I should get an insert error. I would
like to be able to catch that error and go back to the start. How do I do
that?
Thanks
 
Another alternative is to use an unbound form and some simple VBAS code
to store the record at the click of a command button; under this
approach, you can pick up the last number used and calculate the next
right at the time of saving, rather than upon entering a new record,
which will resolve the conflicts in a multi-user environment - that is
to say, the chance of two users clicking the button on the exact same
split second it takes for the code to run, is next to nothing.

HTH,
Nikos
 
Nikos,
Thanks, that is what I am doing, but I wanted to build in the safety net of
catching the error if it occurs, and going back to recalculate rather than
presenting the user with a possibly unintelligible error message.
Unfortunately the documentation available on the error conditions from any
method (in this case the execute method) is sparse to say the least. Also I
am using this as a evaluation of the Access' potential in developing
industrial strength applications and this is one thing that I would expect a
robust application to be able to deal with.
 
I've never had the need for this so I'm not an expert on the subject,
but forcing a duplicate PK value error I found that the Err.Value is 0,
no description or source available... you can capture the Err.Number in
your error handling, and act accordingly, but the duplicate PK is not
the only actual error that will produce a 0 error, a wrong data type
will as well (and possibly other things?).

Nikos
 
Nikos,
Again thanks. Good old Microsoft being helpful to their customers as
always. At least with Oracle you get definable error messages. Admittedly
most of the descriptions for those error messages as somewhat obtuse, but at
least you know which one you got.
 
If Microsoft were helpful enough there would be no need for these NGs!
Anyway, admittedly Jet is nowhere near as robust as Oracle, or any other
major database engine for that matter; presumably because it wasn't
intended to compete with those to begin with! In my humble opinion,
Access's strength lies primarily in all the wonderful things it lets you
do with the user interface, automation etc, i.e. outside Jet.

Regards,
Nikos
 
Back
Top