Print report after populating via recordset

[PeteG]

Hi,

I have a report where I am populating text box values from an adodb recordset. The following works fine to preview the report:

Dim rptTest as Report
DoCmd.OpenReport "rptTester", acPreview
Set rptTest = Reports!rptTester
Call rptTest.Populatefields(rs)

The report opens and the fields are filled out as I have specified in the populatefields method.

However, if I change acPreview to acViewNormal (to print instead of preview), I get the error "the report name rptTester you entered is misspelled or refers to a report that isn't open or doesn't exist" when the code tries to execute the 'set' statement above. Presumably, as the report closes automatically after being printed, it's no longer in scope.

My question is, how can I open a report, call a method to populate some text boxes with a recordset, and then print it?

Thanks for any advice.

 

[dhookom]

Populating control values with code is a bit unusual particularly if you aren't doing this from code in the report.

If you really need to use code rather than binding the controls to fields in the record source then I would move the code to the On Format event of the section of the report containing the controls.

Duane
Hook'D on Access
MS Access MVP

 

[PeteG]

Thanks for your reply. The rationale behind populating the fields directly from a recordset was because I produce the recordset from another form that contains text and combo boxes that the users manipulates to build up the criteria for a sql query (the data is in Sybase). Having the resultant recordset, I thought it would be more efficient to just pass it to the object that I want to use next, be it another form to display the results, or a report to print them.

Now, it looks like it would be far easier to write the recordset to a table and then use that as the source of any form or report.

Thanks

Pete

 

[danvlas]

Bind your report directly to the sql query that produces the recordset.

Binding a report to a recordset is possible if the application is ADP not MDB. The recordset must be ADO, not DAO.

I'd like to be able to bind a report to a recordset in MDB too, to avoid unnecessary hits to the database and the risk of getting some information different from what I had 1 minute before, because of some recent update.

I experienced a funny thing once: A report was printed like below:

For intCount=1 to intNumberOfCopies
    DoCmd.OpenReport strReportName, , , "SomeFilterCriteria"
Next

From 5 copies, 2 had one set of data and 3 showed something else because some guy remembered to insert one forgotten document that fitted the report criteria and did it exactly at that moment.


Daniel Vlas
Systems Consultant

 

[dhookom]

I would use the SQL string to update the SQL property of a saved Pass-Through query to your Sybase database. This is very efficient and takes only a couple lines of DAO code.

Duane
Hook'D on Access
MS Access MVP

 

[PeteG]

Yes, I did try to do that. The problem I found (and I admit I have spent some time away from Access VBA so could well have missed a trick here) was that I could only do that if I used an ODBC connection - and I specifically wanted to avoid that. I wanted all connection details to reside within the app to make it self-contained and the only way I could see to do that was to use ADODB provider to connect to Sybase. By doing that, I came across other issues, such as being unable to use pass-thru queries and unable to use Recordset (I had to use adodb Recordset which appears to offer less flexibility). I got over those though!

Thanks for the advice, it's been very useful to get others views!

 

[dhookom]

I don't use DSNs with my SQL Server pass-through queries. The DSNless connection string is saved with the pass-through queries and linked tables.

Duane
Hook'D on Access
MS Access MVP