Q:why recordCount with query give 1 (1 Viewer)

iroyal2016

New member
Local time
Today, 01:52
Joined
Apr 18, 2016
Messages
8
Hi, I'm new in this forum and welcome all
I have a small question :

Code:
Public Sub getCount()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Employees.Company, Employees.[Last Name] FROM Employees;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

With rst
    Debug.Print .RecordCount
End With
End Sub

The immediate window gives me :
1

what is wrong with my code ?
I have 3 records in that query.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:52
Joined
Sep 21, 2011
Messages
14,050
Try .movelast then .movefirst then display the record count

I do not believe the .movefirst is required if all you want is the count, but if you were to process that recordset, then you would want it.
 

ashleedawg

"Here for a good time"
Local time
Today, 01:52
Joined
Jun 22, 2017
Messages
154
...also don't forget to .close the recordset at the end of the procedure. This page has some good starter tips for working with VBA recordsets (#12 re: closing and #4 re: what Gasman said).
 

iroyal2016

New member
Local time
Today, 01:52
Joined
Apr 18, 2016
Messages
8
I tried to fix my Code :
Code:
Public Sub getCount()

Dim db As Database
Dim rst As Recordset
Dim x As String
Dim strSQL As String

strSQL = "SELECT Employees.Company, Employees.[Last Name] FROM Employees;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
With rst
    Debug.Print .RecordCount
    .Close
End With
End Sub

but still it's give (1)
 

iroyal2016

New member
Local time
Today, 01:52
Joined
Apr 18, 2016
Messages
8
Try .movelast then .movefirst then display the record count

I do not believe the .movefirst is required if all you want is the count, but if you were to process that recordset, then you would want it.

maybe it will work, but I gotta understand why this code doesn't work with me
even I watch the video and follow all the steps one by one.

I know that I can get count record in another way, but I want this code work.

thanks
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:52
Joined
Sep 21, 2011
Messages
14,050
That is because you took note of Ashleedawg's post, but not mine?

I tried to fix my Code :
Code:
Public Sub getCount()

Dim db As Database
Dim rst As Recordset
Dim x As String
Dim strSQL As String

strSQL = "SELECT Employees.Company, Employees.[Last Name] FROM Employees;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
With rst
    Debug.Print .RecordCount
    .Close
End With
End Sub
but still it's give (1)
 

isladogs

MVP / VIP
Local time
Today, 08:52
Joined
Jan 14, 2017
Messages
18,186
The changes listed by Gasman & Ashleedawg do work correctly
Did you specify DAO.Database?
Did you reset rst to nothing at the end?

Try this minor modification:

Code:
Public Sub getCount()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Employees.* FROM Employees;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

With rst
    .MoveLast
    '.MoveFirst 'not needed for this purpose alone
    Debug.Print .RecordCount
    .Close
End With

Set rst=Nothing

End Sub
 

missinglinq

AWF VIP
Local time
Today, 04:52
Joined
Jun 20, 2003
Messages
6,423
maybe it will work, but I gotta understand why this code doesn't work with me even I watch the video and follow all the steps one by one.

Not to be unkind, but the fact that you 'tried to fix my code,' but totally ignored Gasman's advice to use .movelast then .movefirst before displaying the record count, would indicate that you're not being careful when following 'all the steps one by one.'

MS Access is not the same as playing horseshoes...being close doesn't count...you have to always be exact!

To answer the why your original code wasn't working...when trying to display the recordcount of a recordset, you have to first move to the end of the recordset. The reason for this is that Access doesn't always load the entire recordset when loading a Form. Using .MoveLast ensures that this is done, giving you the correct count.

Linq ;0)>
 

iroyal2016

New member
Local time
Today, 01:52
Joined
Apr 18, 2016
Messages
8
The changes listed by Gasman & Ashleedawg do work correctly
Did you specify DAO.Database?
Did you reset rst to nothing at the end?

Try this minor modification:

Code:
Public Sub getCount()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Employees.* FROM Employees;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

With rst
    .MoveLast
    '.MoveFirst 'not needed for this purpose alone
    Debug.Print .RecordCount
    .Close
End With

Set rst=Nothing

End Sub



thanks it works
 

iroyal2016

New member
Local time
Today, 01:52
Joined
Apr 18, 2016
Messages
8
Not to be unkind, but the fact that you 'tried to fix my code,' but totally ignored Gasman's advice to use .movelast then .movefirst before displaying the record count, would indicate that you're not being careful when following 'all the steps one by one.'

MS Access is not the same as playing horseshoes...being close doesn't count...you have to always be exact!

To answer the why your original code wasn't working...when trying to display the recordcount of a recordset, you have to first move to the end of the recordset. The reason for this is that Access doesn't always load the entire recordset when loading a Form. Using .MoveLast ensures that this is done, giving you the correct count.

Linq ;0)>


but I gotta say something:-
FIRST: I am new here.
Second: I didn't ignore any one, but sometimes I couldn't understand.
Third: I did follow all steps in the video step by step

Last: my goal was to get the result without any editing because I've seen it in the video work perfectly good., that is all.

thanks everyone .. it [SOLVED]
 

Users who are viewing this thread

Top Bottom