count rows 1

[jamesmills]

simple i am sure but not for somone who does not know too much about access at moment....

i would like to count how many records there are in a table and then display this in a lable

 

[jdegeorge]

James

We meet again! What I do is look at the table and see which field is ALWAYS populated and then count on that field to a variable.

VARIABLE = Count([FIELD])

Hope this helps.

Jim DeGeorge

 

[jdegeorge]

Forgot something. Is this label on a form or a report?

Jim DeGeorge

 

[jamesmills]

table on a form....i have used an ID field for all my tables so i can use that....great thanks....will give it a shot

 

[jdegeorge]

Don't waste your time, it won't work. I'm sure there's a better way, but what I do is take the table and query it counting the one field and store it to a temptable. Then, I base the form on the real table and the temp table, but don't link any fields in the SQL. What that does is add the counted number to every record, but it gets the desired result.

Jim DeGeorge

 

[jamesmills]

hummm so if i was to place a txt box on form and give it default of this '=Count([tblCustomers].[ID])'

why will it not work?

 

[jdegeorge]

A form has focus on only 1 record at a time, so the result should be 1 or at least not your total. Access needs to run through the table to get the count, or even maybe some code behind your form that does the counting. Try it and you'll see that it won't work. I'll keep thinking about the SQL.

Jim DeGeorge

 

[jamesmills]

ahhh right i see.... so there has to be sql code on the form load event to count the rows in teh table and then assign that value to the lable.....?

 

[jdegeorge]

Yes. I'm trying to figure that out now. The basic concept is that on the form's OPEN property you have to get Access to run through the table and count that field, store it to a variable and get that variable to be viewed on your form or report. Did you ever do anything like this?

Jim DeGeorge

 

[jamesmills]

well yer i know what has to be done but i just done know the sql for count!....looking

 

[jamesmills]

ok so the sql we need is:
SELECT COUNT(*) FROM tblName

however i now have no idea what else i need to add to the VD Code in the form load setion?

Need to run the sql and then assign the result to a srting or somthing?

 

[jdegeorge]

You're on the right track. You need the OpenRecordSet function. I'm on a conference call right now, but check that out in help for the context, etc.

Jim DeGeorge

 

[jamesmills]

OK well enjoy your call....lol...can not get anything to work

Dim rs As Recordset
Dim finalDate As String

Set rs = CurrentDb.OpenRecordset("SELECT COUNT(*) FROM tblCustomers&quot
finalCount = rs
rs.Close

lblTotalCustomers.Caption = finalCount

 

[WildHare]

Jim, you might want to check out the DCOUNT function..it's the bee's knees for this kind of thing...

MyTableRecCount = Dcount("KeyField", "MyTable&quot


Make appropriate substitutions for YOUR field and table names, and you'll be off to the races. And don't bother listening to anyone who chimes in with "Domain functions are hogs and take too many CPU cycles..et et et cetera)

Chances are, even your lowly P3/250mHz has cycles to spare...I know mine does.

Jim



If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[jdegeorge]

Jim (WildHare)

Too many Jim's here...

Any who, I tried this on the form's open property:

[tt]Private Sub Form_Open(Cancel As Integer)

Dim finalCount As Integer

finalCount = MyTableRecCount = DCount("ID", "tblCustomers&quot

lblTotalCustomers.Caption = finalCount + 1


End Sub[/tt]

I added the "+ 1" part because the value was coming up one short each time.

Nice going! Enjoy the star.

Jim DeGeorge

 

[WildHare]

I added the "+ 1" part because the value was coming up one short each time.

Hmmmm..I was not aware that DCOUNT truncated in a reverse manner from its' E-O-F travels...

I ALWAYS use a key to DCOUNT on - it's the only way you're assured of getting an acurate...er, accuritte...er..correct count.

(The other) Jim


If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[jdegeorge]

Are you saying that if this was tied to an .EOF syntax somewhere, it would have returned the correct record count without my having to add 1? What would that code look like?

Jim DeGeorge

 

[WildHare]

No, what I meant was that the DCOUNT function should return an "accurate" count of records, depending on critiera, not ONE LESS than actual. Thus, I can only believe that the field you are using has one NULL value in the table.. DCOUNT does not count NULLS.

DCount("*", "TableName") will always return a correct count of the actual number of records in a table.

JMH




If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[jamesmills]

Private Sub Form_Load()


Dim finalCount As Integer

finalCount = MyTableRecCount = DCount("ID", "tblCustomers&quot

lblTotalCustomers.Caption = finalCount
End Sub


So why will this not work....grrrrr?

 

[jdegeorge]

Put it in Sub for OPEN not Load. Maybe that's the difference? It worked for me in OPEN.

Jim DeGeorge