Custom Report Footer

[Eljefegeneo]

I current have three different reports that are the same except for the footers. That means that each time I have a change to the report, I have to remember to make the change on all three copies. Is there a way to add (1) some custom text such as "Client Copy", "Please Sign and Return", "Our Copy" plus one or two data fields from the report query in the page footer (the data fields would be different for each copy)?
Basically, what I would like to do is have just one report, but print three copies of it, with each of the three copies having different footers as described above. Is this possible?
Using Access 2010.
Thanks.

 

[CJ_London]

If you are using docmd.openreport then do the following:

docmd.OpenReport "myReport",,,,,"1"
docmd.OpenReport "myReport",,,,,"2"
docmd.OpenReport "myReport",,,,,"3"

The text is completing the openargs parameter of OpenReport

Then in your report on open event put the following

Select Case Me.OpenArgs
    Case "1"
        txtBox="Client Copy"
        txtFld1=[qryField1]
        etc
    Case "2"
        txtBox="Please Sign and Return"
        txtFld1=[qryField2]
        etc
    Case "3"
        txtBox="Our Copy"
        txtFld1=[qryField3]
        etc
End Select

 

[Eljefegeneo]

Thank you. I knew someone would know how to do it. Again, I do appreciate you answering my post. I worked on it for several hours before posting, sometimes I can figure these things out, sometimes not.

 

[Eljefegeneo]

I did as you said, but it didn't do anything except print three copies of the same report without anything in the textbox. I added an unbound control to the form, gave it the name of TextBox1, and then added the OpenArgs as you said. It didn't print what I had in each "Case" statement. I then thought maybe it should be a bound control with nothing in it, but that didn't work either. Haven't tried the "txtField1 = [qryField] yet.

Private Sub Command545_Click()
DoCmd.OpenReport "rptContractAAA", , , , , "1"
DoCmd.OpenReport "rptContractAAA", , , , , "2"
DoCmd.OpenReport "rptContractAAA", , , , , "3"
End Sub

Select Case Me.OpenArgs
Case "1"
txtBox1 = "Client Copy"
'txtFld1 = [qryField1]
'etc
Case "2"
txtBox1 = "Please Sign and Return"
'txtFld1 = [qryField2]
'etc
Case "3"
txtBox1 = "Our Copy"
'txtFld1 = [qryField3]
'etc
End Select
End Sub

 

[JHB]

... I added an unbound control to the form, gave it the name of TextBox1, and then added the OpenArgs as you said. It didn't print what I had in each "Case" statement. ...

You mean Report, do you not?
The name is not "TextBox1" but "txtBox1".

 

[Mihail]

I think that txtBox1 is not misspelled in the form/report because the compiler not raise an error.
It is misspelled only in the post.

From my experience, the OpenArgs are unexpectedly lost. Can't understand why, but is a fact. (I use A2007).
So, the first thing I do, in the Open event for a Form, is to save this OpenArgs into a variable at Form module level:

Option Explicit
Dim SavedOpenArgs

Private Sub Form_Open(Cancel As Integer)
     SavedOpenArgs = Me.OpenArgs
  .....................................
End Sub

Then I use this variable in my code.

Select Case SavedOpenArgs
   ......................
End Select

 

[JHB]

I think that txtBox1 is not misspelled in the form/report because the compiler not raise an error.
It is misspelled only in the post.

You do not know, do you?
If the "Option Explicit" isn't set, no error would raise.
From the codeline below, you can't see if it is a variable or an control the value is assign, (it is some kind of bad programming, and can take long time to debug, use Me in front, if it is a control - Me.txtBox.)!

txtBox="Client Copy"

 

[Mihail]

@JHB
Not 100% sure but... 99.99 because Eljefegeneo wrote:
"I did as you said, but it didn't do anything except print three copies of the same report without anything in the textbox."

With or With not Option Explicit, at run time an error is raised. Am I wrong ?

The 0.01 % doubt is coming because no one of us can be sure that the statements inside the Select Case have been executed.

 

[Eljefegeneo]

Yes, Option Explicit was added and a run time error did occur. I am going to try to put this in a simple DB, with one table, one form, one query and one report and try some of the above. I will let you know if I can get it to work.

 

[Eljefegeneo]

I have attached a sample DB with two different reports. FormA has a command button to open ReportA, which does give a run time error saying "you cannot assign a value to this object". This is the code suggested by CJ London.
FormB has a command button that opens ReportB, using the suggestions by Mihail. I really did not know how to implement his suggestions, and made a feeble attempt at it. The report will run, but there is nothing in the footer.

 

[JHB]

@JHB
With or With not Option Explicit, at run time an error is raised. Am I wrong ?

Sorry but, Yes you are wrong, (you can try it you self)!

 

[JHB]

