1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access records disappearing on report

Discussion in 'Business Applications' started by willi1972, Jun 8, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. willi1972

    willi1972 Thread Starter

    Joined:
    Jun 8, 2012
    Messages:
    4
    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
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    willi1972, welcome to the Forum.

    This sounds a bit odd, are you sure that the record is not on yet another page?
     
  3. willi1972

    willi1972 Thread Starter

    Joined:
    Jun 8, 2012
    Messages:
    4
    Hi,

    I agree - very odd! I have never had a problem like this before.

    The record is definitely not on a different page. I have tried with different data, and the same thing happens.

    Regards
    Richard
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    It could be being caused by the VBA, Have you tried removing (taking a copy first) the VBA to see if the last record then appears?
     
  5. willi1972

    willi1972 Thread Starter

    Joined:
    Jun 8, 2012
    Messages:
    4
    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
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Perhaps you should use a dedicated If then statement, rather than using the Else for that part of the code to see if it makes any difference.
     
  7. willi1972

    willi1972 Thread Starter

    Joined:
    Jun 8, 2012
    Messages:
    4
    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. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1056264