Running two action queries at once

A

Andi Lee Davis

I have this code which allows you to reverse an archived proposal to a live
one.
The 1st query is an update query and the second is a delete query.
However I can't get the information from my pop up box to correspond to
match the proposal / deal I want to change. The error is in the last two
lines.

Private Sub Command1_Click()

Dim Message
Dim Title
Dim Default
Dim strFilter As String

Message = "Enter the number of the Proposal Number you wish to reverse"
Title = "Proposal Number?"
Default = 0
StrPropNo = InputBox(Message, Title, Default)

Let ProposalID = StrPropNo

DoCmd.OpenQuery "ReverseProposal", acViewNormal, acEdit, "PropNo = " &
Queries!ReverseProposal!DealNo & ""
DoCmd.OpenQuery "ReverseProposal2", acViewNormail, acEdit, "PropNo = " &
Queries!ReverseProposal!DealNo & ""

End Sub

Could somebody help me with this? Thank you very much

Andi Lee Davis
 
W

Wayne Morgan

I checked to make sure, but I only show 3 arguments for OpenQuery, you have
4. Also, if ReverseProposal and ReverseProposal2 are action queries (i.e.
update and delete queries) then you need to run them, not open them. The
command needed would be DoCmd.RunSQL. What you may prefer to this command is
the Execute command.

Example:
CurrentDb.QueryDefs("ReverseProposal").Execute dbFailOnError

This will require that you set a Reference to DAO if you don't currently
have one. In the code window, go to Tools|References and see if Microsoft
DAO 3.6 Object Library is checked.
 
P

Pete

Assuming that PropNo is a numeric field and not a text field, try.

Private Sub Command1_Click()

Dim intFilter As Integer

intFilter = InputBox("Enter the number of the Proposal Number you wish to
reverse", "Enter Prposal No")

DoCmd.OpenQuery "ReverseProposal", acViewNormal, acEdit, "PropNo = " &
intFilter & ""

DoCmd.OpenQuery "ReverseProposal2", acViewNormail, acEdit, "PropNo = " &
intFilter & ""

End Sub

You need to be consistent with your data types. If PropNo is text then:
Private Sub Command1_Click()

Dim strFilter As String

strFilter = InputBox("Enter the number of the Proposal Number you wish to
reverse", "Enter Prposal No")

DoCmd.OpenQuery "ReverseProposal", acViewNormal, acEdit, "PropNo = '" &
strFilter & "'"

DoCmd.OpenQuery "ReverseProposal2", acViewNormail, acEdit, "PropNo = '" &
strFilter & "'"

End Sub

Note the single quotes either side of strFilter in the Open Query where
clause.
 
A

Andi Lee Davis

hi there - the proposal number is numeric because it is the ProposalID -
Primary key -

Ok I will try all three suggestions in order to update and delete.

In answer to Waynes question. They are an update and delete query. Because I
have a live table and a history table. When a proposal is finished I have an
append query which writes the data from the live table to the archive table
on the event a check box is made true.
The live proposals forms have a filter to hide all checked proposals from
view.
If this happens accidentilly I wanted a way of reversing the process so that
the proposal was unchecked in the live table and deleted from the archive
table.

Of course you could run both queries and have it ask for the proposal number
each time it ran each query which I have at the moment. But I thought it
would save time by only asking for it once and running one query then the
other.

However I shall investigate all three suggestions.

Thanks Andi
 
A

Andi Lee Davis

Ok this seems fair. But if you type in doCmd.OpenQuery and the 3, normal and
edit etc arguments anyway it will run the query anyway.
 
A

Andi Lee Davis

Hi I tried both but it came up with an error.

Complie Error:

Wrong number of arguments or invalid property settings


Any I deas?
 
W

Wayne Morgan

Ok, since the query appears to be a store query (one that you have in the
Queries tab of the Database window), try running the query manually from
there. Does it work? If not, then calling it from code won't work either.
Also, you shouldn't need to pass it the parameter if the value is available
in an open form, instead have the query refer to the control on the form to
get the value. To do this, you would place a statement similar to the
following in the query's criteria box for that field.

Forms!frmMyForm!txtMyTextbox
 
P

Pete

Hi Andi

Sorry I was getting my commands muddled. You cannot pass a parameter into
the OpenQuery Command. Instead create your query as a string and then use
DoCmd.RunSQL in the example below replace MySQL with your queries' SQL - view
each query in design SQL view, replace the line feeds with spaces (so it
forms 1 continuous line) and copy and paste the resultant string. The
SetWarnings commands prevent the user having to confirm the action queries.


Private Sub Command1_Click()
Dim intFilter As Integer
intFilter = InputBox("Enter the number of the Proposal Number you wish to
reverse", "Enter Prposal No")
DoCmd.SetWarnings False
DoCmd.RunSQL"mySQL WHERE PropNo = " & intFilter & ""
DoCmd.RunSQL"mySQL WHERE PropNo = " & intFilter & ""
DoCmd.SetWarnings True
End Sub
 
Top