Simple: Count number of Records (2 Viewers)

[e2cheng]

I'm trying to count the number of records in a query, form and report. Theoretically, the method should be identical. But I'm having major issues sorting this out. I've tried searchin gprevious forum, but don't really understand how to implement things such as the

.count property
Recordset..... property
currentrecord.count property
etc.

just really confused. I also found this thing on the MS help menu that gave me step by step instructions for displaying this in a text box. only problem, they wanted me to set the RunningCount property to something.. i forgot, just confused now...

Thank you in advance for any help! very much appreciated

 

[sambo]

Dim db As DAO.Database, rst As DAO.Recordset, recNum As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("YourTableQueryName", vbOpenDynaSet)

rst.MoveLast
rst.MoveFirst  'Populate the recordset
recNum = rst.RecordCount  'count the Records
MsgBox recNum

 

[e2cheng]

sambo to the rescue again!

hi sambo,

thank you very much for your reply again... i tried just cutting and pasting and i'm getting this error msg..

method or data member not found. and it's refering to the rst.MoveLast record.. I'm not sure what that's supposed to mean, am i missing a library somewhere???

all i did was replace your "YourTableQueryName" to the name of my current form. i'm using this code on the form oload you think that might be a problem as well??

thanx again!

eric

 

[sambo]

A form is not a Recordset..
Recordsets are either queries or tables.

If you want to use your form's recordset try this...

Set rst = Forms!YourFormName.Form.RecordsetClone

Instead of this..

Set rst = db.OpenRecordset("YourTableQueryName", vbOpenDynaSet)

 

[e2cheng]

ok, i've tried changing that line of code and putting in my form name there. but i still get the same error.

so i went back tot eh original and ran the code with it referencing a query, but i still get that error...

not sure what's happening in this function...

thank you for your patience and help

 

[sambo]

Go to Tools->References...
Make sure DAO 3.6 is checked.
This allows you to use the DAO library

 

[e2cheng]

yep MS DAO 3.6 Obj Lib is checked...

 

[sambo]

Whoops..

Put in dbOpenDynaSet instead of vbOpenDynaSet

 

[Mile-O]

Just one thing I noticed in Sambo's code example.

Dim db As DAO.Database, rst As DAO.Database, recNum As Integer

You have rst dimensioned as a database and not a recordset.

 

[sambo]

DOH!!

That would probably do it too...
I'll fix that for future viewers.

 

[e2cheng]

thank you all!

thank you all for your help. i get it.. much appreciated!

 

[sambo]

Sorry about the fat fingers..

 

[e2cheng]

referencing

hi, sorry to bother again, but let's say i put the code in a function

Public Function recNum(objectName As String) As Integer
    Dim db As DAO.Database, rst As Recordset

    Set db = CurrentDb
    'Set rst = db.OpenRecordset("qryDynamic_QBF", dbOpenDynaset)

'*************************************
    Set rst = objectName & .Form.RecordsetClone
'*************************************
    rst.MoveLast
    rst.MoveFirst  'Populate the recordset
    recNum = rst.RecordCount  'count the Records
End Function

what is the proper way to attach my objectName variable in front of .Form.RecordsetClone .

Thank you once again for your patience..

Sincerely,
Eric

 

[sambo]

Currently, you have it set up to only return the recordset from a form (not tables or queries), so why don't you just change your parameter from an object to a form data type.

Instead of..
Public Function recNum(objectName As String) As Integer

Try..
Public Function recNum(frm As Form) As Integer

Set rst = frm.Form.RecordsetClone


Here is the new Function Call...
tempNum = recNum(Forms!YourForm)
MsgBox tempNum

 

[e2cheng]

hi sambo,

thank you so much again. don't know how much you've helped! passing in a form, ingenius... i will have to explore more.. but for now, it's time to get some rest.

extremely grateful,
eric