Run Time Error 3061 Too Few Parameters Expected 1 (1 Viewer)

[atrium]

I have been down this track before and for the life of me I cannot see where the problem is. I have gone over the other posts, compared both problems, practically replicated what I used before to overcome the problem and I can't get passed it.

All I want to do is isolate certain records, loop through them and complete certain functions for each individual record. At this point I can't isolate the record set


The code in question

 Private Sub ProcessTasksButt_Click()
  ' This is where the TaskRunner takes over and processes the tasks ticked to be processed.
  If DLookup("ProcessTask", "APActionTasksQry", "ProcessTask = -1") Then
     'There is at least 1 task needing processing
     MsgBox "We have tasks to process"
     Dim strQryFile As String
     Dim strQryFileSQL As String
     Dim db As Database
     Dim RecCounter As Integer
     Dim rs As Recordset
     DBEngine.SetOption dbMaxLocksPerFile, 1000000
     
     strQryFile = "APActionTasksQry"
     Me.QryFile = strQryFile
     
     Set db = CurrentDb
     'strQryFileSQL = "SELECT * FROM " & Me.QryFile & " WHERE  UserId = " & [Forms]![LoginFrm]![OpIdFld] & " AND ProcessTask = True " & " ORDER BY APId, APStepId;"
[COLOR=red] [/COLOR]
 [COLOR=red]     strQryFileSQL = "SELECT * FROM " & Me.QryFile & " WHERE APActionTasksQry.UserId = " & [Forms]![LoginFrm]![OpIdFld] & " AND APActionTasksQry.ProcessTask = True " & " ORDER BY APActionTasksQry.APId, APActionTasksQry.APStepId;"
[/COLOR]     Set rs = db.OpenRecordset(strQryFileSQL)
     RecCounter = 0
     '----------------------------------------------------------------------------------------------
     '----------------- The LOOP starts Here -------------------------------------------------------
     
     Do While Not rs.EOF
       MsgBox "Current Record number " & RecCounter
       If Me.CompletedFld = 0 Then
            MsgBox "Valid Record number " & RecCounter
            ' Deliver the precedent doc to create, create it and pick up the next task
    
              '-----------------------------------------------------------------------------------------------
              'Load precedent template as a dotx, merge fields into precedent document, allow user to modify if needed, save it as a pdf
              ' The one used  #########################
            On Error GoTo ErrTrap

The line after the red code is where the problem is.

Now the Qry that is the source of this is created using

 SELECT ActionTasks.ActionTaskId, ActionTasks.APId, ActionPlans.Title, ActionTasks.APStepId, ActionPlanSteps.APStepNumber, ActionPlanSteps.StepDesc, ActionTasks.PrecDocId, ActionTasks.PrecDocCode, PrecedentDocuments.Description, ActionTasks.ActionDueDate, ActionTasks.ClientId, Clients.ClientShortFileNo, Clients.ClientFileNumber, Clients.FirstName, Clients.BranchCode AS intClientBranchCode, [Group Branches_1].BranchCode AS strClientBranchCode, ActionTasks.MatterId, Matters.MatterShortNo, Matters.MatterLongNo, Matters.MatterTitle, Matters.BranchCode AS intMatterBranchCode, [Group Branches].BranchCode AS strMatterBranchCode, ActionTasks.UserId, ActionTasks.DateCreated, ActionTasks.Suppressed, ActionTasks.DateTaskCompleted, ActionTasks.Completed, ActionTasks.ProcessTask
FROM ((((((ActionTasks LEFT JOIN ActionPlans ON ActionTasks.APId = ActionPlans.APId) LEFT JOIN ActionPlanSteps ON ActionTasks.APStepId = ActionPlanSteps.APStepId) LEFT JOIN Clients ON ActionTasks.ClientId = Clients.ClientId) LEFT JOIN Matters ON ActionTasks.MatterId = Matters.MatterId) LEFT JOIN PrecedentDocuments ON ActionTasks.PrecDocId = PrecedentDocuments.PrecDocId) LEFT JOIN [Group Branches] ON Matters.BranchCode = [Group Branches].BranchId) LEFT JOIN [Group Branches] AS [Group Branches_1] ON Clients.BranchCode = [Group Branches_1].BranchId
WHERE (((ActionTasks.ActionDueDate)<=Date()) AND ((ActionTasks.UserId)=[Forms]![LoginFrm]![OpIdFld]) AND ((ActionTasks.DateTaskCompleted) Is Null) AND ((ActionTasks.Completed)=False))
ORDER BY ActionTasks.APId, ActionTasks.APStepId;

As you can see in the code where the problem is I have tried a number of versions but still can't get it to work.

Thank you to anyone with a solution or even a suggestion where it's wrong.

Atrium

 

[arnelgp]

why using Me, when you can use the strQryFile:

strQryFileSQL = "SELECT * FROM " & strQryFile & " WHERE APActionTasksQry.UserId = " & [Forms]![LoginFrm]![OpIdFld] & " AND APActionTasksQry.ProcessTask = True " & " ORDER BY APActionTasksQry.APId, APActionTasksQry.APStepId;"

also use Querydefs to open your recordset:

set rs = db.QueryDefs(strQryFileSQL).OpenRecordset(dbOpenDynaset)

 

[atrium]

Thanks Arnelgp for your help.

I keep on getting compile errors on the
set rs = db.QueryDefs(strQryFileSQL).OpenRecordset(dbOpenDy naset)

do I need to declare dbOpenDy naset or initialise them ??

atrium

 

[JHB]

Only for the compiler error you get.
You've a space in "dbOpenDy naset" it should be "dbOpenDynaset"
I would go back and use the first SQL-string:

 strQryFileSQL = "SELECT * FROM " & Me.QryFile & " WHERE  UserId = " & [Forms]![LoginFrm]![OpIdFld] & " AND ProcessTask = True " & " ORDER BY APId, APStepId;"

No need for " UserId = [Forms]![LoginFrm]![OpIdFld]" because you've selected it out in the first query.
The problem I think is the "ProcessTask = True", normally "True" = "-1" so "ProcessTask = -1"
So it should be:

strQryFileSQL = "SELECT * FROM " & strQryFile & " WHERE  ProcessTask = -1"  & " ORDER BY APId, APStepId;"
Set rs = db.OpenRecordset(strQryFileSQL, dbOpenDynaset)