• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

record count

There are 4 records in the database but the record count in the msgbox only show 1.  I know I am missing something simple but untill I can get to a good book I am stabbing in the dark till then.  And the online help is not helping much.  So here is my play code if you could give me some guidance please.

Option Explicit
'declare database variables
Public db As Database
Public rs As Recordset
Private Sub Cancel_Click()

    'quits login and closes program
    Unload LogInForm
   
End Sub
Private Sub Form_Load()
 
    'open database
    Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\tenneco.mdb")
    Set rs = db.OpenRecordset("select pcid from workcells")
       
    'centers login in center of screen
    Me.Top = (Screen.Height - Me.Height) / 2
    Me.Left = (Screen.Width - Me.Width) / 2
       
End Sub
Private Sub LogIn_Click()

    MsgBox rs.RecordCount
       
End Sub
0
csindorf
Asked:
csindorf
1 Solution
 
MarineCommented:
Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\tenneco.mdb")
    Set rs = db.OpenRecordset("select pcid from workcells")

this will create a read only recordset . Any way you should do this.
Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\tenneco.mdb")
    Set rs = db.OpenRecordset("select pcid from workcells",dbOpenDynaset)
then you can
rs.Movelast
rs.RecordCount
msgbox rs.RecordCount
0
 
MarineCommented:
if you want to have recordset readonly then you can do the same but just replace dbOpenDynaset with dbOpenSnapshot.
GoodLuck.
0
 
SpyMasterCommented:
This is defiantly a bug because I have experienced the same problems in the past.  You need to refresh the recordset.  Do as Marine suggested but include this so that your program does not generate a runtime error if there are 0 records.

If rs.RecordCount > 0 Then rs.Movelast
MsgBox rs.RecordCount
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
MarineCommented:
spyMaster then you should take away your Answer as Proposed one. It wouldn't be fare to place an answer to which i had given  a correct answer as well.
0
 
SpyMasterCommented:
Oh sorry Marine, new to this... Registered with the service today.
0
 
MarineCommented:
oh, Welcome to EE. It's a great site for Tech Stuff. Enjoy.
0
 
tirupur_selvaCommented:
It would be even better if rs.movefirst is called after rs.movelast-this would bring back to the first position, all other things stated above by other experts would work fine.
0
 
Glen RichmondCustomer Reporting Programmer.Commented:
yes they are rite you need the DB cursor on the last record in the recordset to return the correct record count and use rs.movefirst or rs.moveprevious to return to origonal cursor position.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now