Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Red face Unanswered: Problem with recordcount, returns 1

    Could somebody please help me! I am having a problem with a very simple operation and know that I am just overlooking something. I am looping through a recordset using a for statement and using the recordcount as the "to", yet it keeps returning the value of 1.

    Here is the code:

    Public Function openConn()

    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase("c:/pbc/pbcData.mdb")
    End Function

    Public Function closeConn()

    db.Close
    Set db = Nothing

    ws.Close
    Set ws = Nothing
    End Function

    Private Sub rolloverTasks ()
    Call openConn

    Dim rsMR As Recordset
    Dim sqlMR As String
    'rolls open routine maintenance tasks to overdue
    sqlMR = "SELECT m_entry_id, status FROM tblMaintenance WHERE status = 10 OR status = 14"
    Set rsMR = db.OpenRecordset(sqlMR)

    v-problem.

    For i = 1 To rsMR.RecordCount
    If rsMR!status = 14 Then
    rsMR.Edit
    rsMR!status = 10
    rsMR.Update
    Else
    rsMR.Edit
    rsMR!status = 13
    rsMR.Update
    End If
    Next i
    rsMR.Close
    Set rsMR = Nothing

    Dim rsTR As Recordset
    Dim sqlTR As String
    'rolls open tasks to overdue
    sqlTR = "SELECT tentry_id, status FROM tblTaskHistory WHERE status = 10 OR status = 14"
    Set rsTR = db.OpenRecordset(sqlTR)

    v-problem

    For i = 1 To rsTR.RecordCount
    If rsTR!status = 14 Then
    rsTR.Edit
    rsTR!status = 10
    rsTR.Update
    Else
    rsTR.Edit
    rsTR!status = 13
    rsTR.Update
    End If
    Next i
    rsTR.Close
    Set rsTR = Nothing
    call closeconn
    end sub

    I have changed the openrecordset to a table and the recordcount is accurate, but I have used "Select * From tblMaintenance" and it also returns 1.

    Any help would be appreciated.
    Thanks,
    Josh

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Problem with recordcount, returns 1

    It sounds dumb, but try adding a couple of lines...

    rsMR.MoveLast
    rsMR.MoveFirst

    ..and..

    rsTR.MoveLast
    rsTR.MoveFirst

    ...to your code, right after you've done your Set rs = statement...

    This will ensure that the Count property is properly populated... I've seen it used mannnnnnyyyy times...

    HTH...

  3. #3
    Join Date
    Jan 2004
    Posts
    3

    Re: Problem with recordcount, returns 1

    you are a genius, thanks

    Originally posted by Trudi
    It sounds dumb, but try adding a couple of lines...

    rsMR.MoveLast
    rsMR.MoveFirst

    ..and..

    rsTR.MoveLast
    rsTR.MoveFirst

    ...to your code, right after you've done your Set rs = statement...

    This will ensure that the Count property is properly populated... I've seen it used mannnnnnyyyy times...

    HTH...

  4. #4
    Join Date
    Jan 2004
    Location
    Australia
    Posts
    15
    You might want to add some more lines of code to that. Most times you will have to move to the last record in the recordset to get the true record count, but if the recordset returns nothing then moving to the last record will error out.

    You need to set up some error handling so it can get around this.

    Code:
    'Example
    
    if rsMR.EOF = false then
          rsMR.MoveLast
          rsMR.MoveFirst
    End if
    In your case you might know that you are always going to return at least 1 record and then you won't have a problem, but it is probably good practice to set it up like this to accomodate for every situation, especially the ones that you never even thought of.

  5. #5
    Join Date
    Jan 2004
    Posts
    3
    thanks
    Originally posted by A Webster
    You might want to add some more lines of code to that. Most times you will have to move to the last record in the recordset to get the true record count, but if the recordset returns nothing then moving to the last record will error out.

    You need to set up some error handling so it can get around this.

    Code:
    'Example
    
    if rsMR.EOF = false then
          rsMR.MoveLast
          rsMR.MoveFirst
    End if
    In your case you might know that you are always going to return at least 1 record and then you won't have a problem, but it is probably good practice to set it up like this to accomodate for every situation, especially the ones that you never even thought of.

Posting Permissions

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