Combining Multiple Reports in PDF From Access
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Combining Multiple Reports in PDF From Access
OK, sounds good. I appreciate the help!
Leesha
Leesha
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combining Multiple Reports in PDF From Access
I'd like to have some additional information:
What is the name of the field that identifies the store? Is it named WalmartNumber? And is this a number field, as the name implies?
Thanks in advance!
What is the name of the field that identifies the store? Is it named WalmartNumber? And is this a number field, as the name implies?
Thanks in advance!
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Combining Multiple Reports in PDF From Access
Hi,
Yes it is a number field set to double since it may go to 1 decimal place. And the name in each of the reports is [WalmartNumber]. The info for the stores that need to print is in tblImportTemp. The field in the table is called [Walmart] and is also a number field set to double. I can change it to [walmartnumber] if I need to but prefer not to if possible because this table is used for multiple other reports all of which are formated using the name [walmart].
Thanks!
Yes it is a number field set to double since it may go to 1 decimal place. And the name in each of the reports is [WalmartNumber]. The info for the stores that need to print is in tblImportTemp. The field in the table is called [Walmart] and is also a number field set to double. I can change it to [walmartnumber] if I need to but prefer not to if possible because this table is used for multiple other reports all of which are formated using the name [walmart].
Thanks!
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combining Multiple Reports in PDF From Access
Here is some sample code:
You could insert this into the On Click event procedure of a command button, for instance.
Code: Select all
Dim strSQL As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
strSQL = "SELECT DISTINCT Walmart FROM tblImportTemp"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)
On Error GoTo ErrHandler
Do While Not rst.EOF
DoCmd.OpenReport ReportName:="rptInvoiceCSVAllImport", _
WhereCondition:="WalmartNumber=" & rst!Walmart
DoCmd.OpenReport ReportName:="rptSalesAdjustmentReportAutoImport2", _
WhereCondition:="WalmartNumber=" & rst!Walmart
DoCmd.OpenReport ReportName:="rptRentCalculatorReportAutoImport2", _
WhereCondition:="WalmartNumber=" & rst!Walmart
rst.MoveNext
Loop
ExitHandler:
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Sub
ErrHandler:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End If
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Combining Multiple Reports in PDF From Access
Hi Hans,
This worked perfectly!! Thanks so much. I can't wait for the end user to test it out.
Leesha
This worked perfectly!! Thanks so much. I can't wait for the end user to test it out.
Leesha
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Combining Multiple Reports in PDF From Access
Hi Hans,
I'm uploading a stripped down version of the DB. There are not tables etc. I don't need code changed/tested. I need to know why I can't make changes to the report and form that are part of the DB. I believe its due to the module "modcollatereports" but don't know why. I can make the change but when I go to close the form or report and am prompted to save and say yes, the form / report won't close. If I save I don't get an error and then if I go to close the DB I get prompted to say again and the same thing happens - the report or form won't close. I need to make changes to both.
Thanks!
Leesha
I'm uploading a stripped down version of the DB. There are not tables etc. I don't need code changed/tested. I need to know why I can't make changes to the report and form that are part of the DB. I believe its due to the module "modcollatereports" but don't know why. I can make the change but when I go to close the form or report and am prompted to save and say yes, the form / report won't close. If I save I don't get an error and then if I go to close the DB I get prompted to say again and the same thing happens - the report or form won't close. I need to make changes to both.
Thanks!
Leesha
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combining Multiple Reports in PDF From Access
Both the form and the report appear to be corrupt - their Has Module property is set to Yes, but I can't see a module for either in the Visual Basic Editor. When I open the form or report in design view and click View Code, I get an empty module. I can then save and close it, but as a consequence all existing code - if any - is lost.
Do you have a recent backup with a non-corrupt version of the form and report?
Do you have a recent backup with a non-corrupt version of the form and report?
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combining Multiple Reports in PDF From Access
Oh, and I cannot import the form or report into a blank database; this could be caused by the missing subforms/subreports, however.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1495
- Joined: 05 Feb 2010, 22:25
Re: Combining Multiple Reports in PDF From Access
I have other versions but the issue is the same on them. Also, when I tried importing to a blank database before sending to you I would get an error that the name conflicts with an existing module, Project or object library.
Leesha
Leesha
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combining Multiple Reports in PDF From Access
I'm afraid that you'll have to create the form and report from scratch again, sorry!
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 495
- Joined: 13 Sep 2013, 07:56
Re: Combining Multiple Reports in PDF From Access
Just an alternative you could try in order to avoid re-creating from scratch.HansV wrote:I'm afraid that you'll have to create the form and report from scratch again, sorry!
Export the Form/Report etc as text.
Reimport the text back into Access.
I have code if you need examples.
That will drop the serial version number that Access keeps incrementing with a Form, etc which has an upper ceiling )I never got to the bottom of why it does this, but once it hits that value it won't allow any more edits, etc.
Regards, Ben
"Science is the belief in the ignorance of the experts."
- Richard Feynman
"Science is the belief in the ignorance of the experts."
- Richard Feynman
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combining Multiple Reports in PDF From Access
Unfortunately, that won't work. The form and report are too corrupted to be exported to text. The line
Application.SaveAsText acReport, "rptInvoiceAllCSVImport2", "H:\Access\rptInvoiceAllCSVImport2.txt"
results in
(H:\Access is an existing folder on my laptop)
The same happens for the form.
Application.SaveAsText acReport, "rptInvoiceAllCSVImport2", "H:\Access\rptInvoiceAllCSVImport2.txt"
results in
(H:\Access is an existing folder on my laptop)
The same happens for the form.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 495
- Joined: 13 Sep 2013, 07:56
Re: Combining Multiple Reports in PDF From Access
There must be a working form somewhere back in earlier editions, no?
Regards, Ben
"Science is the belief in the ignorance of the experts."
- Richard Feynman
"Science is the belief in the ignorance of the experts."
- Richard Feynman
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combining Multiple Reports in PDF From Access
I asked higher up in this thread "Do you have a recent backup with a non-corrupt version of the form and report?"
Leesha replied "I have other versions but the issue is the same on them."
I don't know how far back Leesha's backup copies go...
Leesha replied "I have other versions but the issue is the same on them."
I don't know how far back Leesha's backup copies go...
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 495
- Joined: 13 Sep 2013, 07:56
Re: Combining Multiple Reports in PDF From Access
ok, just a thought.
I run a weekly backup of exporting all objects as text objects (within a directory showing date of export). This has saved me in the past and well worth implementing. A simple loop through each object container does the trick. There is also a corresponding re-import object to match.
I run a weekly backup of exporting all objects as text objects (within a directory showing date of export). This has saved me in the past and well worth implementing. A simple loop through each object container does the trick. There is also a corresponding re-import object to match.
Regards, Ben
"Science is the belief in the ignorance of the experts."
- Richard Feynman
"Science is the belief in the ignorance of the experts."
- Richard Feynman