Counting records in Data Control...

[louvelle]

How do you count and add up all the records in one field of the Microsoft Access using data control?...

Situation:

I made a database using MSAccess. I made two forms. One for inputting the quantities for selling and the other one is for checking how many quantities have been sold out. So every time that you sell, the record gets save. And when I go to the second form, I will see how many things I've been sold..

[si_the_geek]

Is there a reason you are using a dreaded Data Control?

For an explanation of why it is a bad idea (and a link to the recommended alternative), see the article Why is using bound controls a bad thing? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)


Anyhoo.. to do what you want, run an SQL statement like this:
"SELECT Sum(FieldName) as MyTotal FROM tablename"

This will return just one row containing one column (called MyTotal), which will have the total in it.

[akhileshbc]

To count the total number of records in a database.. use the below code:

If data1.recordset.absoluteposition > -1 then
data1.recordset.movelast
Rec_Count=data1.recordset.recordcount 'stores the total number of records
data1.recordset.movefirst
data1.refresh
else
Rec_Count=0 ' shows empty database
msgbox "Empty"
end if

[louvelle]

Is there a reason you are using a dreaded Data Control?

For an explanation of why it is a bad idea (and a link to the recommended alternative), see the article Why is using bound controls a bad thing? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)


Anyhoo.. to do what you want, run an SQL statement like this:
"SELECT Sum(FieldName) as MyTotal FROM tablename"

This will return just one row containing one column (called MyTotal), which will have the total in it.

Well, I've been using the Data Control because it is the basic control use in database. But, somehow I heard that the ADO Data Control has a lot of functions and codes. But, I have to master the basics first before moving on to the advance...

[louvelle]

@akhileshbc

I'll try your codes at home.. I'm currently at work... Thanks!

[GaryMazzone]

What SI is saying is Do Not Use the Data Controls. Lean ADO and SQL.

[louvelle]

What SI is saying is Do Not Use the Data Controls. Lean ADO and SQL.

I'm only temporary using Data Control because I want to learn the basics of Database making. And besides, I don't want to get too much advance from our studies.