Loop through query records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to loop through records in a query and save a report to word for
each record. Steve Schapel kindly got me started with some code but I am a
bit stuck

So far
Private Sub Command1_Click()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("[mailingReportBase]")

Set rst = qdf.OpenRecordset()

BaseSQL = qdf.SQL

Do Until rst.EOF
qdf.SQL = Left(BaseSQL, Len(BaseSQL) - 3) & " WHERE [companyId] =" &
rst![companyId]
DoCmd.OutputTo acReport, DLookup("[name]", "templates", _
"[templeId]=" &
[Forms]![systemForm]![mailContractForm].[Form]![letterFormBase].[Form]![template]), _
"RichTextFormat(*.rtf)", "C:\Documents and
Settings\Judith\Desktop\test1\'" & [fileLoc] & "'.rtf", False
Loop

rst.Close
Set rst = Nothing
qdf.SQL = BaseSQL
Set qdf = Nothing
Set dbs = Nothing

End Sub

I get an error on
Set rst = qdf.OpenRecordset()
Too few parameters expected. Any suggestions please
 
Does the query name actually contain [ and ] characters? You do not need
those characters to delimit the name of a query in this step when the query
name has blank spaces in the name. Try this:

Set qdf = dbs.QueryDefs("mailingReportBase")



--

Ken Snell
<MS ACCESS MVP>


judith said:
I am trying to loop through records in a query and save a report to word for
each record. Steve Schapel kindly got me started with some code but I am a
bit stuck

So far
Private Sub Command1_Click()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("[mailingReportBase]")

Set rst = qdf.OpenRecordset()

BaseSQL = qdf.SQL

Do Until rst.EOF
qdf.SQL = Left(BaseSQL, Len(BaseSQL) - 3) & " WHERE [companyId] =" &
rst![companyId]
DoCmd.OutputTo acReport, DLookup("[name]", "templates", _
"[templeId]=" &
Click to expand...
[Forms]![systemForm]![mailContractForm].[Form]![letterFormBase].[Form]![temp
late]), _
"RichTextFormat(*.rtf)", "C:\Documents and
Settings\Judith\Desktop\test1\'" & [fileLoc] & "'.rtf", False
Loop

rst.Close
Set rst = Nothing
qdf.SQL = BaseSQL
Set qdf = Nothing
Set dbs = Nothing

End Sub

I get an error on
Set rst = qdf.OpenRecordset()
Too few parameters expected. Any suggestions please
Click to expand...
 
I think I have found my mistake from your great mvps.org/access site. The
query had a criteria based on an open form field. I now understand that it
must be a straight query so I will test for the criteria in the Do Loop.
Thanks very much for your help

Ken Snell said:
Does the query name actually contain [ and ] characters? You do not need
those characters to delimit the name of a query in this step when the query
name has blank spaces in the name. Try this:

Set qdf = dbs.QueryDefs("mailingReportBase")



--

Ken Snell
<MS ACCESS MVP>


judith said:
I am trying to loop through records in a query and save a report to word for
each record. Steve Schapel kindly got me started with some code but I am a
bit stuck

So far
Private Sub Command1_Click()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("[mailingReportBase]")

Set rst = qdf.OpenRecordset()

BaseSQL = qdf.SQL

Do Until rst.EOF
qdf.SQL = Left(BaseSQL, Len(BaseSQL) - 3) & " WHERE [companyId] =" &
rst![companyId]
DoCmd.OutputTo acReport, DLookup("[name]", "templates", _
"[templeId]=" &
Click to expand...
[Forms]![systemForm]![mailContractForm].[Form]![letterFormBase].[Form]![temp
late]), _
"RichTextFormat(*.rtf)", "C:\Documents and
Settings\Judith\Desktop\test1\'" & [fileLoc] & "'.rtf", False
Loop

rst.Close
Set rst = Nothing
qdf.SQL = BaseSQL
Set qdf = Nothing
Set dbs = Nothing

End Sub

I get an error on
Set rst = qdf.OpenRecordset()
Too few parameters expected. Any suggestions please
Click to expand...
Click to expand...
 
That was going to be my next question... :-)

Glad it's working for you!

--

Ken Snell
<MS ACCESS MVP>

judith said:
I think I have found my mistake from your great mvps.org/access site. The
query had a criteria based on an open form field. I now understand that it
must be a straight query so I will test for the criteria in the Do Loop.
Thanks very much for your help

Ken Snell said:
Does the query name actually contain [ and ] characters? You do not need
those characters to delimit the name of a query in this step when the query
name has blank spaces in the name. Try this:

Set qdf = dbs.QueryDefs("mailingReportBase")



--

Ken Snell
<MS ACCESS MVP>


judith said:
I am trying to loop through records in a query and save a report to
Click to expand...
word
for
each record. Steve Schapel kindly got me started with some code but I am a
bit stuck

So far
Private Sub Command1_Click()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("[mailingReportBase]")

Set rst = qdf.OpenRecordset()

BaseSQL = qdf.SQL

Do Until rst.EOF
qdf.SQL = Left(BaseSQL, Len(BaseSQL) - 3) & " WHERE [companyId] =" &
rst![companyId]
DoCmd.OutputTo acReport, DLookup("[name]", "templates", _
"[templeId]=" &
Click to expand...
[Forms]![systemForm]![mailContractForm].[Form]![letterFormBase].[Form]![temp
late]), _
"RichTextFormat(*.rtf)", "C:\Documents and
Settings\Judith\Desktop\test1\'" & [fileLoc] & "'.rtf", False
Loop

rst.Close
Set rst = Nothing
qdf.SQL = BaseSQL
Set qdf = Nothing
Set dbs = Nothing

End Sub

I get an error on
Set rst = qdf.OpenRecordset()
Too few parameters expected. Any suggestions please
Click to expand...
Click to expand...
Click to expand...
 
Back
Top