Print report cover with changing footer

[HomerJ]

Greetings and thanks for taking the time to look at my post.

The challenge I face is this. I prepare deposit transmittals for two separate locations. I currently do this in Excel but have been working on converting the process to Access. After entering the necessary data, I print a certain number of copies that have different footers on the cover/summary page. One location has 5 different footers, the other has 4. Currently, I have some code that allows me to do this in Excel. It's not pretty, but it does the job. Here is the code from Excel.

Sub PrintBase()
'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

ActiveWindow.SelectedSheets.PrintOut Copies:=1
    With ActiveSheet.PageSetup
        .CenterFooter = "Treasurer - Original (w/receipts)"
    End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1
    With ActiveSheet.PageSetup
        .CenterFooter = "Treasurer 1st Copy"
    End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1
    With ActiveSheet.PageSetup
        .CenterFooter = "Treasurer 2nd Copy"
    End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1
    With ActiveSheet.PageSetup
        .CenterFooter = "Accounting Copy (w/receipts)"
    End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1

    If Sheets("CP Cover").Range("I16") > 0 Then
    With ActiveSheet.PageSetup
        .CenterFooter = "Health Copy"
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
    End If
    With ActiveSheet.PageSetup
        .CenterFooter = ""
    End With
Sheets("Data").Select
    Call FilterDate
    Sheets("CP Cover").Select
    
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

MsgBox ("Your Coupeville deposit has been printed")

End Sub

I have a variation on this for the second location.

What I hope to do is to achive the same results in Access. In the case of the above example, print 5 cover sheets, each with a different footer, along with a detail sheet for 4 of those. I'm thinking that I could achive this with a for...next or do until loop that references a query which would have the appropriate footer names for the respective location. I have a table that has all the footer text.

I have tried using recordsets and SQL, but I'm new to that and the closest I have gotten is code that would print the appropriate number of copies for each listing in the query. However, it did not change the footer text. Unfortunately, I had a system crash without having saved that version of code so I am back at the starting line.

Can someone tell me if I'm on the right track and possibly give me a pointer in the right direction to solve this.

By the way, the report cover sheet is unbound. If I try setting the query for the footer names as the source, it duplicates the subreport that I have on the cover sheet.

Also, I am using Access 97.

Thanks for your help.

 

[HomerJ]

Well I finally had a "D'oh!" moment last night and figured out a way to make this work, but not with code. I set the query with the footers as the record source for the form and then set the Force New Page property of the detail section to "After section". I figured out that I was seeing multiple copies of my subreport because that property was set to none.

Of course, I would still like to know for my own edumacation how to do the above with code if someone can understand the situation the way I described it and would like to enlighten me.