Counting the number of records of a report in VBA 1

[Bresart]

Hi, i have a report 'Glossary' and need to show the entries number in the page header of the report.

It works putting a textfield called text1 in the first header with its control source property set to

=Contar([mainField]) in english versions: =Count(..

and in the textfield text2 in the header:

=[text1].Valor in english versions: =[text1].Value



but i don't want to depend on this expressions because they are different in different idiom versions of Access, and that can give problems.

What i usually do in these cases is to call to a function in an independent module, in this way:

in the controlsource property of the text2 texfield: =ReadRecordsNumber()


and in the Module1:

Function ReadRecordsNumber() as Integer
ReadRecordsNumber = Reports!reporName.RecordsetClone.Recordcount

End Function


But this line into the function doesn't work, it works in forms and perhaps it's forms specific.

Which would be the correct line in ths case, for the report, in order to replace this line in the external function?

Thanks for any help.

 

[Remou]

I do not think you can use RecordsetClone with a report. How about using DCount and the query that the report is based on?

 

[Bresart]

Thanks Remou.

I don't understand what you exactly mean,

- adding a new field in the query that the report is based on which Totals argument be Dcount, or

- using the expression Dcount with a concrete field of the query that the report is based on in a calculated textbox.

I thing it's the first one, isn't it?

 

[Bresart]

I have made it by doing from the textbox text2, in its controlsource propery, a calling to a function:

=ReadRecordsNumber()



placed on an independent module:


Function ReadRecordsNumber() As Integer

strQuery = "SELECT Count(tblGlosario.termino) AS CuentaDetermino " & _
"FROM tblGlosario;"

Set rrdset = New ADODB.Recordset
rrdset.Open strQuery, CurrentProject.Connection, _
adOpenStatic, , adCmdUnknown

ReadRecordsNumber = rrdset!CuentaDetermino

rrdset.Close

End Function




Perhaps it's what you meant.