Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Unanswered: Confusing Recordcount results

    I'm totally stumped by this, and it's pretty hard to debug my code when recordcount doesn't seem to be functioning correctly...

    Can anyone explain to me why:

    Code:
    strSortSQL = "SELECT tmp_orders_messy.* " & _
                 "FROM tmp_orders_messy " & _
                 "WHERE NOT EXISTS " & _
                    "( SELECT 1 " & _
                    "FROM ref_knownCodes " & _
                    "WHERE [Exclude] = tmp_orders_messy.[Product Code] ) " & _
                 "AND NOT EXISTS " & _
                    "( SELECT 1 " & _
                    "FROM ref_knownCodes " & _
                    "WHERE [Custom] = tmp_orders_messy.[Product Code] ) " & _
                 "AND NOT tmp_orders_messy.[Product Code] LIKE '[0-9]*' " & _
                 "ORDER BY tmp_orders_messy.[Order Date], tmp_orders_messy.[Order Number]"
    Followed by:

    Code:
    Set rs = CurrentDb.OpenRecordset(strSortSQL)
    
    MsgBox "Current Record is: " & rs.RecordCount
    rs.MoveLast
    MsgBox "Total Records: " & rs.RecordCount
    rs.MoveFirst
    MsgBox "Current Record is: " & rs.RecordCount

    Returns 11 everytime.

    There are 8 Fields in the table in question, and 13 records in total, 2 of which are being excluded according to the NOT EXIST statements.

    But why is it returning the total number of records, instead of the current records position in the record set?

    Any help would be great!

    Thanks fella's.
    Last edited by kez1304; 09-29-11 at 11:03.
    Looking for the perfect beer...

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Found that what I was looking for was rs.AbsolutePosition...

    Could have sworn it was recordCount... Perhaps it changed from windows 2000 to 2007? (upgraded recently).

    Either way, all solved.
    Looking for the perfect beer...

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    RecordCount gives you... the Record Count!

    CurrentRecord gives you... the Current Record number!

    As you found out, AbsolutePosition works as well, in this situation. And per Allen Browne, RecordCount causes grief with Access 2007/2010, sometimes working and sometimes not.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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