473,549 Members | 2,717 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

counting records

2 New Member
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 984
2,322 Recognized Expert Moderator Top Contributor
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
1,107 Recognized Expert Top Contributor
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
Report Codebehind:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  6.     Dim lCurrentColor As Long
  8.     gReportAltCount = gReportAltCount + 1
  10.     If (gReportAltCount Mod 2) = 0 Then
  11.         lCurrentColor = nColorAccessTheme4
  12.     Else
  13.         lCurrentColor = vbWhite
  14.     End If
  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
  22. End Sub
  24. Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
  26.     Dim lStart As Long
  27.     Dim lOddColor As Long
  28.     Dim lEvenColor As Long
  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
  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 = " "
  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
  73. 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.
Jan 28 '15 #3
2 New Member
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

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

Similar topics

by: mgm | last post by:
hello, I have a query that is supposed to return only 1 record, however I recently found that because of an error in the database it can return more than 1. So what I need to do is capture if it is more than 1 record and alert the user to contact the database administrator. How can I get the count of records returned by a recordset? Is...
by: N. Graves | last post by:
I have a table with records of games played with a field for players name and another fields is a "WinorLose". One player may play several games to complete a match. I want my report to tell me how many Wins or Loses and Individual has in a match.. I'm not having a problem getting the reports and the grouping it by person but I'm having a...
by: BlackFireNova | last post by:
I have an Access 2003 mdb which contains software records. I need to sort on a particular type of software, and then identify and count how many copies there are per each group of that type purchased on the same date. I have no trouble doing a query to extract the type (say MS Excel 2002, for example). The trouble is, there could be 50...
by: Bill Agee | last post by:
I would like to number the records in a query. If I have 10 records, is there a way to assign numbers 1 - 10 to those records?
by: chrisc | last post by:
Hello, Hope this is the right place for this... I am creating a testing database for components as they come off a production line. My reports need to select faults that are found, as well as pass or fails dependent on a batch number.
by: allyn44 | last post by:
Hello, I have built a serch form for users to edit records. I only want them to pull up the record they need, and I want to check for nulls. There should not be dupes becasue the underlying tables are indexed. So the user will pull up either one record or none. Here is my problem: I pass the form name through a tmpvariable and ther other...
by: joshimahen | last post by:
I have created 4 datagrid in different controls and these controls I am calling in home page. Now I want that if there are more than 20 rows(for all 4 controls or it may be 1 or controls also) in that screen then one more button should display and remaining recored and controls should display in next page when user click on more button. I am...
by: KCangelstar | last post by:
In my database, I am trying to count records from a field that meet the criteria of "SELECT DISTINCTROW WARes016.Stratum, Count((.)) AS CountOfDays FROM WARes016 INNER JOIN ON WARes016.LRS = .LRS GROUP BY WARes016.Stratum, <42 ORDER BY WARes016.Stratum; This code when run breaks the count into two groups:a count of per Stratum that meet my...
by: crassostrea | last post by:
Hello and Happy New Year, I have two tables in Access 2003 (Windows XP) with similar, but different, information. We’ll call them table A and table B. I want to count the number of records in each table and display how many of each there are, and the total, by year: 2008 A B A+B 2007 A B A+B 2006 A B A+B Right now I...
by: neelsfer | last post by:
When i use this code to count the number of records on display in a specific subform in the "txtRecordNo" field that is open now, it seems to "update" all the time as the cursor changes every second and i am unable to add new records on mainform, untill i click in one of the fields of this Subform. Then it settles down. Otherwise it works 100%...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.