Combining Multiple Reports in PDF From Access

[Leesha]

OK, sounds good. I appreciate the help!
Leesha

[HansV]

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!

[Leesha]

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!

[HansV]

Here is some sample code:

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

You could insert this into the On Click event procedure of a command button, for instance.

[Leesha]

Hi Hans,
This worked perfectly!! Thanks so much. I can't wait for the end user to test it out.

Leesha

[Leesha]

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

[HansV]

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?

[HansV]

Oh, and I cannot import the form or report into a blank database; this could be caused by the missing subforms/subreports, however.

[Leesha]

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

[HansV]

I'm afraid that you'll have to create the form and report from scratch again, sorry!

[BenCasey]

I'm afraid that you'll have to create the form and report from scratch again, sorry!

Just an alternative you could try in order to avoid re-creating from scratch.
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.

[HansV]

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

S301.png

(H:\Access is an existing folder on my laptop)

The same happens for the form.

[BenCasey]

There must be a working form somewhere back in earlier editions, no?

[HansV]

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

[BenCasey]

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.