Results 1 to 12 of 12
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919

    Report RecordSource record count at Open

    In A2003, is there a method that can be used in the Open event as to the number of records in the RecordSource?


    Thanks,
    Bill

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The form method here:

    http://www.baldyweb.com/RecordCounts.htm

    or a DCount() against the source query. Note the load event might work better.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Paul,
    Apparently, this (code) is what you had in mind? Open event for a report. DCount in fact reported the correct number of records.
    Thanks,
    Bill

    Private Sub Report_Open(Cancel As Integer)



    Me.Filter = "RetYear = " & """" & gblRetreatYear & """" & " AND Attending = true" <<<<<<<<<<Still had to set the filter
    Me.FilterOn = True

    strCriteria = "RetYear = " & """" & gblRetreatYear & """" & " AND Attending = true"
    MsgBox DCount("[RosID]", "QRosterW/App", strCriteria)

    End Sub

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The DCount would work in either, I wasn't sure the recordset method would work in the open event. Because I'm lazy plus it would be more efficient, I'd set the criteria variable first and then use it for both the filter and the DCount.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Yes, once I had working code, I set a string variable to the "criteria" and used that string for both the filter and DCount.

    Thanks again.
    Bill

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem Bill. It is golf weather up there yet?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Just an addendum here, if you're looking to use the DAO recordset count you'd need to the last record (rst.movelast()) then do a recordcount (rst.recordcount()) then go back to the first record (if you were going to process records one at a time).

    Code:
    dim db as database
    dim rst as recordset
    dim iRecordcount as Long
    
    set db = currentdatabase
    set rst = db.openrecordset("SELECT * FROM TableName")
    rst.movelast
    irecordcount = rst.recordcount
    rst.close
    set db = nothing

  8. #8
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    I've not checked recently, but I think they'll open Graeagle on the 1st of May. At least that's when they usually open.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by rpeare View Post
    Just an addendum here, if you're looking to use the DAO recordset count you'd need to the last record (rst.movelast()) then do a recordcount (rst.recordcount()) then go back to the first record (if you were going to process records one at a time).

    Code:
    dim db as database
    dim rst as recordset
    dim iRecordcount as Long
    
    set db = currentdatabase
    set rst = db.openrecordset("SELECT * FROM TableName")
    rst.movelast
    irecordcount = rst.recordcount
    rst.close
    set db = nothing
    That was addressed in the link I posted, and yours will error on an empty recordset.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Thanks. Being as how I'm dealing with the underlying RecordSource of a Report, it was far less code to simply use DCount and get a count of the primary key field at Open. Were I dealing with a RecordSet in a general module, I would certainly employ your DAO suggestion.

    Thanks for your thoughts,
    Bill

  11. #11
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Yes, I didn't mention that it would bomb because he seemed to be using a recordset that had values, normally I program for a null dataset if I'm not sure what the results are going to be.

  12. #12
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    I was aware the code posted was vulnerable to error if the RecordSet was empty. But your postings here in that regard are nonetheless appreciated. As it is in the case of my report, I make it a practice of coding a "Report_NoData" in the report's code sheet and message to the user when that condition arises.
    Thanks,
    Bill

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 09-17-2013, 11:08 PM
  2. Replies: 2
    Last Post: 04-02-2013, 02:24 AM
  3. Open Report is opening one record only
    By kelann in forum Reports
    Replies: 4
    Last Post: 10-23-2012, 11:08 AM
  4. Replies: 8
    Last Post: 10-27-2011, 02:11 PM
  5. Record Count within a Report
    By BLD21 in forum Reports
    Replies: 1
    Last Post: 04-06-2011, 10:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums