Tech Support Guy banner
Status
Not open for further replies.

Access records disappearing on report

2K views 7 replies 2 participants last post by  OBP 
#1 ·
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,

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.

Any help on this would be much appreciated!

Kind regards
Richard
 
See less See more
#5 ·
Hi

I also think it must be something to do with the VBA. All records DO appear with no VBA. I have used breakpoints to trace exactly what is happening, and the VBA is behaving exactly as I expect it to. The most important thing is that the visible property of the detail section is turned on under certain circumstances, and this happens for the missing record. However, the detail section for that record never appears. I have also narrowed it down to the record disappearing at the end if the page, rather than at the top of the next page.

The relevent section of code is:
Code:
        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
            Else
                Detail.Visible = False 'hide it if they have not run out of sub bank
        End If
This appears in the Detail_format section. It is as though the last visible=true command on the page never gets executed.

Regards
Richard
 
#7 ·
Hi,

I have tried this, to no avail. 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. 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.

Regards
Richard
 
#8 ·
I don't know of anything that would over ride the Detail On Format Event.
Although with Access 2007 they appear to have added an "On Current" event to the overall Report which I don't remember in Access 2003, it may have been there but I don't remember it.
You could try adding the detail.visible=true code to that event.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top