Loop through query

  • Thread starter Thread starter Lori
  • Start date Start date
L

Lori

I have a query that has a list of ClientId's grouped by
the first 4 digits of the id. I now want to update
another table with each of these one at a time and run a
macro in between.

Qry
JGC0
MCSH
FEIN

Table "Variables" with one line with a field for ClientID.

I know I will have to do this in VBA but I'm not sure how
to update the Variables table with the querry information.

Thanks in advance for any help.

Lori
 
I have the loop part figured out. Now I just need to
reference the variable table. Do I need to open another
recordset?

Here is what I have so far. It creates the files with the
different names so I know it is looping right. It just
isn't updating the "TrustForQuicken" field in the
variables table.

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

Dim stDocName As String
Dim stText As String
Dim stFileName As String

stText = "Text Files"

rst.Open "qryTrustInterestPaidOn",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until rst.EOF
'change trust name in variables table
[variables]![trustforquicken] = rst!Four
'run quicken macro
stDocName = "mcrQuickenMonthly"
stFileName
= "J:\shared\Accounting\Quicken\ImportFiles\" & rst!Four
& ".qif"
DoCmd.RunMacro stDocName
stDocName = "rptQuicken"
DoCmd.OutputTo acOutputReport, stDocName, stText,
stFileName
'go to next record
rst.MoveNext

Loop

Thanks

Lori
 
Yes I need another recordset. I got it to work with a
second recordset.

Lori
-----Original Message-----
I have the loop part figured out. Now I just need to
reference the variable table. Do I need to open another
recordset?

Here is what I have so far. It creates the files with the
different names so I know it is looping right. It just
isn't updating the "TrustForQuicken" field in the
variables table.

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

Dim stDocName As String
Dim stText As String
Dim stFileName As String

stText = "Text Files"

rst.Open "qryTrustInterestPaidOn",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until rst.EOF
'change trust name in variables table
[variables]![trustforquicken] = rst!Four
'run quicken macro
stDocName = "mcrQuickenMonthly"
stFileName
= "J:\shared\Accounting\Quicken\ImportFiles\" & rst!Four
& ".qif"
DoCmd.RunMacro stDocName
stDocName = "rptQuicken"
DoCmd.OutputTo acOutputReport, stDocName, stText,
stFileName
'go to next record
rst.MoveNext

Loop

Thanks

Lori
-----Original Message-----
I have a query that has a list of ClientId's grouped by
the first 4 digits of the id. I now want to update
another table with each of these one at a time and run a
macro in between.

Qry
JGC0
MCSH
FEIN

Table "Variables" with one line with a field for ClientID.

I know I will have to do this in VBA but I'm not sure how
to update the Variables table with the querry information.

Thanks in advance for any help.

Lori
.
Click to expand...
.
Click to expand...
 
Back
Top