Running queries one after the other

[Noel]

Hi. Some time ago I created a command button On Click
event code as follows to run two Queries, one after the
other. I retested this recently and I now find that the
first query runs but the second does not. I must have
changed something and forgot to retest it at the time.
Whats gone wrong? Thanks, Noel

The first query is called Append School Offers to Archive
The second is called Clear Fields - School Offers

Private Sub AppendSchoolOffers_Click()
On Error GoTo Err_AppendSchoolOffers_Click
DoCmd.SetWarnings False
Dim stDocName As String

stDocName = "Append School Offers to Archive"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.OpenQuery "Clear Fields - School Offers"
DoCmd.Close
Exit_AppendSchoolOffers_Click:
Exit Sub

Err_AppendSchoolOffers_Click:
MsgBox Err.Description
Resume Exit_AppendSchoolOffers_Click

DoCmd.SetWarnings True
End Sub

 

[Noel]

Hi again - just noticed that something in the second
query was not correct and it was this that was stopping
it running. All works OK but would still welcome comments
on the code Im using - the way it runs the second query
seems to be different to the way it runs the first. Why
is this? Thanks, Noel

 

[fredg]

Hi. Some time ago I created a command button On Click
event code as follows to run two Queries, one after the
other. I retested this recently and I now find that the
first query runs but the second does not. I must have
changed something and forgot to retest it at the time.
Whats gone wrong? Thanks, Noel

The first query is called Append School Offers to Archive
The second is called Clear Fields - School Offers

Private Sub AppendSchoolOffers_Click()
On Error GoTo Err_AppendSchoolOffers_Click
DoCmd.SetWarnings False
Dim stDocName As String

stDocName = "Append School Offers to Archive"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.OpenQuery "Clear Fields - School Offers"
DoCmd.Close
Exit_AppendSchoolOffers_Click:
Exit Sub

Err_AppendSchoolOffers_Click:
MsgBox Err.Description
Resume Exit_AppendSchoolOffers_Click

DoCmd.SetWarnings True
End Sub

It's running, but your line DoCmd.Close is closing it as soon as it
opens.
What are you trying to Close? The form that calls the queries?
Use
DoCmd.Close acForm, Me.Name

Also, you have the
DoCmd.SetWarnings True
in the wrong place.
The code will never reach that line as it will either exit after
closing the form, or, if there is an error, resume at the same
Exit_AppendSchoolOffers_Click: line.

Move that line to right after the second OpenQuery line.

 

[Noel]

Thank you Fred. As I said in my update, the problem
appeared to be in the query itself. But I can see the
errors you point out in the code. I will take your advice
incorporate the changes you suggest. Thanks again, Noel