Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    recordcount (2003 SP3)

    Hello All,
    I am using this in code to get the number of records that match certain criteria:

    Dim db As Database, rsNewEst As Recordset
    Dim db1 As Database, rsExistingIncident As Recordset, intEstimateRev As Integer, strEstRec As String, intRecCount

    Set db1 = CurrentDb
    strEstRec = "Select * from tblEstimates where incident_id = " & Forms!frmincidents!Incident_ID
    Set rsExistingIncident = db1.OpenRecordset(strEstRec)

    intRecCount = rsExistingIncident.RecordCount

    There are a total of nine records that match but the recordcount is only returning a count of one. What am I missing?

    Thanks,
    Mark

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: recordcount (2003 SP3)

    The record count isn't calculated immediately for reasons of efficiency.

    If you only need the count, you can use

    intRecCount = DCount("*", "tblEstimates", "incident_id = " & Forms!frmincidents!Incident_ID)

    If you need the recordset for other purposes, you must move to the last record to get an accurate count:

    Set rsExistingIncident = db1.OpenRecordset(strEstRec, dbOpenDynaset)
    rstExistingIncident.MoveLast
    intRecCount = rsExistingIncident.RecordCount
    rstExistingIncident.MoveFirst

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: recordcount (2003 SP3)

    Hi Hans,
    OK, so now it makes sense why it was only returning a 1 initially. I guess I can just use the 1 for now as I am only really looking to see if there are any records there to begin with.

    Thanks,
    Mark

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: recordcount (2003 SP3)

    Another way to test whether there are records is the EOF property: if rsExistingIncident.EOF is True immediately after opening the recordset, there are no records, and if it is False, there is at least one record.

Posting Permissions

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