You have to use "On Load" event instead of "On Open" event.
I've attached the database - I only change report A.
I don't know why you are setting "SelectionX" from code, you don't need that.

 

[Eljefegeneo]

Thank you for taking a look at my problem. I think we are almost there. With the command button on FormA that says "OpenReportA", I can only open one of the reports in acViewPreview. I tried it in acPrint, but only one report prints.
Then you ask the question why I need to set the selection X. It is only really needed for the copy "3". It contains information that I do not want the client to see. I make a command button Open ReportA1, and a ReportA1. The code on load (please note that the first reply said event on open), will explain what I want. That is, the first two copies are the same except for the "Client Copy" and "Please sign and return". The third copy, "Our copy", has three extra fields that are for our eyes only.
I tried to fix it myself, but am as lost as ever.
Thanks.

 

[CJ_London]

I stand corrected about which event to use - the load event is the appropriate one in this instance.

I think the other issue is that the report needs to be closed before 'sending' the next one. So for command54_click use this code

DoCmd.OpenReport "ReportA", acViewPreview, , "[ID] = " & Me![ID], , "1"
DoCmd.Close acReport, "ReportA"
DoCmd.OpenReport "ReportA", acViewPreview, , "[ID] = " & Me![ID], , "2"
DoCmd.Close acReport, "ReportA"
DoCmd.OpenReport "ReportA", acViewPreview, , "[ID] = " & Me![ID], , "3"
DoCmd.Close acReport, "ReportA"

and use the debugger to step through the code - to use the debugger, place the cursor on the first line of the code above and hit the F9 key - a maroon dot will appear to the left. Then when you click the print all 3 button the code will stop on this line highlighted in yellow. Hit the F8 key to step through the code - it will go to the report load event, step through the code and then display the report, as you continue to hit F* it will retunr to the orginal code, close the report and move on to the next line when it will open the reort again.

With regards your selectionA,B C, since these are brought through in your report recordsource you can simply have these as bound controls and set their visible property to false or true as required

So rename your txtfield1,2,3 as SelectionA,B C and set their control sources to SelectionA,B, C so they become bound controls.

Then in the appropriate place in the case statement put

SelectionA.Visible=False (for openargs =1 or 2) and
SelectionA.Visible=True (for openargs=3)

 

[Eljefegeneo]

Will try it. What I thought about half way through this problem was that I couldn't open a report and then open it again. I will try this after cooking my 4th of July dinner on the BBQ - spare ribs - and let you know how it all comes out.

 

[Mihail]

What surprise for me !!!!!!!!!!!!!
Forgive me, JHB . Indeed.
Without Option Explicit no error here.
Thank you again.

 

[JHB]

I think the other issue is that the report needs to be closed before 'sending' the next one. So for command54_click use this code

DoCmd.OpenReport "ReportA", acViewPreview, , "[ID] = " & Me![ID], , "1"
DoCmd.Close acReport, "ReportA"
DoCmd.OpenReport "ReportA", acViewPreview, , "[ID] = " & Me![ID], , "2"
DoCmd.Close acReport, "ReportA"
DoCmd.OpenReport "ReportA", acViewPreview, , "[ID] = " & Me![ID], , "3"
DoCmd.Close acReport, "ReportA"

Sorry - but it is still not correct, the code will produce 3 reports, with the same name/ID.
Eljefegeneo - with the setup you've in the sample database, a form which have a Recordsource, you can use a Recordset and RecordsetClone and a loop, to get the different ID's. The database is attached with the change.

  Dim rst As Recordset, x As Integer

  Set rst = Me.RecordsetClone
  If Not rst.EOF Or Not rst.BOF Then
    rst.MoveFirst
    Do
      x = x + 1
      DoCmd.OpenReport "ReportA", acViewPreview, , "[ID] = " & rst![ID], , CStr(x)
      DoCmd.Close acReport, "ReportA"
      rst.MoveNext
    Loop Until rst.EOF
  End If

 

[JHB]

What surprise for me !!!!!!!!!!!!!
Forgive me, JHB . Indeed.
Without Option Explicit no error here.
Thank you again.

Not for me - and you are forgiven.
Therefore always use Me in front of a control - then MS-Access knows you want to assign the value to a control, and not assign it to a variable, if no control with the name is found.
And of cause, always use the "Option Explicit", it spare you for much frustration and many sleepless nights.

 

[Mihail]

I never avoid Option Explicit. It is why I don't know (until now) what is happen at run time.
And now another question is raised: WHY ?!?!? Microsoft allow a programmer to NOT use Option Explicit ?!?!? But this question not fit in this thread so... I answer to my self: No good reason. One more time: Thank you.

 

[Eljefegeneo]

Thanks to all for the help. JHB, sometimes I do want three reports with different footers for the same ID, and other times not. Your second bit of code may be very helpful when I want to print out my monthly invoices.