Form button and parameter to run update qry. Error 3061 1

[blackduck]

I have a button on a form that I want to run two Update queries from but am getting "Error: 3061 Too few parameters. Expected 1."

Its driving me nuts, any help would be greatly appreciated.

My code is:

Private Sub cmdComplete_Click()

If MsgBox("Are you sure?", vbYesNo) = vbYes Then
'Run Archive - Update
Dim strCompleteVisit As String
Dim strUpdateActionReqd As String
Dim errLoop As Error
' Define two SQL statements for action queries.
' Mark assessment as closed and add current date.
strCompleteVisit = "UPDATE Assessment SET Assessment.Closed = True, Assessment.DateClosed = Date() WHERE (((Assessment.Closed)=False) AND ((Assessment.SiteID)=[Forms]![frmCompleteVisit]![cboSite]));"
' Mark assessment ActionClosed where score is 4 or 99.
strUpdateActionReqd = "UPDATE Assessment SET Assessment.ActionClosed = True WHERE (((Assessment.ScoreID)<4) AND ((Assessment.SiteID)=[Forms]![frmCompleteVisit]![cboSite]));"

' Run action query to restore data. Trap for errors,
' checking the Errors collection if necessary.
On Error GoTo Err_Execute
CurrentDb.Execute strCompleteVisit, dbFailOnError
CurrentDb.Execute strUpdateActionReqd, dbFailOnError

On Error GoTo 0

RetValue = MsgBox("Site Visit now complete", vbOKOnly)
DoCmd.Close

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next

Else
'Don't do the update
MsgBox "Not Updated", vbOKOnly
End If

End Sub

 

[formerTexan]

Blackduck,

When passing parameters from form controls, you will have to include some additional info about those parameters through the use of a querydef.

Dim db As DAO.Database
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
Set db = CurrentDb()

    strCompleteVisit = "UPDATE Assessment SET Assessment.Closed = True, Assessment.DateClosed = Date() WHERE (((Assessment.Closed)=False) AND ((Assessment.SiteID)=[Forms]![frmCompleteVisit]![cboSite]));"

Set qdf = db.CreateQueryDef(vbNullString, strCompleteVisit )

For Each prm In qdf.Parameters
    prm.Value = Eval(prm.name)
Next prm
qdf.Execute

qdf.Close
db.close
Set prm = Nothing
Set qdf = Nothing
Set db = nothing

Cheers, Bill

 

[blackduck]

Thanks Bill, but I think I just didn't really get it. I put your code in adding the second query below yours. The first one runs, ie strCompleteVisit. But the second one doesn't.

Private Sub Command29_Click()
Dim db As DAO.Database
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
Set db = CurrentDb()

strCompleteVisit = "UPDATE Assessment SET Assessment.Closed = True, Assessment.DateClosed = Date() WHERE (((Assessment.Closed)=False) AND ((Assessment.SiteID)=[Forms]![frmCompleteVisit]![cboSite]));"
strUpdateActionReqd = "UPDATE Assessment SET Assessment.ActionClosed = True WHERE (((Assessment.ScoreID)<4) AND ((Assessment.SiteID)=[Forms]![frmCompleteVisit]![cboSite]));"

Set qdf = db.CreateQueryDef(vbNullString, strCompleteVisit)

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
qdf.Execute

qdf.Close
db.Close
Set prm = Nothing
Set qdf = Nothing
Set db = Nothing

End Sub

 

[formerTexan]

Hello again Blackduck,

you will need to set a querydef and run the parameters for each query. Sorry for not pointing that out.

Private Sub Command29_Click()
Dim db As DAO.Database
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
Set db = CurrentDb()

    strCompleteVisit = "UPDATE Assessment SET Assessment.Closed = True, Assessment.DateClosed = Date() WHERE (((Assessment.Closed)=False) AND ((Assessment.SiteID)=[Forms]![frmCompleteVisit]![cboSite]));"
    strUpdateActionReqd = "UPDATE Assessment SET Assessment.ActionClosed = True WHERE (((Assessment.ScoreID)<4) AND ((Assessment.SiteID)=[Forms]![frmCompleteVisit]![cboSite]));"

Set qdf = db.CreateQueryDef(vbNullString, strCompleteVisit)
For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next prm
qdf.Execute

Set qdf = db.CreateQueryDef(vbNullString, strUpdateActionReqd)
For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next prm
qdf.Execute

qdf.Close
db.Close
Set prm = Nothing
Set qdf = Nothing
Set db = Nothing
    
End Sub

See if this adjustment clears the protest.

Cheers, Bill

 

[blackduck]

It works! yeah!

Thank you soo much Bill, it works beautifully.

Regards
Kerri