Force Page Footer to bottom of page 1

[PRMiller2]

I'm sure this is very simple but I'm afraid it's got me stumped.

I have a report with a report header, page header, detail and page footer. The page footer contains date/time printed, page count, and user information. For some reason, the page footer is printing directly after the last line of data from the detail section. I did verify that I'm using page footer and not report footer.

The only thing that's worked for me so far was setting the Page Footer property (in the report property sheet) to "Not with Rpt Ftr" but, of course, then the page footer doesn't print at all when there's a footer involved.

Help!

 

[dhookom]

Are you sure you are using the Print Preview mode? I have never heard of this happening. Without some type of code, the page header and footer sections will always appear at the same locations on your paper (top and bottom).

If your's doesn't, I would try create a new, similar report and see if it behaves the same.

Duane
Hook'D on Access
MS Access MVP

 

[PRMiller2]

Yes, it's very bizarre. Here's the line I'm using:

DoCmd.OpenReport "rpt_CPA", acViewPreview

It happens on all of my reports. I'm not sure what else to do here... I don't suppose a decompile would help?

 

[dhookom]

I would try set a different default printer.

Duane
Hook'D on Access
MS Access MVP

 

[PRMiller2]

Just switched the default printer to a completely different make/model, still the same behavior. Google search doesn't seem to help much, most returns are either telling people to use page footer instead of report footer, or how to disable the page footer on one page but not others.

 

[dhookom]

While I'll be ....
In all my 10+ years of participating in various Access forums and discussions, I have never heard of this issue. The only thing that came close was Access 1.1 when a report bottom margin was less than the printer could support. The bottom of the page "layered" over the page a few inches above the bottom of the page.



Duane
Hook'D on Access
MS Access MVP

 

[PRMiller2]

Yeah, it's tripping me out. You wouldn't believe the number of times I've opened several reports just to check yet again and make sure it's page footer, not report footer!

Wish I could post a portion of the database here to have members poke through it!

 

[dhookom]

You could create a harmless example and email it to me if you want. duanehookom AT gmail DOT com.

Make sure you include Tek-Tips in the subject line so I don't delete it.

Duane
Hook'D on Access
MS Access MVP

 

[PRMiller2]

On its way, thanks!

 

[PRMiller2]

Well dhookom, whatever the problem was that originally prompted me to go this route has been solved. When I set the Page Footer property in the Property Sheet to “Not with Rpt Ftr,” that solved the formatting issue but then caused, of course, the intermittent issues when there was a report footer – expected behavior. I just went through all of the forms and set that Page Footer property back to “All Pages,” and now the page footer is displaying correctly. Perhaps there was some other property I overlooked that ended up being reset when I made the secondary changes.

At any rate, it works now… and I just learned a valuable lesson: don’t strip out all the data for a sample database until you’ve tried ALL possible solutions!

 

[dhookom]

I'm a bit confused why you use code in the On Open to set the Record Source of your reports.

Duane
Hook'D on Access
MS Access MVP

 

[PRMiller2]

Well it depends on the report. For my conversion report, I dispaly the result of calculations queried from a variety of tables. I'm sure I could write the necessary query to pull them all together, but I found it easier to write it that way.

On the remaining reports (those that all begin with the name rpt_CPA), those had all originally been one report, with the recorsource being set dependant on certain criteria provided by a form. However, I ran into an issue that necessitated splitting them into individual reports. For most reports I do like to set the recordsource based on a query.

Is programmitcally setting the recordsource not an optimal way to populate the report?

 

[dhookom]

Why not set the record source property to the SQL statement you build in your report's On Open event? This seems much simpler and easier to maintain. The code doesn't have to be run in order to render the report.

Duane
Hook'D on Access
MS Access MVP

 

[PRMiller2]

In the conversion report, trying to set the me.txtListingAppointsment.Value results in an error: "You can't assign a value to this object." So I moved it to the Report_Load event and it executed fine.

