Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008
    Posts
    13

    Unanswered: Help - DAO Recordcount is not working

    Hi,

    I am triing to use a dao vba command rs.recordcount.

    Everytime I do this it will bring back a count of 1.

    Example:

    if rs.recordcount = 0 then
    'do something
    else
    'do something
    end if

    It always brings back 1 even though there are no records. Can anyone help me?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's the code I use (it's ADO) though (it's actually a fucntion I call which returns the recordcount:

    Function RetMyTableRecordCount() as integer
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyTable"
    rs.open strsql,currentproject.connection,adopenkeyset,adlo ckreadonly (remove the space adlockreadonly)
    if rs.eof and rs.bof then
    RetMyTableRecordCount = 0
    else
    RetMyTableRecordCount = rs.recordcount
    end if
    rs.close
    set rs = nothing
    End Function

    You can't just put in rs.Recordcount in your code (even in dao). rs first needs to be configure correctly in order to open a recordset which you are actually counting (ie. applying the .recordset to). You should have some code before that which has dim rs as .... and set rs = ..... and so on above this code. If you do and simply forgot to copy it, please copy and paste it for us to see. If not, you may want read up on how to open a recordset and use the .recordcount command.
    Last edited by pkstormy; 10-29-08 at 01:49.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    You might also want to try rs.MoveLast to make sure that the recordset is fully populated before checking the RecordCount. Don't forget to do a rs.MoveFirst if you need to loop through anything otherwise it would be a short trip to EOF if you were already at the last record.

    C

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you use the ADO code I posted above....

    If you open the recordset up as adkeyset and adlockreadonly, you don't need to utilize the rs.movelast/rs.movefirst.

    If you open the recordset up as something else (ie. adopendynamic, adlockoptimistic), then yes you will need to rs.movelast/rs.movefirst before your rs.recordcount Code.

    I'm guessing with DAO code you need to always do the rs.movefirst/rs.movelast.
    Last edited by pkstormy; 10-29-08 at 02:42.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    PK - you are right. I've just been too stubborn to learn ADO and I just think in DAO. I suppose one of these days I'll have to start using ADO.

    C

Posting Permissions

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