Returning a Query's RecordCount to VBA

[whdyck]

I'm using Access 2003.

Probably a bonehead question here: How can I run a query within VBA and capture the recordcount of the result set?

I'd like to use the CurrentDb.Execute function, but it seems to work only for "action" type queries (Inserts, Updates, or Deletes).

Thanks for any help you can give.

Wayne

 

[mdlueck]

Usually I have found that in the Recordset object containing the query results.

I suggest you place a breakpoint and watch on the object the .Execute returns and peek around inside it.

I do not use CurrentDb.Execute ever, so not sure what type of object that returns.

 

[vbaInet]

Use the DCount() function to count how many records your query returns. See here:

http://www.techonthenet.com/excel/formulas/dcount.php

 

[bob fitz]

This will give you the record count:

Dim i As Integer
i = DCount("*", "[B][COLOR=red]NameOfYourQuery[/COLOR][/B]")

Substitute NameOfYourQuery with the name of your query.