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)" _
strpathandfilename
'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]

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.openreport 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]

@CD 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]

@Dan2kx
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.OpenReport() procedure has a parameter for opening in Design mode (View:=acViewDesign), 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]

@CD 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]

@CD Tom
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.

[ADezii]

@NeoPa
Hello NeoPa, I honestly do not see a problem with dynamically modifying the Record Source of Objects in Design Mode then saving them. This, obviously cannot be done within the context of a .mde File which was not mentioned until Post #6, and as far as what I can see, there is no indication that the OP is working within a Multi-User environment.

[Denburt]

I have found this to be a very handy little gem. I think I can see how you might be having an issue with it, I did have to do a bit of code changing and such myself. One thing you might do is instead of changing the recordsource for the report use the same query name just rebuild the query using the QueryDef Object before trying to output the report.

[Denburt]

Tom it appears you have two threads with the same issue so I will be merging these. Thanks

[CD Tom]

Sorry about that I'm still getting the hang of using this, I'd like to say this has been a great help. I really appreciate all the great advise, keep up the good work and Thanks a million.

[Denburt]

No problem Tom it appears that you have several people helping to guide you good luck.

[CD Tom]

I've figured out how to do the QueryDef with all the inner joins and = to's but I do have one problem (of course) I want one of the columns in the query to all have the same name in the original query I just use the Title:master.grouptitle, but when I try this in the code build query it gives me an error. The reason for this is because grouptitle might be maintitle in another query and in the report I want to reference it as just Title. Who do I do this. Thanks

[Dan2kx]

Is there an event (Before update etc) that would be able to change the recordsource etc (and work in a MDE, (Not the OP's concern, my own))

Dan

[CD Tom]

I've got the querydef working and I thing this is what I want to do. I figured out the title thing (which was quite simple after I found where to look) and now that it is working like I want the modreportotPDF is also working. I want to thank all those who help with great suggestions. I really appreciate all you expert advise. I'll be back!!!!! when I have another problem.
Thanks Again.

[NeoPa]

@ADezii
ADezii, It seems to me that designing a project that can only work under a restrictive set of circumstance (EG. single user only) is a recipe for problems later on in the project. Who wants to scrap and start again when the spec changes?

Furthermore, it is much harder to work with objects when you can't predict what you'll find due to the last user leaving things in an unpredictable state. Having a fair understanding of how you work (very VBA oriented) I'm surprised you haven't fallen over such things yourself. I have routines which take SQL out of existing queries and modify them intelligently. Sometimes they need to know exactly what to expect from the original query (matching and replacing precise existing strings for instance). Having this undefined, or dependent on who and when the job was last run, simply narrows down your options. Why plan to restrict your options later, whatever your current objectives are? Always plan to give yourself as much flexibility and scope as you can would be my advice.

PS. Please don't let this post distract from Tom's latest request.

[ADezii]

@NeoPa
All your points are valid NeoPa, but I personally work with many Databases that are Single-User (either myself, or Single-User/Departmental specific) that I know will never be migrated to Multi-User for various reasons. Under these assertions, I see no reason why the previous logic is not viable. BTW, always a pleasure conversing with you.

[DonRayner]

@CD Tom
Glad we could help Tom.

[CD Tom]

Now that I have figured out how to use the QueryDef I need to continue lines of code on the next line because they are to long. I thought that the _ was the continuation but that doesn't seen to work. What do I need to do.

[Dan2kx]

you need to close " and use & eg...

"Really long SQL goes here " & _
"And then drops off the page onto the next line"

you need to have a space in there before the quote (if the SQL requires it)

[CD Tom]

That worked Access sure has some funny rules. Thanks

[NeoPa]

@ADezii
I suppose if the assertion is clearly stated ADezii, it can do no harm.
@ADezii
PS. I learn a lot from you, so it only seems fair that I try to pass something back ;)

[NeoPa]

@CD Tom
An interesting point Tom, but if you think about it, without that rule, including such special characters in any of your data strings would require special handling (like an escape character first as is usual in some other situations). There has to be a way for the compiler to know whether to treat a character as part of a string or part of the syntax.