Add error handling to parameter query?

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

Guest

I have a between date type parameter query. How would I add error handling to
a parameter. Example: If the user puts in the wrong date. I want it to return
a msg stating...Invalid date entered.

how or where would I begin.

Thanks in advance
 
Generally, Query processing does not have any way for you to customise the
error-handling AFAIK.

A work-around is to create a Form frmParameter to allow the user to enter
param values with a CommandButton to open / execute the Query which uses the
Form's Controls as the Parameters. In the CommandButton_Click Event, you
can validate the user's input. If the input is invalid, you can provide a
msg to the user. If the input is valid, you can then open / execute the
Query and Access will replace the Parameters with the values in the
Controls.
 
Thank you that is not what I was looking for but it is a GREAT work around.
Thanks again
 
Van said:
Generally, Query processing does not have any way for you to customise the
error-handling AFAIK.
Click to expand...

Well, can you pay general regard to errors (though in the OP's specific
case if the parameter value is of the wrong type the query won't
actually be executed, of course).

Take for example a table with a UNIQUE constraint:

CREATE TABLE Test (
key_col INTEGER NOT NULL UNIQUE,
data_col INTEGER NOT NULL
)
;

some existing data:

INSERT INTO Test (key_col, data_col)
VALUES (1, 1)
;

and a procedure ('parameter query') to add a new row:

CREATE PROCEDURE Proc1Test (
arg_key_col INTEGER,
arg_data_col INTEGER
) AS
INSERT INTO Test (key_col, data_col)
VALUES (arg_key_col, arg_data_col)
;

Try executing this with data that would fall foul of the unique
constraint:

EXECUTE Proc1Test 1, 2
;

and your get an error, '...were not successful because they would
create duplicate values...'

Now consider a revised procedure that 'handles' the error:

CREATE PROCEDURE Proc2Test (
arg_key_col INTEGER,
arg_data_col INTEGER
) AS
INSERT INTO Test (key_col, data_col)
SELECT DISTINCT arg_key_col, arg_data_col
FROM Test AS T1
WHERE NOT EXISTS (
SELECT *
FROM Test AS T2
WHERE arg_key_col = T2.key_col
)
;

Executing this with data that would fall foul of the unique constraint
does not cause an error:

EXECUTE Proc1Test 1, 2
;

The INSERT fails, a fact that can be detected by examining the 'rows
affected' property.

Personally, I wouldn't do it this way but then I don't have a problem
with this engine messages that have been branded as *ugly* <g>. I'd
rather have an error I can trap, rather than a simple success/failure
flag.

Jamie.

--
 
Van,

In reguards to this reponse you left a while back and I thank you for your
help. Do you know of a way I can add a parameter that would fire off a start
date and a End Date from the same field in my ain query? The only catch I am
using the same query nested within two other queries and it ask for the
parameter everytime I run the main query. I have code that fires off the
query in turn ask for the two seprate Date parameters (3 times) then sends
the query to Excel. This is caused because it is nested. I have tried various
ways to elimanate this but come up with nothing. Any Ideas? Thanks again for
your help!
 
I normally avoid this by using a frmParameter as detailed in my first post
in this thread. This way, I simply use the references to the Controls on
the Form as many time as I need ...
 
Back
Top