By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

DoCmd.OpenQuery and error trapping

P: n/a
jj
I've got a form button that fires off 3 queries but if the first query
returns an error, I don't want the other two queries to happen.

Example: first query runs an insert from a linked table but if for some
reason a field is missing a value that's required, Access throws up an
error. At that point I just want to end the process and notify them of the
error so they can correct the data and try the process again.
Here's my code:
DoCmd.OpenQuery "download_records_clear", acNormal, acEdit (clears
a temp table)
DoCmd.OpenQuery "download_records", acNormal, acEdit (downloads
linked records)
DoCmd.OpenQuery "download_records_set", acNormal, acEdit (update
linked records d/l value)

Well if Query "download_records" doesn't go well, I don't want to do query
"download_records_set".
Is there some sort of error checking I can put in between those two lines?

Here's the complete code for that button:

------------------------------------------------------
Private Sub ImportWebFiles_Click()
Dim qryOption As Boolean
On Error GoTo Err_ImportWebFiles_Click
qryOption = Application.GetOption("Confirm Action Queries")
Application.SetOption "Confirm Action Queries", False

DoCmd.OpenQuery "download_records_clear", acNormal, acEdit
DoCmd.OpenQuery "download_records", acNormal, acEdit
DoCmd.OpenQuery "download_records_set", acNormal, acEdit

MsgBox "Records downloaded."

Application.SetOption "Confirm Action Queries", qryOption
Exit_ImportWebFiles_Click:
Exit Sub

Err_ImportWebFiles_Click:
MsgBox Err.Description
Application.SetOption "Confirm Action Queries", qryOption
DoCmd.SetWarnings True
Resume Exit_ImportWebFiles_Click

End Sub
------------------------------------------------------------
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"jj" <jj@test.net> wrote in message
news:X1*******************@twister.rdc-kc.rr.com...
I've got a form button that fires off 3 queries but if the first query
returns an error, I don't want the other two queries to happen.

Example: first query runs an insert from a linked table but if for some
reason a field is missing a value that's required, Access throws up an
error. At that point I just want to end the process and notify them of the error so they can correct the data and try the process again.
Here's my code:
DoCmd.OpenQuery "download_records_clear", acNormal, acEdit (clears a temp table)
DoCmd.OpenQuery "download_records", acNormal, acEdit (downloads
linked records)
DoCmd.OpenQuery "download_records_set", acNormal, acEdit (update linked records d/l value)

Well if Query "download_records" doesn't go well, I don't want to do query "download_records_set".
Is there some sort of error checking I can put in between those two

lines?

Use the Execute method instead with the appropriate option.

Dim MyDB as Database
Set MyDB = CurrentDB

MyDB.Execute "download_records_clear", dbFailOnError
MyDB.Execute "download_records", dbFailOnError
MyDB.Execute "download_records_set", dbFailOnError

The dbFailOnError causes an Error event to be raised if the query fails.
Code execution should then revert to your error handler.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #2

P: n/a
jj
Hmmm.. it's giving me a User-defined Data Type Not Found error with:

Dim MyDB As Database
Set MyDB = CurrentDb

I didn't have to define a database connection before or anything, so what
would it be looking for here?

Thanks!


"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:bn************@ID-98015.news.uni-berlin.de...
"jj" <jj@test.net> wrote in message
news:X1*******************@twister.rdc-kc.rr.com...
I've got a form button that fires off 3 queries but if the first query
returns an error, I don't want the other two queries to happen.

Example: first query runs an insert from a linked table but if for some
reason a field is missing a value that's required, Access throws up an
error. At that point I just want to end the process and notify them of

the
error so they can correct the data and try the process again.
Here's my code:
DoCmd.OpenQuery "download_records_clear", acNormal, acEdit

(clears
a temp table)
DoCmd.OpenQuery "download_records", acNormal, acEdit (downloads linked records)
DoCmd.OpenQuery "download_records_set", acNormal, acEdit

(update
linked records d/l value)

Well if Query "download_records" doesn't go well, I don't want to do

query
"download_records_set".
Is there some sort of error checking I can put in between those two

lines?

Use the Execute method instead with the appropriate option.

Dim MyDB as Database
Set MyDB = CurrentDB

MyDB.Execute "download_records_clear", dbFailOnError
MyDB.Execute "download_records", dbFailOnError
MyDB.Execute "download_records_set", dbFailOnError

The dbFailOnError causes an Error event to be raised if the query fails.
Code execution should then revert to your error handler.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 12 '05 #3

P: n/a
jj
Nevermind I found that I had to select DAO 3.6 in Tools>References.

However, is there another way to work this so that setting doesn't have to
be made on other files, etc? Can .Execute only be used with DAO 3.6?

"jj" <jj@test.net> wrote in message
news:OZ*******************@twister.rdc-kc.rr.com...
Hmmm.. it's giving me a User-defined Data Type Not Found error with:

Dim MyDB As Database
Set MyDB = CurrentDb

I didn't have to define a database connection before or anything, so what
would it be looking for here?

Thanks!


"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:bn************@ID-98015.news.uni-berlin.de...
"jj" <jj@test.net> wrote in message
news:X1*******************@twister.rdc-kc.rr.com...
I've got a form button that fires off 3 queries but if the first query
returns an error, I don't want the other two queries to happen.

Example: first query runs an insert from a linked table but if for some reason a field is missing a value that's required, Access throws up an
error. At that point I just want to end the process and notify them
of
the
error so they can correct the data and try the process again.
Here's my code:
DoCmd.OpenQuery "download_records_clear", acNormal, acEdit

(clears
a temp table)
DoCmd.OpenQuery "download_records", acNormal, acEdit

(downloads linked records)
DoCmd.OpenQuery "download_records_set", acNormal, acEdit

(update
linked records d/l value)

Well if Query "download_records" doesn't go well, I don't want to do

query
"download_records_set".
Is there some sort of error checking I can put in between those two

lines?

Use the Execute method instead with the appropriate option.

Dim MyDB as Database
Set MyDB = CurrentDB

MyDB.Execute "download_records_clear", dbFailOnError
MyDB.Execute "download_records", dbFailOnError
MyDB.Execute "download_records_set", dbFailOnError

The dbFailOnError causes an Error event to be raised if the query fails.
Code execution should then revert to your error handler.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.