A SELECT COUNT(ID) query to put result in a var. in VBA

[easyc]

Hi,

I'm a bit of a newb to VBA - I've done lots of work with VBScript but not with Access and VBA. I have a question, it's a bit drawn out but please have a read...

SELECT statement result to variable:
-----------------------------------
I'd really appreciate your help if anyone can answer this:

I have a form with a text box on it, I want to display the total number of records in a table in this textbox. I've looked into several ways to do this with VBA, DoCmd.OpenQuery, CurrentDB.Execute (which I think is the correct one), as well as DoCmd.RunSQL.

The SQL I'm trying to run is simple:
"SELECT COUNT(ID) AS custs FROM tableName"

I understand that DoCmd.OpenQuery and DoCmd.RunSQL are for Actions (ie not SELECT statements) but I cant find another workaround to get the SELECT statement to run and put the result in a variable.

The result of the SELECT will be a single count value so it should be pretty straight forward.

Once I've got the result of the SELECT statement into a var I can then populate the textbox on my form. To me this sounds so simple but I cant find any examples on the web - can anyone help?

Thanks for taking time to read!
Clive

 

[DB7]

Yo Clive,
It's as easy as using a simple aggregate function, in the Control Source, of your textbox.

Your text box should be unbound. Not linked to any table.
Make sure the name of the text box, is not the same as another control on your form, or a field from the underlying table itself.

in the control source property, of your unbound field write...
"=DCount("pkID","tblName","txtCountry Like 'A*'")"

pkID is the name of a field , you want to count records for. If you want to count ALL records, use the primary key, because any null values for the field you specify, will not be included int the count.

tblName is the name of your table

txtCountry Like... is the search criteria. NOT REQUIRED.
If you want ALL records, leave this blank. (don't include comma)..
=DCount("pkID","tblName")

Do a lookup in VBA help for aggregate functions, or specifically DCount.

Good luck!

 

[easyc]

Sorted!

Many thanks DB7 - the =DCount("pk","tableName") worked a treat.

Spot on, thanks
Clive