how many records in my table?

[blarson0]

Just a simple question real fast:

When viewing a form, is there a variable that stores how many records are in the table the form uses for data?

Thanks!

-Brad

 

[zevw]

Yes!!

Dim dbs as Database
Dim rst as recordset
Dim cnt

set dbs = currentdb
set rst = dbs.OpenRecordset(&quot;<Table Name>&quot

cnt = rst.RecordCount

 

[krsherm]

Well, almost. You need to move to the last record in the recordsource in order to get the proper count at least in the DAO provided by zevw. If you run that code when the form opens, it will only return the number of records already loaded... which could be 0... but it will ALWAYS be less than the total number of records... unless of course the recordcount is zero.

Try this:

Dim dbs as Database
Dim rst as recordset
Dim cnt as long '(Always strong-type your data types)

set dbs = currentdb
set rst = dbs.OpenRecordset(&quot;<Table Name>&quot
rst.MoveLast

cnt = rst.RecordCount

'Always close your object references
rst.close
set rst =nothing
dbs.close
set db = nothing

 

[blarson0]

Thanks guys, that is working great... for one form...

The other one is giving me an error:

Run-Time error 3061:
Too few parameters. Expected 1.


'Code from the gratious krsherm and zevw
Dim dbs As Database
Dim rst As Recordset
Dim cnt As Long '(Always strong-type your data types)

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(&quot;Report Q&quot
rst.MoveLast

cnt = rst.RecordCount

'Always close your object references
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing


It works when I use &quot;Everything Q&quot;, but not &quot;Report Q&quot;. I think it might be because Report Q is not editable... any suggustions?

Thanks!

-Brad

 

[blarson0]

Oh yeah, I should add that the error is happening on the line:

Set rst = dbs.OpenRecordset(&quot;Report Q&quot

-Brad

 

[Junior1544]

this has to be based on a table... not a form or report... it probably worked for your form since it may have the same name as your table... just look at the record source for the report and reference that in your
set rs
line...

--James junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[blarson0]

Thanks James.

I got it to work by doing:

Dim count As Long
DoCmd.GoToRecord , , acLast
count = Me.CurrentRecord
DoCmd.GoToRecord , , acFirst

not at all pretty, but oh well

Thanks for the help!

-Brad

 

[JoeMiller]

Another way to do it (though some frown on domain aggregate functions) is to use a DCount.

Dim MyVar
MyVar = DCount(&quot;*&quot;,&quot;MyTable&quot

HTH Joe Miller
joe.miller@flotech.net

 

[Junior1544]

i was thinking about that myself for a bit... i use it in my database, but it's kinda slow...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[zevw]

Well I want to reiterate that this works on tables

I do not use it on Forms!!

=============
rst.RecordCount
=============

 

[cmmrfrds]

When working in Access 2000 and above make sure you explicitly define the data objects since Access 2000 defaults to ADO data objects.

DAO
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
ADO
Dim rst As ADOSB.Recordset

This is a DAO statement
Set rst = dbs.OpenRecordset(&quot;Report Q&quot

 

[cmmrfrds]

When working in Access 2000 and above make sure you explicitly define the data objects since Access 2000 defaults to ADO data objects.

DAO
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
ADO
Dim rst As ADODB.Recordset

This is a DAO statement
Set rst = dbs.OpenRecordset(&quot;Report Q&quot