Using the SQL COUNT function

[wcboyd]

OK, all, this is making me crazy. I am simply wanting to display the number of records in a table when some one clicks on a button. This just displays the msgbox w/o the record count. I am sure I am missing something simple. Any ideas?

Thanks in advance.

Craig

++++++++++++++++++++++++++++++++++++++++++++++++++++++

Private Sub Command0_Click()
Dim conDatabase As ADODB.Connection
Dim strSQL As String

Set conDatabase = CurrentProject.Connection

strSQL = "Select Count(*) As [NoOfCusts] From tblCustomers;"

conDatabase.Execute strSQL

Test = MsgBox("Record Count is:" & NoOfCusts & " ", vbOKOnly, "Test Box")

conDatabase.Close

Set conDatabase = Nothing

End Sub

 

[EMP]

You don't need to open the table with code. If Test is a text box on the form, just put this in the ControlSource property of the text box.

="Record Count is: " & DCount("*","tblCustomers")


The DCount() function can return the number of records in the table.

^

 

[wcboyd]

Emp,

Thanks for the reply. The DCOUNT() function will certainly prove useful to me in the future.

The code I posted is because I am trying to take a "phased" approach to what I am doing. I am new to using SQL in Access and did not want everyone to pitch in and code for me. I would not learn anything. What you see below is the result of a lot of reading and spending more than one or two hours of beating my head against the wall.

Anyway, "phase 1" is getting the record count to display in a MsgBox. In "phase 2" I will be adding a lengthy WHERE clause and preventing a record from being added if COUNT(*) > 0.

So, if you, or anyone else could tell me what in the world I am missing I would be ever so grateful!

Thanks.

 

[Bat17]

Just add your "lengthy WHERE clause " to the Dcount Function

If you are doing this just in Access I would use DAO rather than ADO. Its easier and faster.

Depending on the version of Access that you have you may need to set a reference to Microsoft DAO under Tools>References... in any module.

You can then use something like:-

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

'strSQL = "Select Count(*) As [NoOfCusts] From tblCustomers;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
MsgBox "Found " & rs![NoOfCusts]
rs.Close
Set rs = Nothing
Set db = Nothing


HTH

Peter