Running a Parameter Query within a Loop

[Thales750]

Hello Everyone,
This time I have a hard one,
Can anyone see anything wrong with this code?
It’s supposed to run a parameter query within a loop.

Private Sub Command171_Click()
Dim MyDB As Database, RS As Recordset
 Dim Para As String
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set RS = MyDB.OpenRecordset("tblAnalystPerAnalysis", dbOpenDynaset)
Para = RS!LOCCON
      RS.MoveFirst
    Do Until RS.EOF
 With CurrentDb.QueryDefs("qryAnalistAppend")
    .Parameters("p1") = Para
 
   .Execute
   End With
  RS.MoveNext
      Loop

It halts on .Execute

It reports "missing parameters, 2 are needed"
Which is weird because the parameter query is made up of 2 queries that both need a “p1” parameter and when it’s run manually, it only requires 1 “p1” entry
Now, if anyone knows a faster, better, easier, way to do this let me know.
Thank my remaining hair has turned white.

 

[WIS]

Hello Everyone,
This time I have a hard one,
Can anyone see anything wrong with this code?
It’s supposed to run a parameter query within a loop.

Private Sub Command171_Click()
Dim MyDB As Database, RS As Recordset
 Dim Para As String
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set RS = MyDB.OpenRecordset("tblAnalystPerAnalysis", dbOpenDynaset)
Para = RS!LOCCON
      RS.MoveFirst
    Do Until RS.EOF
 With CurrentDb.QueryDefs("qryAnalistAppend")
    .Parameters("p1") = Para
 
   .Execute
   End With
  RS.MoveNext
      Loop

It halts on .Execute

It reports "missing parameters, 2 are needed"
Which is weird because the parameter query is made up of 2 queries that both need a “p1” parameter and when it’s run manually, it only requires 1 “p1” entry
Now, if anyone knows a faster, better, easier, way to do this let me know.
Thank my remaining hair has turned white.

I've just put this on the General of this forum for a problem which looks a bit similar:

Courtesy of Litwin, Getz et al

Dim db As Database, rst As Recordset, qdf As QueryDef, prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("qrySomethingWithDateFilter")

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

Set rst = qdf.OpenRecordset(dbOpenDynaset)