Running Multiple Queries

[Ella]

I am trying to run two up-date queries but only the first runs.
Can anyone explain where I’m going wrong.

Thanks
Ella

Private Sub Form_Close()
On Error GoTo Err_Form_Close

'frmDetails

Dim stDocName1 As String
Dim stDocName2 As String

stDocName1 = "qCountOpenActionsApend"
DoCmd.OpenQuery stDocName1, acNormal, acEdit

stDocName2 = "qUserEditing02frmDetailsNo"
DoCmd.OpenQuery stDocName2, acNormal, acEdit

[Forms]![frmReview]![frmReviewSub].Requery
[Forms]![frmReview]![frmActionForUser].Requery

Exit_Form_Close:
Exit Sub

Err_Form_Close:
MsgBox Err.Description
Resume Exit_Form_Close

End Sub

 

[bcap]

I'm afraid that question is impossible to answer unless you (1) say exactly
what happens; do you get an error message, and if so what is it? and (2)
post the SQL for the query.

 

[Stefan Hoffmann]

hi Ella,

I am trying to run two up-date queries but only the first runs.
Can anyone explain where I’m going wrong.
stDocName1 = "qCountOpenActionsApend"
DoCmd.OpenQuery stDocName1, acNormal, acEdit

OpenQuery is for Select queries only. Use CurrentDb.Execute, e.g.

On Local Error Goto LocalError

Dim db As DAO.Database
Dim Query As String

Set db = CurrentDb

Query = "qCountOpenActionsApend"
db.Execute Query, dbFailOnError

Query = "qUserEditing02frmDetailsNo"
db.Execute Query, dbFailOnError

[Forms]![frmReview]![frmReviewSub].Requery
[Forms]![frmReview]![frmActionForUser].Requery

Set db = Nothing

Exit Sub

LocalError:
Set db = Nothing
MsgBox "Error.."


mfG
--> stefan <--

 

[Ella]

Works well but one query is not working. I have tried to use the result of
one query as the value for the append query, qCountOpenActionsApend, this is
its SQL,
UPDATE tbDetails SET tbDetails.Actions = [qCountOpenActions]![Actions]
WHERE (((tbDetails.SADID)=[Forms]![frmReview]![frmReviewSub]![SADID]));
I need the number of actions in tbActionBy to be indicated in tbDetails for
each record.
To get the count I used this query. QCountOpenActions;
SELECT tbActionBy.SADID, Count(tbActionBy.Completed) AS Actions
FROM tbActionBy
GROUP BY tbActionBy.SADID, tbActionBy.Completed
HAVING (((tbActionBy.SADID)=[Forms]![frmReview]![frmReviewSub]![SADID]) AND
((tbActionBy.Completed)=False));
This is probably not the way to do this so I would appreciated any help to
achieve an easier result

Ella

hi Ella,

I am trying to run two up-date queries but only the first runs.
Can anyone explain where I’m going wrong.
stDocName1 = "qCountOpenActionsApend"
DoCmd.OpenQuery stDocName1, acNormal, acEdit

OpenQuery is for Select queries only. Use CurrentDb.Execute, e.g.

On Local Error Goto LocalError

Dim db As DAO.Database
Dim Query As String

Set db = CurrentDb

Query = "qCountOpenActionsApend"
db.Execute Query, dbFailOnError

Query = "qUserEditing02frmDetailsNo"
db.Execute Query, dbFailOnError

[Forms]![frmReview]![frmReviewSub].Requery
[Forms]![frmReview]![frmActionForUser].Requery

Set db = Nothing

Exit Sub

LocalError:
Set db = Nothing
MsgBox "Error.."


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Ella,

Works well but one query is not working. I have tried to use the result of
one query as the value for the append query, qCountOpenActionsApend, this is
its SQL,
UPDATE tbDetails SET tbDetails.Actions = [qCountOpenActions]![Actions]
WHERE (((tbDetails.SADID)=[Forms]![frmReview]![frmReviewSub]![SADID]));

Try this:

UPDATE tbDetails
SET tbDetails.Actions =
DCount("*",
"tbActionBy",
"NOT Completed AND SADID=" & tbDetails.SADID)
WHERE tbDetails.SADID=[Forms]![frmReview]![frmReviewSub]![SADID];


mfG
--> stefan <--