Private Sub Report_Load()
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    
    Me.txtDateRange = "Date Range: " & Format([Forms]![frm_ReportCriteria]![txtStartDate], "mm/dd/yyyy") & " to " & Format([Forms]![frm_ReportCriteria]![txtEndDate], "mm/dd/yyyy")
    
    'Populate % Listing Appointments to Contracts
    strSQL = "SELECT Count(eventOccurrence.fk_EventID) AS Appointments" & _
             " FROM eventOccurrence" & _
             " WHERE (((eventOccurrence.eventDate) Between #" & g_datStartDate & "# AND #" & g_datEndDate & "#)" & _
             " AND ((eventOccurrence.fk_EventID)=23));"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    
    If rs.RecordCount > 0 Then
        Me.txtListingAppointments.Value = Nz(rs("Appointments"))
    End If
    
    Set rs = Nothing
    
    strSQL = "SELECT Count(client.intClient) AS Contracts" & _
             " FROM eventOccurrence" & _
             " LEFT JOIN client ON eventOccurrence.fk_ClientID = client.pk_ClientID" & _
             " WHERE (((eventOccurrence.eventDate) Between #" & g_datStartDate & "# AND #" & g_datEndDate & "#)" & _
             " AND ((eventOccurrence.fk_EventID)=23) AND ((client.intClient)=-1));"

    Set rs = db.OpenRecordset(strSQL)
    
    If rs.RecordCount > 0 Then
        If Nz(rs("Contracts")) < 1 Then
            Me.txtSellerContracts.Value = "0"
            Me.txtSellerContracts2.Value = "0"
            Me.txtSellerPercentage.Value = "0%"
        Else
            Me.txtSellerContracts.Value = Nz(rs("Contracts"))
            Me.txtSellerContracts2.Value = Nz(rs("Contracts"))
            Me.txtSellerPercentage.Value = Me.txtSellerContracts / Me.txtListingAppointments
        End If
    End If
    
    Set rs = Nothing
    
    'Populate % Listing Contracts to Sales
    strSQL = "SELECT Count(client.intClient) AS Sales" & _
             " FROM (eventOccurrence" & _
             " LEFT JOIN client ON eventOccurrence.fk_ClientID = client.pk_ClientID)" & _
             " LEFT JOIN reTransaction ON client.pk_ClientID = reTransaction.fk_ClientID" & _
             " WHERE (((eventOccurrence.eventDate) Between #" & g_datStartDate & "# AND #" & g_datEndDate & "#)" & _
             " AND ((eventOccurrence.fk_EventID)=23)" & _
             " AND ((client.intClient)=-1)" & _
             " AND ((reTransaction.fk_ClientID) Is Not Null));"

    Set rs = db.OpenRecordset(strSQL)
    
    If rs.RecordCount > 0 Then
        If Nz(rs("Sales")) < 1 Then
            Me.txtListingSales.Value = "0"
            Me.txtListingsSoldPercentage.Value = "0%"
        Else
            Me.txtListingSales.Value = Nz(rs("Sales"))
            Me.txtListingsSoldPercentage.Value = Me.txtListingSales / Me.txtSellerContracts2
        End If
    End If
    
    Set rs = Nothing
    
    'Populate % Buyer Appointments to Contracts
    strSQL = "SELECT Count(eventOccurrence.fk_EventID) AS Appointments" & _
             " FROM eventOccurrence" & _
             " WHERE (((eventOccurrence.eventDate) Between #" & g_datStartDate & "# AND #" & g_datEndDate & "#)" & _
             " AND ((eventOccurrence.fk_EventID)=7));"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    
    If rs.RecordCount > 0 Then
        Me.txtBuyerAppointments.Value = Nz(rs("Appointments"))
    End If
    
    Set rs = Nothing
    
    strSQL = "SELECT Count(client.intClient) AS Contracts" & _
             " FROM eventOccurrence" & _
             " LEFT JOIN client ON eventOccurrence.fk_ClientID = client.pk_ClientID" & _
             " WHERE (((eventOccurrence.eventDate) Between #" & g_datStartDate & "# AND #" & g_datEndDate & "#)" & _
             " AND ((eventOccurrence.fk_EventID)=7) AND ((client.intClient)=-1));"

    Set rs = db.OpenRecordset(strSQL)
    
    If rs.RecordCount > 0 Then
        If Nz(rs("Contracts")) < 1 Then
            Me.txtBuyerContracts.Value = "0"
            Me.txtBuyerContracts2.Value = "0"
            Me.txtBuyerPercentage.Value = "0%"
        Else
            Me.txtBuyerContracts.Value = Nz(rs("Contracts"))
            Me.txtBuyerContracts2.Value = Nz(rs("Contracts"))
            Me.txtBuyerPercentage.Value = Me.txtBuyerContracts / Me.txtBuyerAppointments
        End If
    End If
    
    Set rs = Nothing
    
    'Populate % Buyer Contracts to Sales
    strSQL = "SELECT Count(client.intClient) AS Sales" & _
             " FROM (eventOccurrence" & _
             " LEFT JOIN client ON eventOccurrence.fk_ClientID = client.pk_ClientID)" & _
             " LEFT JOIN reTransaction ON client.pk_ClientID = reTransaction.fk_ClientID" & _
             " WHERE (((eventOccurrence.eventDate) Between #" & g_datStartDate & "# AND #" & g_datEndDate & "#)" & _
             " AND ((eventOccurrence.fk_EventID)=7)" & _
             " AND ((client.intClient)=-1)" & _
             " AND ((reTransaction.fk_ClientID) Is Not Null));"

    Set rs = db.OpenRecordset(strSQL)
    
    If rs.RecordCount > 0 Then
        If Nz(rs("Sales")) < 1 Then
            Me.txtBuyerSales.Value = "0"
            Me.txtBuyersSoldPercentage.Value = "0%"
        Else
            Me.txtBuyerSales.Value = Nz(rs("Sales"))
            Me.txtBuyersSoldPercentage.Value = Me.txtBuyerSales / Me.txtBuyerContracts2
        End If
    End If
    
    Set rs = Nothing
    Set db = Nothing

    Me.lblBrokerageName.Caption = gu_strBrokerageName
    Me.lblBusinessName.Caption = gu_strBusinessName
    
