Share via

VBA or Macro needed to count the number of records in a table using the current value entered into the form control for that table's field

Anonymous
2010-12-14T19:29:56+00:00

I need to count the number of times a particular device has been used, using the table serial number field.  There are going to be hundreds of the device all differentiated only by serial number.  I am thinking to use the SQL "SELECT COUNT..."  and then DoCmd.RunSQL on the query string.  The only problem is that I can't figure out how to get the form's CURRENT serial number control value into the query string.  Once I get that to run, I need to take the COUNT value subtract that from the upper limit of use and enter that into a separate form field.  These values will, yes, be stored back into the table ----- they want a running history within each record.  Calculated fields are a requirement.  I am VERY OPEN to alternative approaches.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2010-12-14T19:33:48+00:00

    You could use DCount("*","tblSomething", "SerialNo=" & [SerialNo]) to get the count, where tblSomething is the name of the table and SerialNo the name of the field. This assumes that SerialNo is a number field. If it is a text field, it'd be DCount("*","tblSomething", "SerialNo=" & Chr(34) & [SerialNo] & Chr(34))

    Chr(34) is the double quote character ".

    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,810 Reputation points Volunteer Moderator
    2010-12-15T12:54:21+00:00

    Sometimes technical requirements are overruled by management requirements - calculated fields are a requirement - needs to be stored with the record.  No option.

    Sorry, but I STRONGLY disagree with that statement. Management requirements are that a running history be kept. That requirement can be met with a query utilizing an effective date. Management does not have to even know how the running history is achieved, they only need to know that an accurate history can be produced. The ONLY time that management requirements trump technical requirements is when the management requirements can't be achieved by following technological rules. And that rarely occurs. I have several mantras when designing databases and one of them is that users NEVER dictate database design. They can dictate user interfaces (which includes reports) but not design. If users knew how to design a database properly they wouldn't need a developer to do it for them.

    Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

    0 comments
  2. Anonymous
    2010-12-14T22:10:17+00:00

    Yes INDEED.  I got the count and now it is just a matter of entering back into the table (bad form I know - but I'm only following orders.... :)

    Thanks

    0 comments
  3. Anonymous
    2010-12-14T21:21:36+00:00

    Sometimes technical requirements are overruled by management requirements - calculated fields are a requirement - needs to be stored with the record.  No option.

    0 comments
  4. ScottGem 68,810 Reputation points Volunteer Moderator
    2010-12-14T19:36:52+00:00

    Sorry but calculated fields are NOT a requirement. You can use a date stamp to get your counts as of any specific date range.

    If you are looking to return the count of records for a specific device identified by a serial number, then you can set the controlsource of a text box to:

    =DCount("*","tablename","[SerialNumber] = " & [controlname])

    Controlname is the name of the control displaying the serial nunmber if the serial number is text use

    "[SerialNumber] =' " & [controlname] & "'"

    Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

    0 comments