Use Dcount in Access VBA

If you want to use the Dcount in VBA.

Download Sample Database

Here is the code-

Sub use_dcount()
Dim sumfield As String
Dim tblname As String
countrecords = "sales"
tblname = "sales_detail"
MsgBox DCount(countrecords, tblname, "[Rep Name] = 'a'"), vbOKOnly
' with query
Dim SQLresult As String
Dim rs As Recordset
SQLresult = "SELECT COUNT([sales_detail].sales) AS Filtersales FROM [sales_detail] WHERE ([sales_detail].[rep name]='a')"
Set rs = CurrentDb.OpenRecordset(SQLresult)
MsgBox rs(0), vbInformation
End Sub