Results 1 to 8 of 8

Recordcount not working for DAO based on sql

  1. #1
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    190

    Recordcount not working for DAO based on sql

    Hi pplz,

    I have another thread going about a huge amount of stuff im doing.

    This is separate and related to .recordcount.

    I have this query.
    Code:
    SELECT scripts.InvoiceID, scripts.PaymentID, scripts.RcvdAmount, scripts.WrittenOff, scripts.OnHold, scripts.ScriptIDFROM scripts
    WHERE (((scripts.InvoiceID)=21) AND ((scripts.PaymentID) Is Null) AND ((scripts.WrittenOff)=false) AND ((scripts.OnHold)=false))
    Now on PHP Admin it WORKS and gives 4 records.

    When i do


    currentdb.openrecordset(SQLSTRING)

    and then use rs.recordcount

    It is coming up with ONE record only!!!

    I have tried using

    rs.movelast
    rs.movefirst

    before doing this but it fails on rs.movelast (no current record)

    any ideas!!?

    Access gives 4 records,
    PHP admin gives 4 records!

    What am i doing wrong!?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,227
    I'm a bit confused. How about posting all of the code that is creating and using that recordset? What do you mean: Access gives 4 records?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    4,547
    You don't need moveFirst/moveLast to get record count.

    set rst = currentdb.openrecordset(SQL)
    msgbox rst.recordcount

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,227
    @ranman256: seems to me that DAO will only return 1 until you do a move last assuming you want the total recordcount for the recordset.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    10,647

  6. #6
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    190
    thanks for the responses.

    This is all of my code, pretty much not much more than what i posted.

    Code:
    Dim InvRS As DAO.Recordset
    strsql = "SELECT scripts.InvoiceID, scripts.PaymentID, scripts.RcvdAmount, scripts.WrittenOff, scripts.OnHold, scripts.ScriptID from Scripts WHERE (((scripts.InvoiceID)=21) AND ((scripts.PaymentID) Is Null) AND ((scripts.WrittenOff)=0) AND ((scripts.OnHold)=0))"
    
    Set InvRS = CurrentDb.OpenRecordset(strsql)
    MsgBox InvRS.RecordCount

    What i meant by "access returns 4"

    I took the sql string and put it into access query as well as into phpmyadmin

    Both return the 4 records.

    It is only when in a dao recordset that it doesnt return correctly.


    When this was a linked table, via access not odbc mysql it was working fine.

  7. #7
    gangel is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    190
    i just added:

    Code:
    invtxt = ""
    InvRS.MoveLast
    InvRS.MoveFirst
    Do Until InvRS.EOF
    invtxt = InvRS!ScriptID & " " & invtxt
    InvRS.MoveNext
    Loop
    
    
    MsgBox invtxt
    MsgBox InvRS.RecordCount
    it gave my 76 77 78 79

    which is correct, 4 scriptID

    !!!


    I then Took away all the invtxt stuff and just left it as before and IT WORKED

    WTF

    So now its working and isnt saying "no current record" when i use invrs.movelast and move first....

    No idea what is happening now.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,227
    Hmmm...I hate it when that happens. I would exit Access and then launch it again to make sure of the issue.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 12-17-2014, 08:51 PM
  2. checking date when recordcount > 0
    By JeroenMioch in forum Programming
    Replies: 14
    Last Post: 03-05-2013, 11:39 AM
  3. RecordCount after filtering is incorrect
    By George in forum Access
    Replies: 2
    Last Post: 05-29-2012, 12:02 PM
  4. Recordcount with filter on
    By injanib in forum Forms
    Replies: 3
    Last Post: 06-15-2011, 02:07 PM
  5. Replies: 3
    Last Post: 10-20-2009, 05:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums