469,150 Members | 1,896 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,150 developers. It's quick & easy.

counting records

2
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.
Jan 28 '15 #1
3 873
TheSmileyCoder
2,321 Expert Mod 2GB
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.
Jan 28 '15 #2
jforbes
1,107 Expert 1GB
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:
Expand|Select|Wrap|Line Numbers
  1. Global Const nColorAccessTheme4 = -2147483604
  2. Global gReportAltCount As Long
  3.  
Report Codebehind:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  5.  
  6.     Dim lCurrentColor As Long
  7.  
  8.     gReportAltCount = gReportAltCount + 1
  9.  
  10.     If (gReportAltCount Mod 2) = 0 Then
  11.         lCurrentColor = nColorAccessTheme4
  12.     Else
  13.         lCurrentColor = vbWhite
  14.     End If
  15.  
  16.     Me.ItemNumber.BackColor = lCurrentColor
  17.     Me.QTY.BackColor = lCurrentColor
  18.     Me.SUPPLIER.BackColor = lCurrentColor
  19.     Me.SupplierPartNumber.BackColor = lCurrentColor
  20.     Me.Description.BackColor = lCurrentColor
  21.  
  22. End Sub
  23.  
  24. Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
  25.  
  26.     Dim lStart As Long
  27.     Dim lOddColor As Long
  28.     Dim lEvenColor As Long
  29.  
  30.     lStart = 2880
  31.     If (gReportAltCount Mod 2) = 0 Then
  32.         lOddColor = vbWhite
  33.         lEvenColor = nColorAccessTheme4
  34.     Else
  35.         lOddColor = nColorAccessTheme4
  36.         lEvenColor = vbWhite
  37.     End If
  38.  
  39.     If Me.Top < 360 + lStart Then Me.txtBlank16 = " "
  40.     If Me.Top < 720 + lStart Then Me.txtBlank15 = " "
  41.     If Me.Top < 1080 + lStart Then Me.txtBlank14 = " "
  42.     If Me.Top < 1440 + lStart Then Me.txtBlank13 = " "
  43.     If Me.Top < 1800 + lStart Then Me.txtBlank12 = " "
  44.     If Me.Top < 3160 + lStart Then Me.txtBlank11 = " "
  45.     If Me.Top < 2520 + lStart Then Me.txtBlank10 = " "
  46.     If Me.Top < 2880 + lStart Then Me.txtBlank9 = " "
  47.     If Me.Top < 3240 + lStart Then Me.txtBlank8 = " "
  48.     If Me.Top < 3600 + lStart Then Me.txtBlank7 = " "
  49.     If Me.Top < 3960 + lStart Then Me.txtBlank6 = " "
  50.     If Me.Top < 4320 + lStart Then Me.txtBlank5 = " "
  51.     If Me.Top < 4680 + lStart Then Me.txtBlank4 = " "
  52.     If Me.Top < 5040 + lStart Then Me.txtBlank3 = " "
  53.     If Me.Top < 5400 + lStart Then Me.txtBlank2 = " "
  54.     If Me.Top < 5760 + lStart Then Me.txtBlank1 = " "
  55.  
  56.     Me.txtBlank2.BackColor = lEvenColor
  57.     Me.txtBlank4.BackColor = lEvenColor
  58.     Me.txtBlank6.BackColor = lEvenColor
  59.     Me.txtBlank8.BackColor = lEvenColor
  60.     Me.txtBlank10.BackColor = lEvenColor
  61.     Me.txtBlank12.BackColor = lEvenColor
  62.     Me.txtBlank14.BackColor = lEvenColor
  63.     Me.txtBlank16.BackColor = lEvenColor
  64.     Me.txtBlank1.BackColor = lOddColor
  65.     Me.txtBlank3.BackColor = lOddColor
  66.     Me.txtBlank5.BackColor = lOddColor
  67.     Me.txtBlank7.BackColor = lOddColor
  68.     Me.txtBlank9.BackColor = lOddColor
  69.     Me.txtBlank11.BackColor = lOddColor
  70.     Me.txtBlank13.BackColor = lOddColor
  71.     Me.txtBlank15.BackColor = lOddColor
  72.  
  73. End Sub
  74.  
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.
Jan 28 '15 #3
go9189
2
Hi TheSmileyCoder and jforbes, thank you for your helpful yet informative reply. I have solved my problem. Have a good day.
Jan 30 '15 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by N. Graves | last post: by
1 post views Thread by Bill Agee | last post: by
2 posts views Thread by joshimahen | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.