counting records

[go9189]

Hi, I am trying to count the number of records in order to catch the last record for looping. My purpose is to fill up the extra space on my ACCESS 2003 report with records made invisible. I am doing this because I would like each page of the report to fill with given size of columns and borders. Each page of the report can accomodate 6 records. I use the MsgBox to monitor. I placed the code iLine = iLine + 1 in Detail - Format section and found out that the MsgBox has actually count from 1 to 7 with only 6 records. Can anyone help to explain why 7 but not 6, and if there is anyway to count the records correctly? Thanks.

[TheSmileyCoder]

A detail section of a report can be formatted more than once. A typical example would be near the end of the page. Access will format the detail and try to see if it will fit the current page. If not, it will move it to the next page and do a format again. There might even be cases where Access starts by thinking it should be on the second page, does a format, realises it can fit on the previous page and does a "retreat", and places it on the previous page.

Basically, you shouldn't be counting the format event.

If you have a criteria for selecting the records, you could apply the same criteria to a DCount call and use that.

[jforbes]

TheSmileyCoder has answered your question as to why quite well.

I recently went down this rabbit hole while replacing an existing MS-Word Document process with an Access Report. We wanted to keep the format similar to a Word Table since that was what our customers were used to recieving. And the standard process with the Word Document was to fill the table out with blank table lines to the end of the page to make sure it looked "Nice". Not only that, the report was to be Greybar, alternating grey and white background per line.

I ended up getting something close to the Word Format and in doing so I fell into the same programming pattern of seeing how far down the page we were when running out of records and then attempting to fill in the blank space with fields or possibly painting boxes. I scrapped the counting approach and went with hiding and showing existing fields in a group footer based on how far down the page the footer appeared.

I've included the code below for the Footer. Maybe you can reuse it. The Code to alternate colors is also in the code, I didn't feel up to stripping that out.

Globals for the Graybar:

Global Const nColorAccessTheme4 = -2147483604
Global gReportAltCount As Long
 

Report Codebehind:

Option Compare Database
Option Explicit
 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
 
    Dim lCurrentColor As Long
 
    gReportAltCount = gReportAltCount + 1
 
    If (gReportAltCount Mod 2) = 0 Then
        lCurrentColor = nColorAccessTheme4
    Else
        lCurrentColor = vbWhite
    End If
 
    Me.ItemNumber.BackColor = lCurrentColor
    Me.QTY.BackColor = lCurrentColor
    Me.SUPPLIER.BackColor = lCurrentColor
    Me.SupplierPartNumber.BackColor = lCurrentColor
    Me.Description.BackColor = lCurrentColor
 
End Sub
 
Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
 
    Dim lStart As Long
    Dim lOddColor As Long
    Dim lEvenColor As Long
 
    lStart = 2880
    If (gReportAltCount Mod 2) = 0 Then
        lOddColor = vbWhite
        lEvenColor = nColorAccessTheme4
    Else
        lOddColor = nColorAccessTheme4
        lEvenColor = vbWhite
    End If
 
    If Me.Top < 360 + lStart Then Me.txtBlank16 = " "
    If Me.Top < 720 + lStart Then Me.txtBlank15 = " "
    If Me.Top < 1080 + lStart Then Me.txtBlank14 = " "
    If Me.Top < 1440 + lStart Then Me.txtBlank13 = " "
    If Me.Top < 1800 + lStart Then Me.txtBlank12 = " "
    If Me.Top < 3160 + lStart Then Me.txtBlank11 = " "
    If Me.Top < 2520 + lStart Then Me.txtBlank10 = " "
    If Me.Top < 2880 + lStart Then Me.txtBlank9 = " "
    If Me.Top < 3240 + lStart Then Me.txtBlank8 = " "
    If Me.Top < 3600 + lStart Then Me.txtBlank7 = " "
    If Me.Top < 3960 + lStart Then Me.txtBlank6 = " "
    If Me.Top < 4320 + lStart Then Me.txtBlank5 = " "
    If Me.Top < 4680 + lStart Then Me.txtBlank4 = " "
    If Me.Top < 5040 + lStart Then Me.txtBlank3 = " "
    If Me.Top < 5400 + lStart Then Me.txtBlank2 = " "
    If Me.Top < 5760 + lStart Then Me.txtBlank1 = " "
 
    Me.txtBlank2.BackColor = lEvenColor
    Me.txtBlank4.BackColor = lEvenColor
    Me.txtBlank6.BackColor = lEvenColor
    Me.txtBlank8.BackColor = lEvenColor
    Me.txtBlank10.BackColor = lEvenColor
    Me.txtBlank12.BackColor = lEvenColor
    Me.txtBlank14.BackColor = lEvenColor
    Me.txtBlank16.BackColor = lEvenColor
    Me.txtBlank1.BackColor = lOddColor
    Me.txtBlank3.BackColor = lOddColor
    Me.txtBlank5.BackColor = lOddColor
    Me.txtBlank7.BackColor = lOddColor
    Me.txtBlank9.BackColor = lOddColor
    Me.txtBlank11.BackColor = lOddColor
    Me.txtBlank13.BackColor = lOddColor
    Me.txtBlank15.BackColor = lOddColor
 
End Sub
 

The lStart variable comes in handy to fudge the start point of the Hide and Show magic.

The Me.txtBlank# textboxes will print out a box if the contents are non-Null. They have their CanShrink property set to Yes so that when they have their value set to Null, they will shrink to nothing. The one caveat that I found with this approach is with the Shrinking ability of a control is not applied if the control's borders touch another control, so there needs to be some space between the textboxes. This space adds up and will mean that there is at least a 1/2 inch or so of whitespace that I couldn't get rid of on a page that was nearly full of data. After a few uncontrolled expletives on my part we considered it close enough.

[go9189]

Hi TheSmileyCoder and jforbes, thank you for your helpful yet informative reply. I have solved my problem. Have a good day.