returning a query error or looping on an error

  • Thread starter Thread starter dickminter
  • Start date Start date
D

dickminter

I am using an update sql query def in a loop to
sequentially update all fields in a table from a another
table. The query uses variables to set the fieldname to
be updated, and the related update criteria in the new
data table.

The problem is that not every field in the update table
has an update (i.e. matching criteria) in the new data
table, causing a query error. How can I either restrict
query action (using a subquery?), or cause my rountine to
cancel the query aciton and loop when the error occurs?

DM
 
How are you calling the query (syntax)?

Have you tried the

CurrentDb.Execute "QueryName", dbFailOnError
or
CurrentDb.Execute "SQL Statement", dbFailOnError

statement? With the dbFailOnError, it will fail if the query fails. You
should be able to trap this in the form's Error event and Resume Next,
depending on the error number. You'll need to determine the error number for
the error you want to ignore. Another option is to remove the dbFailOnError,
but then you won't know if an error occurred. Is the error just that there
is nothing to do? If so, I don't think the above will give you that error. I
believe it will just execute on zero records. The dbFailOnError should kick
in if there is a write error.
 
Back
Top