End Sub

 

[dhookom]

I would typically use subreports or possibly domain aggregate functions. Assuming you had functions that returned the g_datStartDate and g_datEndDate values I think you could replace the txtListingAppointments with a control source of:

=DCount("fk_EventID","eventOccurence","EventDate Between #" & fGetStartDate() & "# AND #" & fGetEndDate() & "#")

Your code also seems to be setting control values to strings rather than numerics.

    Me.txtBuyerSales.Value = "0"
    Me.txtBuyersSoldPercentage.Value = "0%"

I would have expected:

    Me.txtBuyerSales.Value = 0
    Me.txtBuyersSoldPercentage.Value = 0

I also never use Nz() without two arguements:
Nz(rs("Appointments")[red],0[/red]) rather than Nz(rs("Appointments"))




Duane
Hook'D on Access
MS Access MVP

 

[PRMiller2]

Would that prove more efficient than use DAO recordsets? I haven't tried what you suggested yet due to time constraints, and I'll experiment rather than asking you to write the answer for me, but can DCOUNT handle joins, such as what I have in the following?

"SELECT Count(client.intClient) AS Sales" & _             
" FROM (eventOccurrence" & _             
" LEFT JOIN client ON eventOccurrence.fk_ClientID = client.pk_ClientID)" & _             
" LEFT JOIN reTransaction ON client.pk_ClientID = reTransaction.fk_ClientID" & _             
" WHERE (((eventOccurrence.eventDate) Between #" & g_datStartDate & "# AND #" & g_datEndDate & "#)" & _             
" AND ((eventOccurrence.fk_EventID)=23)" & _             
" AND ((client.intClient)=-1)" & _             
" AND ((reTransaction.fk_ClientID) Is Not Null));"

Thank you for pointing out the strings rather than numerics and the NZ correction. I'll implement those as soon as I can plug back in, hopefully in the next few days!

 

[dhookom]

DCount() must use query or table names.

Duane
Hook'D on Access
MS Access MVP