UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Recordset Not Returning All Records, Access 2016    
 
   
B99
post May 27 2020, 09:58 AM
Post#1



Posts: 62
Joined: 15-April 20



Hi All,

I have some code that when I open a recordset as a full table, it works. But I only need a few records from the table so I tried setting the recordset with a 'WHERE' clause but when I do, it only returns one row. The same exact code, when used in a query, returns multiple rows as expected.

Here is the code that works:

CODE
  Dim db As Database
  Dim rsTest As Recordset
  Dim i As Integer
  
  Set db = CurrentDb
  'Set rsResponse = db.OpenRecordset("tblTest")

  For i = 0 To rsTest.RecordCount - 1
    If rsTest.Fields("DraftCheckbox") = True Then
      'There are 4 records where the checkbox is checked and all are processed
      Debug.Print "checked:"; rsTest.Fields("ID")  
      'Do some stuff
    End If

    rsTest.MoveNext
  Next i

'Do other stuff, close the recordset and db


Rather than loop through the full table, I tried to put the DraftCheckbox = True in the OpenRecordset command:

CODE
  Dim db As Database
  Dim rsTest As Recordset
  Dim i As Integer
  
  Set db = CurrentDb
  
  Set rsTest = db.OpenRecordset("SELECT tblTest.ID, tblTest.Text " _
                 & "FROM tblTest " _
                 & "WHERE tblTest.DraftCheckbox = True")

  For i = 0 To rsTest.RecordCount - 1
      'There are 4 records where the checkbox is checked; only the first record is processed
      Debug.Print "checked:"; rsTest.Fields("ID")  
      'Do some stuff

    rsTest.MoveNext
  Next i

'Do other stuff, close the recordset and db


In the second snippet, the .recordcount is always 1, even though if I use the exact same SQL in a query, it returns the 4 rows I am expecting. Am I missing something?
This post has been edited by B99: May 27 2020, 10:09 AM
Go to the top of the page
 
Doug Steele
post May 27 2020, 10:17 AM
Post#2


UtterAccess VIP
Posts: 22,305
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Realistically, you should never assume that the .RecordCount property is correct unless you've moved to the end of the recordset first:

CODE
  Dim db As Database
  Dim rsTest As Recordset
  Dim i As Integer
  
  Set db = CurrentDb
  
  Set rsTest = db.OpenRecordset("SELECT tblTest.ID, tblTest.Text " _
                 & "FROM tblTest " _
                 & "WHERE tblTest.DraftCheckbox = True")

  If rsTest.RecordCount > 0 Then
      
      rsTest.MoveLast
      rsTest.MoveFirst
  
      For i = 0 To rsTest.RecordCount - 1
      'There are 4 records where the checkbox is checked; only the first record is processed
          Debug.Print "checked:"; rsTest.Fields("ID")  
      'Do some stuff

        rsTest.MoveNext
      Next i

  End If
  
'Do other stuff, close the recordset and db

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
GroverParkGeorge
post May 27 2020, 10:17 AM
Post#3


UA Admin
Posts: 37,521
Joined: 20-June 02
From: Newcastle, WA


Try this to force the entire recordset to populate, which gives you the accurate recordcount for your loop.

CODE
...Code Snippet

Set rsTest = db.OpenRecordset("SELECT tblTest.ID, tblTest.Text " _
                 & "FROM tblTest " _
                 & "WHERE tblTest.DraftCheckbox = True")
    rstTest.MoveLast
    rstTest.MoveFirst
  For i = 0 To rsTest.RecordCount - 1

....Code Snippet


Which Doug already pointed out more fully. thumbup.gif
This post has been edited by GroverParkGeorge: May 27 2020, 10:18 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Doug Steele
post May 27 2020, 10:24 AM
Post#4


UtterAccess VIP
Posts: 22,305
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Just to elaborate, AFAIK, the MoveLast and MoveFirst methods won't work with an empty recordset, which is the reason why I check the value of the RecordCount property first. (I oversimplified when I said you should never assume that the RecordCount property is correct: if it returns 0, you can believe it!)

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
B99
post May 27 2020, 10:33 AM
Post#5



Posts: 62
Joined: 15-April 20



That did it! Now it's working as expected.

Is there a better / more preferred approach for looping through recordsets? I'm learning as I go and when I hit a hurdle I search for someone who had the same problem and then use the solution that was given to them...which sometimes results in some less than elegant code that I'm trying to clean up once I get a working baseline.
Go to the top of the page
 
hci_jon
post May 27 2020, 10:54 AM
Post#6



Posts: 5
Joined: 3-December 19



I usually just loop the the recordset using a While/Wend loop:

CODE
While Not rs.EOF
  'Do something here with each record
  rs.MoveNext
Wend


If you need to break out of the loop and not complete it for any reason, note that you would need to use a "Do While" loop instead.
This post has been edited by hci_jon: May 27 2020, 10:55 AM
Go to the top of the page
 
B99
post May 27 2020, 11:59 AM
Post#7



Posts: 62
Joined: 15-April 20



Ah, OK. Still learning when to use the various looping constructs. It's working now with the while not / wend.

Thanks all!!
Go to the top of the page
 
nuclear_nick
post May 27 2020, 12:14 PM
Post#8



Posts: 1,874
Joined: 5-February 06
From: Ohio, USA


Just to add to your 'looping constructs' education...

CODE
Do Until rs.EOF
  'Do something here with record
  rs.MoveNext
Loop


That is my personal favorite. There is also...

CODE
Do
  'Do something here with record
  rs.MoveNext
Loop Until rs.EOF


But that assumes it is going to loop at least once, unlike the first, but still may come in handy. Never know.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2020 - 08:50 AM