Creating A Recordset To Loop Through

[LaCour]

How can I get DoCmd.OpenQuery to return a recordset that I can loop through?
EXAMPLE:
Call DoCmd.OpenQuery("qryGetProjsReviewOrderby")
-->will open the query.
Set recTest = DoCmd.OpenQuery("qryGetProjsReviewOrderby")
-->will highlight the 'OpenQuery' and say: expected function or variable.


If DoCmd.OpenQuery is not intented to return recordsets, what should I do to have a recordset returned that I can loop through.

Thank you

 

[RoyVidar]

Assuming DAO:

[tt]dim rs as dao.recordset
set rs = currentdb.openrecordset("qryGetProjsReviewOrderby")
if not rs.bof and not re.eof then
rs.movefirst
do while not rs.eof
debug.print rs(0).value, rs(1).value
rs.movenext
loop
end if
rs.close
set rs=nothing[/tt]

- will loop thru the query, print the first two fields values per each record to the immidiate pane (ctrl+g) - have a reference to DAO (VBE - Tools | References, Microsoft DAO 3.# Object Library)

BTW - Welcome to Tek-Tips!

Here's a faq on how to get the most out of the membership faq181-2886.

Roy-Vidar

 

[MichaelRed]

Beyond the actual mechanics -nicely supplied by Roy-Vidar]/color], the concept of opening a set of records to 'loop through' is somnewhat curious. Relational databases are generally designed around and used for operations on 'SETS' of records. With the various clauses available (order by, group by, where, and aggregate operations, it is usually possible to accomplish the operation of 'whatever' (the loop operation) via a query and avoid the tedium of the loop altogether. Perhaps, if the operation required were stated more completly, Roy or another expert would at least give some hints as to how you could use a query to accomplish the necessary task(s) without the necessity of code.





MichaelRed
mlred@verizon.net

 

[LaCour]

Roy-Vidar - thank you for the help ... your suggestion returns the recordset - just what I was looking for.

MichaelRed - thanks for your comments. Let me explain what I am trying to do and you can let me know if there is a better approach.

I have a report that is using every row of a table (only 64 at this point). At 2 pages / report, the document created is 128 pages. My goal is to create a 1 rtf doc for each record. My initial approach was to find a way to break up the word doc after it had been created or some way to create multiple docs. I've been using "DoCmd.OutputTo" and it appears that it will only create 1 doc.

That being said, my current approach is to change the report so it only generates 1 doc / record based on the primary key of the table. I am interested in getting the record set because I will loop through it and with each iteration, I will update the query that the report is based on and then execute the report.

With the help on the recordset, I now can loop through it and I will get a new pk for each loop. I need to figure out how to update the query / update the report the query is based on.

Let me know if there is a better way ... if not advice on how to finish this would be apreciated.

Thank You
Blair

 

[JonFer]

Is the report like a form you are filling out?

Something like this should work. If you have a checkbox in the underlying table, you could add a criteria for the report's query that the checkbox be True. Then as you run through each record, set the checkbox to True, export the report using a file name based on data from the record, set the checkbox back to False, and then move to the next record.

 

[MichaelRed]

Seem like 'one of those' issues. I sometimes call it 'TAINT' (short hand for it '"anit this and it anit that ... ")

You can open a report with open args, basing the report on a query having all of the info fields and the PK.(obviously) the output needs a new/seperate file name for each instantation, else it will just overwrite it. You do still need the recordset and to loop through it, but it only needs the to have the PK and there is no need to mark records, just loop from start to end, using move next for each itteration.

The ubiquitous {F1} (aka help) for some details on how to use the openargs with a report. If this is not clear, repost.



MichaelRed
mlred@verizon.net

 

[LaCour]

Jonfer and MicchaelRed

Thanks for the help. My thought ended up working out ... in my VBA, I have the SQL from my report as a string; everything up to the where. I just complete the SQL with each loop and update the query which the report is using. Here are some lines

Set qryTemp = dbsCurrent.QueryDefs("qryGetProjsReviewTemp")
...
update sReportSQLtemp
...
'Update Query
qryTemp.Sql = sReportSQLtemp
...
'Create report; save off
Call DoCmd.OutputTo(acOutputReport, "rptProjectsToReview", _
acFormatRTF, sPath & sFileName & ".rtf")

This will be very helpful in the future.

Thanks again for help