Record disappears from report when new page is forced

[willi1972]

Hi all,



I have a problem with records disappearing on a report in Access 2007. This is with a fairly simple database to track overtime. It only happens when there are so many items in the report that it is forced to continue on a new page. The record that should be the last on the page just is not there.

If I reduce the font size to squeeze everything into 1 page, there is no problem. When I set the size back to normal and it flows onto 2 pages, I have a missing record, so it does not seem to be a problem with the SQL of the VB.

The SQL used to select the data is as follows:

SELECT Staff.FirstName, Staff.Surname, Staff.SemesterSubBank, Staff.StaffID, [Staff cover].StaffCoverID, [Staff cover].StaffCovering, [Staff cover].CoverDate, [Staff cover].Category, [Staff cover].Reason, [Staff cover].Lektionen, [Staff cover].Kontaktzeit, [Staff cover].Printed, [Staff cover].SubBankEligible, [Staff cover].Time, [Staff cover].Class, [Staff cover].Subject, [Staff cover].Notes
FROM Staff INNER JOIN [Staff cover] ON Staff.StaffID = [Staff cover].StaffCovering;

There is also some VBA to decide which of these records to display, and to do some adding up,


Code:

Public LektionenTotal As Single
Public KontaktzeitTotal As Single
Public MonthlyLektionenTotal As Single
Public MonthlyKontaktzeitTotal As Single
Public BankTotal As Single
Public StartDate As Date
Public EndDate As Date
Public RecordCounter As Long

'#########################################################
'#################Change dates at bottom##################
'#########################################################

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

'decide whther to make a row visible or not
    If [SubBankEligible] = True And ([Lektionen] > 0 Or [Kontaktzeit] > 0) Then 'if it comes out of the subbank and is not a fehlzeit
        BankTotal = BankTotal - Nz([Lektionen]) - (Nz([Kontaktzeit]) / 72) 'update bank on report, converting Kontaktzeit into 45 minute Stunden
        If BankTotal < 0.1 And [CoverDate] >= StartDate And [CoverDate] <= EndDate And [Printed] = 0 Then  'if they have run out of subbank
            Detail.Visible = True
            MonthlyKontaktzeitTotal = MonthlyKontaktzeitTotal + Nz([Kontaktzeit]) 'copied from above
            MonthlyLektionenTotal = MonthlyLektionenTotal + Nz([Lektionen]) 'copied from above
            RecordCounter = RecordCounter + 1
            Else
                Detail.Visible = False 'hide it if they have not run out of sub bank
        End If
        Else 'if is not subbank eligible
            If ([Lektionen] > 0 Or [Kontaktzeit] > 0) And [CoverDate] >= StartDate And [CoverDate] <= EndDate Then 'if it's not subbank eligible then pay it anyway
                Detail.Visible = True
                RecordCounter = RecordCounter + 1
                MonthlyKontaktzeitTotal = MonthlyKontaktzeitTotal + Nz([Kontaktzeit]) 'copied from above
                MonthlyLektionenTotal = MonthlyLektionenTotal + Nz([Lektionen]) 'copied from above
                Else 'if it is a fehlzeit
                    Detail.Visible = False
            End If
    End If

txtLessonTotal.Value = MonthlyLektionenTotal ' add the totals to the footer
txtContactTotal.Value = MonthlyKontaktzeitTotal

End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'initialise for a new mwmber of staff
LektionenTotal = 0
KontaktzeitTotal = 0
MonthlyLektionenTotal = 0
MonthlyKontaktzeitTotal = 0

End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
BankTotal = [SemesterSubBank]
'==========================================
StartDate = #5/1/2012# '        <<<<<CHANGE THESE
EndDate = #5/31/2012#
'==========================================
'last one was 2/21/2012 to 3/27/2012

txtMonth.Value = "April 2012"
RecordCounter = 0
End Sub

The bit of VB which does the adding up counts the missing record successfully, but it does not appear on the report. I have traced exactly what is happening in the VB, and it all behaves as expected - apart from the mysterious non-display of the last record on the page. Specifically, the .visible=true works correctly. Is there another event that happens when a new page happens? Maybe this somehow interferes with the execution of the detail.visible=true for the last record. This command is definitely executed, but nothing happens.

I have tried the report with no VB, and it works as expected, so something unexpected is definitely happening with the VB.

Any help on this would be much appreciated!

Kind regards
Richard

[Missinglinq]

Have you actually tried printing this out? In Print Preview you have to physically navigate to the next page, it doesn't appear underneath the first page, as it would in a Word document.

Linq ;0)>