DoCmd.OpenQuery and error trapping

[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
------------------------------------------------------------

[Rick Brandt]

"jj" <[e-mail]> 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

[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" <[e-mail]> 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

[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" <[e-mail]> 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" <[e-mail]> 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