Stephen Lebans modReportToPDF | Member | | Join Date: Feb 2009 Location: Portland, OR
Posts: 64
| | |
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
|  | Expert | | Join Date: Sep 2008 Location: Canada
Posts: 494
| | | re: Stephen Lebans modReportToPDF
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
| | Member | | Join Date: Feb 2009 Location: Portland, OR
Posts: 64
| | | re: Stephen Lebans modReportToPDF
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
| | Member | | Join Date: Feb 2009 Location: Portland, OR
Posts: 64
| | | re: Stephen Lebans modReportToPDF
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
| | Expert | | Join Date: Sep 2008 Location: Canada
Posts: 494
| | | re: Stephen Lebans modReportToPDF
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
| | Expert | | Join Date: Sep 2008 Location: Canada
Posts: 494
| | | re: Stephen Lebans modReportToPDF
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
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,216
| | | re: Stephen Lebans modReportToPDF
Quote:
Originally Posted by 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 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
| | Needs Regular Fix | | Join Date: Oct 2007 Location: Hull,UK
Posts: 284
| | | re: Stephen Lebans modReportToPDF
I have a similar question regarding making changes in design view, can this be done within an MDE?
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,216
| | | re: Stephen Lebans modReportToPDF
Quote:
Originally Posted by Dan2kx 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.
| | Member | | Join Date: Feb 2009 Location: Portland, OR
Posts: 64
| | | re: Stephen Lebans modReportToPDF
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
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,729
| | | re: Stephen Lebans modReportToPDF
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.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,729
| | | re: Stephen Lebans modReportToPDF
Quote:
Originally Posted by CD Tom 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.
| | Member | | Join Date: Feb 2009 Location: Portland, OR
Posts: 64
| | | re: Stephen Lebans modReportToPDF
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.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,729
| | | re: Stephen Lebans modReportToPDF
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).
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,216
| | | re: Stephen Lebans modReportToPDF
Quote:
Originally Posted by 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. 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.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,216
| | | re: Stephen Lebans modReportToPDF
Quote:
Originally Posted by 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. 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.
|  | Moderator | | Join Date: Mar 2007 Location: Louisiana
Posts: 1,218
| | | re: Stephen Lebans modReportToPDF
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.
| | Moderator | | Join Date: Mar 2007 Location: Louisiana
Posts: 1,218
| | | re: Stephen Lebans modReportToPDF
Tom it appears you have two threads with the same issue so I will be merging these. Thanks
| | Member | | Join Date: Feb 2009 Location: Portland, OR
Posts: 64
| | | re: Stephen Lebans modReportToPDF
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.
| | Moderator | | Join Date: Mar 2007 Location: Louisiana
Posts: 1,218
| | | re: Stephen Lebans modReportToPDF
No problem Tom it appears that you have several people helping to guide you good luck.
| | Member | | Join Date: Feb 2009 Location: Portland, OR
Posts: 64
| | | re: Stephen Lebans modReportToPDF
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
| | Needs Regular Fix | | Join Date: Oct 2007 Location: Hull,UK
Posts: 284
| | | re: Stephen Lebans modReportToPDF
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
| | Member | | Join Date: Feb 2009 Location: Portland, OR
Posts: 64
| | | re: Stephen Lebans modReportToPDF
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.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,729
| | | re: Stephen Lebans modReportToPDF
Quote:
Originally Posted by ADezii 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. 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.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,216
| | | re: Stephen Lebans modReportToPDF
Quote:
Originally Posted by NeoPa 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. 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.
| | Expert | | Join Date: Sep 2008 Location: Canada
Posts: 494
| | | re: Stephen Lebans modReportToPDF
Quote:
Originally Posted by 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. Glad we could help Tom.
| | Member | | Join Date: Feb 2009 Location: Portland, OR
Posts: 64
| | | re: Stephen Lebans modReportToPDF
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.
| | Needs Regular Fix | | Join Date: Oct 2007 Location: Hull,UK
Posts: 284
| | | re: Stephen Lebans modReportToPDF
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)
| | Member | | Join Date: Feb 2009 Location: Portland, OR
Posts: 64
| | | re: Stephen Lebans modReportToPDF
That worked Access sure has some funny rules. Thanks
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,729
| | | re: Stephen Lebans modReportToPDF
Quote:
Originally Posted by ADezii 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. I suppose if the assertion is clearly stated ADezii, it can do no harm. Quote:
Originally Posted by ADezii BTW, always a pleasure conversing with you. PS. I learn a lot from you, so it only seems fair that I try to pass something back ;)
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,729
| | | re: Stephen Lebans modReportToPDF
Quote:
Originally Posted by CD Tom That worked Access sure has some funny rules. Thanks 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.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,449 network members.
|
