sql count

[learning_codes]

Hi,

I try to create a box (text27) on the form and gather total number
from the query. I get an error. I hope you can help me to correct
if I did wrong.


Dim sql_count as string


sql_count = "SELECT Count(Table1.EmployeeID) AS CountOfEmployeeID FROM
Table1;"


Me.text27 = sql_count


When I run the form and the box (text27) shows "SELECT Count
(Table1.EmployeeID) AS CountOfEmployeeID FROM Table1;"

Your help would be much appreciated.

Thanks

 

[fredg]

Hi,

I try to create a box (text27) on the form and gather total number
from the query. I get an error. I hope you can help me to correct
if I did wrong.

Dim sql_count as string

sql_count = "SELECT Count(Table1.EmployeeID) AS CountOfEmployeeID FROM
Table1;"

Me.text27 = sql_count

When I run the form and the box (text27) shows "SELECT Count
(Table1.EmployeeID) AS CountOfEmployeeID FROM Table1;"

Your help would be much appreciated.

Thanks

1) A control source cannot display the results of a SQL statement.

2) All you have done is set the control source of [Text27] to a string
.... "Select Count ... etc".

3) If you just wish to count how many records are returned from a
Table or Query, all you need is, as control source of an unbound
control:

=DCount("*","TableOrQueryName")

where "TableOrQueryName" is the name of the table or query that you
wish to count records of.

 

[Steve Sanford]

Well, you can't run a form, you open a form.

You can't get the record count that way.

Try this:

' ------start of code----------------
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb

'create the select statement
sSQL = "SELECT Count(Table1.EmployeeID) AS CountOfEmployeeID"
sSQL = sSQL & " FROM Table1;"

'open recordset
Set rs = db.OpenRecordset(sSQL)

'go to the last record
rs.MoveLast

'set control to recordcount
Me.text27 = rs.RecordCount

'close recordset
rs.Close
Set rs = Nothing
Set db = Nothing
'-------------end of code ------


HTH

 

[UsenetUser]

If you mean your form recordsource is that query, then try this from
http://msdn.microsoft.com/en-us/library/aa173229(office.11).aspx

Forms!Orders.RecordsetClone.MoveLast
MsgBox "My form contains " _
& Forms!Orders.RecordsetClone.RecordCount _
& " records.", vbInformation, "Record Count"

 

[UsenetUser]

If you mean your form recordsource is that query, then try this from
http://msdn.microsoft.com/en-us/library/aa173229(office.11).aspx

Forms!Orders.RecordsetClone.MoveLast
MsgBox "My form contains " _
& Forms!Orders.RecordsetClone.RecordCount _
& " records.", vbInformation, "Record Count"