Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Access records disappearing on report


(!)

willi1972's Avatar
willi1972 willi1972 is offline
Member with 4 posts.
THREAD STARTER
 
Join Date: Jun 2012
08-Jun-2012, 06:41 AM #1
Access records disappearing on report
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
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,649 posts.
 
Join Date: Mar 2005
Location: UK
08-Jun-2012, 01:48 PM #2
willi1972, welcome to the Forum.

This sounds a bit odd, are you sure that the record is not on yet another page?
willi1972's Avatar
willi1972 willi1972 is offline
Member with 4 posts.
THREAD STARTER
 
Join Date: Jun 2012
08-Jun-2012, 01:55 PM #3
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
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,649 posts.
 
Join Date: Mar 2005
Location: UK
08-Jun-2012, 02:25 PM #4
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?
willi1972's Avatar
willi1972 willi1972 is offline
Member with 4 posts.
THREAD STARTER
 
Join Date: Jun 2012
08-Jun-2012, 02:41 PM #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
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,649 posts.
 
Join Date: Mar 2005
Location: UK
09-Jun-2012, 04:22 AM #6
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.
willi1972's Avatar
willi1972 willi1972 is offline
Member with 4 posts.
THREAD STARTER
 
Join Date: Jun 2012
09-Jun-2012, 10:10 AM #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
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,649 posts.
 
Join Date: Mar 2005
Location: UK
09-Jun-2012, 10:18 AM #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.
__________________
OBP
I do not give up easily
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
access, disappearing, records, report, vba

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