Stephen Lebans modReportToPDF

[CD Tom]

I found Stephen Lebans modReportToPDF and have been trying to get it to work in my application. I've downloaded his database and when I run his test it works fine. I then added it to my application and it works up to the point where it creates the snp file.then the app doesn't continue. If anybody has the modReportToPDF where it stops is at the point where he has the 'Export the selected Report to SnapShot Format.
Docmd.output to acoutputreport, RptName, "SnapshotFormat (*.snp)" _
strpathandfilen ame
'make sure the process has time to complete
DoEvents
My app never gets to the DoEvents and when I look in the directory for the snp file it isn't there.
I've checked his references and we are the same. I do find his snp file in the default directory but not mine.
It's like my application doesn't output snp files.
I'd sure like to use this so If someone has any ideas let me know.
Thanks for all your help.
Tom

[DonRayner]

I don't use it myself but here are some things that you could check.

1. Did you copy the modules from the example database over to your application?
2. Did you copy the included dll files over to your \system32 folder?
3. Looks like he has set a reference to Microsoft DAO 3.6 Object Library, did you do that as well?

Don

[CD Tom]

I did all those but I think I have another problem. Some of the reports I use I change the recordsource using the openargs: I don't know if there is anyway to accomplish this, unless you can have some ideas on the subject. I was able to get it to work on a different report the only difference I can see between the two is the openargs: but still I should get a report with the original record source. Any other ideas would be greatly appreciated.

Tom

[CD Tom]

Changing Record Source in a report then save it using code

Here's an interesting problem, I have a report that depending on the selection the user makes the record source changes, this is working by using the openargs:= command in the docmd.openrepor t command. But I want to send that report to a module that will create a PDF, right now I'm sending it to PrimoPDF but the problem is that there is no place to change the record source in the docmd.outputto. So what I was thinking was is there a way using code to edit the report and change the record source then save the report with the new record source. I don't want to create 11 different reports one for each record source. I do this in many different reports and I'd end up with hundreds of reports all the same except for the record source.
Any help would be appreciated.
Tom

[DonRayner]

Tom, can you post back with the code for the report that is giving you the problem? I'll take a look at it and see what I can come up with.

Don

[DonRayner]

Tom, you could try basing your reports on a query and have your VBA code edit the query before running the report.

Dim db As Database
Dim qry As QueryDef
Set db = CurrentDb
Set qry = db.QueryDefs("YourQueryName")
qry.SQL = "SELECT * FROM YourTableName Where.............;"

I'm not on a machine with Access right now so it's just air code above, check it out before using it.

Don

[ADezii]

Here's an interesting problem, I have a report that depending on the selection the user makes the record source changes, this is working by using the openargs:= command in the docmd.openrepor t command. But I want to send that report to a module that will create a PDF, right now I'm sending it to PrimoPDF but the problem is that there is no place to change the record source in the docmd.outputto. So what I was thinking was is there a way using code to edit the report and change the record source then save the report with the new record source. I don't want to create 11 different reports one for each record source. I do this in many different reports and I'd end up with hundreds of reports all the same except for the record source.
Any help would be appreciated.
Tom

Try:

'Open the Report in Design View, Hidden Window Mode (Access 2002+)
DoCmd.OpenReport "<Report Name>", acViewDesign, , , acHidden

'Programmatically change the Record Source for the report
Reports![<Report Name>].RecordSource = "Select * From Yadda, Yadda, Yadda"

'Close the Hidden/Design View Report and Save changes made
DoCmd.Close acReport, "<Report Name>", acSaveYes

'Re-Open the Report in Preview Mode
DoCmd.OpenReport "<Report Name>", acViewPreview

[Dan2kx]

I have a similar question regarding making changes in design view, can this be done within an MDE?

[ADezii]

I have a similar question regarding making changes in design view, can this be done within an MDE?

By its very nature, Design Changes cannot be made to a Report within a .mde Database. I would think that any programmatic attempt to alter the Design of a Report would also fail, but it should be easy enough to find out.

[CD Tom]

I like the idea about building the query with code but now I have a question about doing that. When you have more than one table in the query how do you write the code? This is pretty new to me, but once I learn I usually don't forget. The idea about doing the report in design mode probably wouldn't work as I have the app setup in a .mde after I get it working.
Tom

[NeoPa]

As the DoCmd.OpenRepor t() procedure has a parameter for opening in Design mode (View:=acViewDe sign), I guess this is quite possible to manage. I've not played with this as I see it as an inherently flawed concept (Consider any multi-user and continuation effects).

If you feel you must proceed anyway, I strongly suggest you make a copy of the object for temporary use which you should delete when finished with. If you can manage to ensure the object name somehow reflects something unique about the current session to avoid any potential clashes, then all the better.

[NeoPa]

The idea about doing the report in design mode probably wouldn't work as I have the app setup in a .mde after I get it working.
Tom

Another reason for avoiding design changes by code is that it is not supported in all environments (MDE is one such of course).

It may be worth seeing if changing a QueryDef is possible in that situation. If so, then I think you may have stumbled across a possible solution. I'd still bear the multi-user and continuation effects in mind though.

[CD Tom]

I think designing the query in code is probably the best option but I don't know how to do this with multiple tables. I've posted a reply earlier hoping someone could show me how this is done.

[NeoPa]

Well Tom, I guess you have an idea of the sort of thing you want. Why don't you design something we can use as an example (it will also tell us, probably better than you can at this stage, what sort of thing you're after). When designed, take the SQL from it and Copy / Paste it into a post in here (Not forgetting the CODE tags of course).

[ADezii]

I think designing the query in code is probably the best option but I don't know how to do this with multiple tables. I've posted a reply earlier hoping someone could show me how this is done.

Once you master the SQL for each Report that you may wish to execute, you can store the SQL String internally in a Table with a Unique and Descriptive identifier. It would not be a simple matter to retrieve the appropriate SQL String from the Reference Table.