Using recordsets to loop through a SQL Query to complete a process

[ericpsu32]

Hello,

I have a process in my head that I know can be done, but alas, I don't know how to do it. Here are the specifics.

I have a table in SQL, call it "invoices." It only has one field, a pkey, used for other operations. What I want to do is store that table in a record set. Then I want to loop through that table performing specific operations for each pkey.

For example, the pkey that is passed into that table, would be used in a stored procedure first, then it would be used to print a report. Then I would want to loop to the next pkey and peform those same actions again and so on...

Here is the action that i want to have happen in the middle of the loop...

Dim ocmd As New Command
Dim sSQL As String

sSQL = "[dbo].[sp_GenerateInvRecTLD] " & ??pkey?? & ", '" & Me.GLDate & "'"
Set ocmd.ActiveConnection = CurrentProject.Connection
ocmd.CommandText = strsql
ocmd.Execute

Docmd.openreport "report Name", , ,"[pkey] = " & ??Pkey??

 

[PWise]

Dim rst As ADODB.Recordset
rst.Open "Select * from invoices", CurrentProject.Connection
Dim ocmd As New Command
Set ocmd.ActiveConnection = CurrentProject.Connection
do while not rst.eof
 sSQL = "[dbo].[sp_GenerateInvRecTLD] " & rst!pkey & ", '" & Me.GLDate & "'"
ocmd.CommandText = strsql
ocmd.Execute


Docmd.openreport "report Name", , ,"[pkey] = " & rst!Pkey
rst.movenext

loop

not tested

 

[ericpsu32]

pwise...Thank you! I actually created a work around using two additional views and DLookups to fill my variables, but this looks like it will do the trick more seamlessly.

I will post it into my proces and try it out!

Thanks again!

 

[ericpsu32]

pwise,

I actually get an error that states the following. "Object variable or with block variable not set." This appears on the "rst.Open "Select * from vw_TLDShipmentsToInvoice", CurrentProject.Connection" line of code...

Any ideas?

 

[Golom]

Dim rst As ADODB.Recordset
[red]Set rst = New ADODB.Recordset[/red]
rst.Open "Select * from invoices", CurrentProject.Connection