Run two from one cntrl button

  • Thread starter Thread starter postman
  • Start date Start date
P

postman

Can i run two queries from the same button.
The button at the moment runs an append query from a temp table into a permanent dB table, but does not delete the contents of the Temp table afterwards.
So i have put another button on the form to run a delete query to empty the Temp table.
I would prefer to have one button do both queries, if possible.
Thanks in advance.
 
Can i run two queries from the same button.
The button at the moment runs an append query from a temp table into a permanent dB table, but does not delete the contents of the Temp table afterwards.
So i have put another button on the form to run a delete query to empty the Temp table.
I would prefer to have one button do both queries, if possible.
Thanks in advance.
Click to expand...

Just code the button event to do what you want?

DoCmd.SetWarnngs False '** Optional
DoCmd.OpenQuery "AppendQueryName"
DoCmd.OpenQuery "DeleteQueryName"
DoCmd.SetWarnings True ' ** only if you previously set warnings to
false.
 
Sorry Fred
the current lines are:
--------------------------------------------
Private Sub Command88_Click()
On Error GoTo Err_Command88_Click

Dim stDocName As String

stDocName = "appqry_SBuyTemp > SBuy Contracts"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command88_Click:
Exit Sub

Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

End Sub
---------------------------------------------
So your suggestion would look like this?
---------------------------------------------
Private Sub Command88_Click()
On Error GoTo Err_Command88_Click

Dim stDocName As String

stDocName = "appqry_SBuyTemp > SBuy Contracts"
DoCmd.OpenQuery stDocName, acNormal, acEdit
stDocName = "Tbl_SBuy Temp Delete"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command88_Click:
Exit Sub

Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

End Sub
 
Sorry Fred
the current lines are:
--------------------------------------------
Private Sub Command88_Click()
On Error GoTo Err_Command88_Click

Dim stDocName As String

stDocName = "appqry_SBuyTemp > SBuy Contracts"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command88_Click:
Exit Sub

Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

End Sub
---------------------------------------------
So your suggestion would look like this?
---------------------------------------------
Private Sub Command88_Click()
On Error GoTo Err_Command88_Click

Dim stDocName As String

stDocName = "appqry_SBuyTemp > SBuy Contracts"
DoCmd.OpenQuery stDocName, acNormal, acEdit
stDocName = "Tbl_SBuy Temp Delete"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command88_Click:
Exit Sub

Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

End Sub
Click to expand...

All you really need is:

Private Sub Command88_Click()
On Error GoTo Err_Command88_Click

DoCmd.OpenQuery "appqry_SBuyTemp > SBuy Contracts"
DoCmd.OpenQuery "Tbl_SBuy Temp Delete"

Exit_Command88_Click:
Exit Sub
Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

End Sub

You will receive a warning message when you run each of the queries
that you are about to Append (or Delete) X number of records etc. It
will also allow you to cancel the Append or Delete.

The DoCmd.SetWarnings False and then True is used to bypass the
warnings. Use it only if you are absolutely sure that you wish the
append or delete to go on without user intervention.
 
Sorry to bother again Fred,

DoCmd.SetWarnngs False
Throws a compile error:
" Method or data member not found" and highlighted in debugger is
".SetWarnings"

Likewise error:
me.ds_SBuy Temp.requery
which I placed directly after last line, to refresh the sub-table form on
the form. as suggested.
Code:
...........................................................
Private Sub Command88_Click()
On Error GoTo Err_Command88_Click

DoCmd.SetWarnngs False
DoCmd.OpenQuery "appqry_SBuyTemp > SBuy Contracts"
DoCmd.OpenQuery "Tbl_SBuy Temp Delete"
DoCmd.SetWarnings True
me.ds_SBuy Temp.requery

Exit_Command88_Click:
Exit Sub
Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

End Sub
......................................................
I like the idea of turning warnings off for this code run, smooths the whole
process & it's somthing I could implement elsewhere.

Thanks
 
Sorry to bother again Fred,

DoCmd.SetWarnngs False
Throws a compile error:
" Method or data member not found" and highlighted in debugger is
".SetWarnings"

Likewise error:
me.ds_SBuy Temp.requery
which I placed directly after last line, to refresh the sub-table form on
the form. as suggested.
Code:
..........................................................
Private Sub Command88_Click()
On Error GoTo Err_Command88_Click

DoCmd.SetWarnngs False
DoCmd.OpenQuery "appqry_SBuyTemp > SBuy Contracts"
DoCmd.OpenQuery "Tbl_SBuy Temp Delete"
DoCmd.SetWarnings True
me.ds_SBuy Temp.requery

Exit_Command88_Click:
Exit Sub
Err_Command88_Click:
MsgBox Err.Description
Resume Exit_Command88_Click

End Sub
.....................................................
I like the idea of turning warnings off for this code run, smooths the whole
process & it's somthing I could implement elsewhere.

Thanks
Click to expand...

That code is fine and should work.
You might try checking your References for any marked Missing.

Open any module in Design view (or click Ctrl + G).
On the Tools menu, click References.
Click to clear the check box for the type library or object library
marked as "Missing:."

An alternative to removing the reference is to restore the referenced
file to the path specified in the References dialog box. If the
referenced file is in a new location, clear the "Missing:" reference
and create a new reference to the file in its new folder.

See Microsoft KnowledgeBase articles:
283115 'ACC2002: References That You Must Set When You Work with
Microsoft Access'
Or for Access 97:
175484 'References to Set When Working With Microsoft Access' for
the correct ones needed,
and
160870 'VBA Functions Break in Database with Missing References' for
how to reset a missing one.

For even more information, see
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Another method to run the append and delete queries, without using
SetWarnings, is:

Private Sub Command88_Click()
On error GoTo Err_Handler
CurrentDb.Execute "appqry_SBuyTemp > SBuy Contracts", dbFailOnError
CurrentDb.Execute "Tbl_SBuy Temp Delete", dbFailOnError

Exit_This_Sub:
Exit Sub
Err_Handler:
Your error handling here
Resume Exit_This_Sub
End Sub
 
Back
Top