Success/fail return code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andreas - 20 Jan 2007 12:49 GMT

I execute the following code that will append data into 2 related tables:
Private Sub Command129_Click()
DoCmd.OpenQuery "Quotation-to-Invoice Query", , acAdd
DoCmd.OpenQuery "Quotation-To-Invoice(Details)", , acAdd
End Sub

Is there a way to retrieve the success or fail status from the first command
so if it failed, I would stop the second command from executing?
Any other way that I could maintain consistency?

Reply to this Message

Douglas J. Steele - 20 Jan 2007 13:18 GMT

OpenQuery can't return status.

Try using

On Error Resume Next

CurrentDb.Execute "Quotation-to-Invoice Query", dbFailOnError
If Err.Number = 0 Then
CurrentDb.Execute "Quotation-To-Invoice(Details)", dbFailOnError
Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"occurred running Quotation-to-Invoice Query."
End If

You might also look into using Transactions so that if the second query
fails, you can roll back the first query as well.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I execute the following code that will append data into 2 related tables:
> Private Sub Command129_Click()
[quoted text clipped - 5 lines]
> command so if it failed, I would stop the second command from executing?
> Any other way that I could maintain consistency?

Reply to this Message

Andreas - 20 Jan 2007 13:43 GMT

I haven't used that before but it returns error 3061 - too few parameters.

Should it work in my case or maybe a parameter is missing?

> OpenQuery can't return status.
>
[quoted text clipped - 22 lines]
>> command so if it failed, I would stop the second command from executing?
>> Any other way that I could maintain consistency?

Reply to this Message

Douglas J. Steele - 20 Jan 2007 19:14 GMT

Try:

On Error Resume Next

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("Quotation-to-Invoice Query")
qdfCurr.Execute dbFailOnError
If Err.Number = 0 Then

Set qdfCurr = dbCurr.QueryDefs("Quotation-To-Invoice(Details)")
qdfCurr.Execute dbFailOnError
Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"occurred running Quotation-to-Invoice Query."
End If

If you're still getting the same error, so the queries actually run by
themselves?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I haven't used that before but it returns error 3061 - too few parameters.
>
[quoted text clipped - 26 lines]
>>> command so if it failed, I would stop the second command from executing?
>>> Any other way that I could maintain consistency?

Reply to this Message

Douglas J. Steele - 20 Jan 2007 21:37 GMT

I noticed you've posted another thread on this topic to another newsgroup.

If you feel you need to post to more than one group (HINT: it's seldom
necessary), please have the courtesy to cross-post (send the one message to
all groups at once), rather than multi-post (send individual messages to
each group). In this way, all responses to your post will be available
together, regardless of what group the responder was in, and the rest of us
won't have to read your post multiple times. (It also uses fewer server
resources)

I see you're using Outlook Express. Click the "Newsgroups:" label to the
left of the box containing the name of the current newsgroup. That will open
a dialog that will let you add additional newsgroups to your post.

Note that it's generally consider to be A Bad Thing to cross-post to more
than about 2 or 3 newsgroups.

Based on what I read in the other newsgroup, it would be a good idea to post
the SQL for your queries.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Try:
>
[quoted text clipped - 50 lines]
>>>> executing?
>>>> Any other way that I could maintain consistency?

Reply to this Message

Andreas - 21 Jan 2007 07:45 GMT

Ok, although this happened only for one post by mistake, I'll try not to do
it again.

Thank you very much for your help.

>I noticed you've posted another thread on this topic to another newsgroup.
>
[quoted text clipped - 71 lines]
>>>>> executing?
>>>>> Any other way that I could maintain consistency?

Reply to this Message