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 first on the new 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.
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,
The bit of VB which does the adding up counts the missing record successfully, but it does not appear on the report.
Any help on this would be much appreciated!
Kind regards
Richard
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 first on the new 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.
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
Any help on this would be much appreciated!
Kind regards
Richard