• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 RichmondCommented:
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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