Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: get the record count from the form's recordset

    I am trying to get the record count from the form's recordset.
    The query works since the form displays three records but the recordcount
    field is showing 0. My Select statement is in form open event procedure and I am using recordcount property to display the # of records on the form.

    Private Sub Form_Open(Cancel As Integer)

    sqlstr = "SELECT * FROM tblBag_results WHERE sampling_data_id = " & sampling_data_id & " Order by bag_num asc"
    Me.RecordSource = sqlstr

    Debug.Print Me.Recordset.RecordCount
    'Me!lblNumBags.Caption = "Total Bags Sampled: " & Me.Recordset.RecordCount
    ...

    Can anyone see why this is not working. Thanks!

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    Probably you need to move to end of the records, or recordcount might return wrong number. use .movelast method before checking recordcount.
    ghozy.

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    There are a couple of problems. First the Open event doesn't 'hook up' the data to the form that happens later. So when the Open event happens you will never be able to determine the recordcount using the form's recordsource. I think you have two options:

    1. Use a separate recordset object to retrieve the data with the SQL and then count the number of records returned

    2. Wait until you get to the On_Load event to get the record count.

    Scenario 1 - In the On_Open Event

    set rstResults = CurrentDB().OpenRecordset(strSQL,DbOpenSnapshot)
    If rstResults.RecordCount<>0 Then rstResults.MoveLast
    lngRecords = rstResults.RecordCount
    rstResults.Close
    set rstResults = Nothing

    Scenario 2 - In the On_Load Event of the form

    rstResults = Me.RecordsetClone
    If rstResults.RecordCount<>0 Then rstResults.MoveLast
    lngRecords = rstResults.RecordCount
    rstResults.Close
    set rstResults = Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •