DCount a grouped value from a table

[ECEK]

Hi All
Im looking to open a form (for the purposes of checking) that counts the grouped reference number in a table's field, however I'm getting myself into a muddle.

This is what I have at the moment:

Dim strSQL As String
Dim RefGCount As Integer
strSQL = "SELECT tblClients.Ref FROM tblClients GROUP BY tblClients.Ref;"

RefGCount = DCount("Ref", strSQL)

Me.ClientCount = RefGCount

The error is saying that it can't find the table !!!!

Any takers ?

Your time and patience is greatly appreciated.

 

[JHB]

You use the function wrong, here are some sample.
https://support.office.com/en-us/article/dcount-function-f6b5d78b-ad0b-4e42-be7a-11a64acbf3d3

 

[ECEK]

The error is saying that it can't find the table !!!!

strSQL = "SELECT tblClients.Ref FROM tblClients GROUP BY tblClients.Ref;"

I have changed my function to

RefGCount = DCount("*", strSQL)

 

[Minty]

As per the link JHB supplied you have the syntax completely wrong.

DCount("Afield","YourTable","YourCriteria")

You can't whack a SQL statement in to the table portion. And even if you could how would you know which clients count you where returning?

I suspect you want something more like

RefGCount = DCount("Ref", "tblClients","ClientID = " & Me.ClientID)

Assuming your clientID is a number and is in the recordset on the form you are working on.

 

[ECEK]

What Im trying to do is count how many unique ClientRefs there are in a table and then show this on a form.

the table holds 25k Client References (with duplications)
However there are just 15k unique Client References.

I just want my form to open and to populate an unbound field with the grouped total.

My work around has been to do this qith queries but I thought that vba would be better than clogging up the queries area.

 

[Minty]

I'm not sure you can do that in a Domain function...
A recordset would do it - something like;

Dim strSQL As String
Dim RefGCount As Integer
Dim rs as Recordset

strSQL = "SELECT tblClients.Ref FROM tblClients GROUP BY tblClients.Ref;"

set rs = currentdb.openrecordset (strSQL , dbOpenSnapshot)
if not rs.eof Then 
    rs.movelast
    RefGCount = rs.recordcount
end if

rs.close
set rs = nothing

You could easily move the workings of this into a function to take any sql string and return a count, this could then be used anywhere.

 

[isladogs]

As already stated a domain function has to be based on a table or query. SQL statements can't be used

Personally I would just create a query (they take up almost zero physical space):

SELECT DISTINCT tblClients.Ref FROM tblClients;

Then run a DCount on that for your form control source

=DCount("*","YourQueryName")

 

[ECEK]

Thanks for your time and effort. I think that the query route is the safest at this stage.