Set form field to query value count 1

[mguidry5]

Howdy,

I'm trying to set the value of an unbound field on an unbound form to a count of values returned in a query. A buddy of mine gave me the following code to use:

Function RecordCount(ByVal queryname As String) As Long
    
    Dim db As Database
    Dim rs As Recordset
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset(queryname)
    rs.MoveLast
      rs.Close
    Set rs = Nothing
    Set db = Nothing
[COLOR=green]'this returns the counter[/color]
       RecordCount = rs.RecordCount
End Function

In the immediate window, I'm getting a RecordCount of 0, when there are 28 records returned in the query. I've read some other posts that mention the use of rs.MoveFirst, but I'm not very familiar with VBA, and any good advice would be most appreciated.

Thanks in advance,

Mike

 

[MacCaillean]

Hi Mike

You are trying to calculate the number of records on a recordset that has already been closed and set to Nothing.

If you still get problems when you move your Recordcount statement, you might like to check out the VBA Help for the DCount function.

Regards

Mac

 

[mguidry5]

Thanks Mac,

I still don't quite understand how to use the RecordCount function, but I got the DCount function to work fine, and it appears much easier to implement.

Thanks again,

Mike

 

[rubbernilly]

With your original code:

Function RecordCount(ByVal queryname As String) As Long
    
    Dim db As Database
    Dim rs As Recordset
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset(queryname)
    if rs.eof then
      RecordCount = 0
    else
      rs.MoveLast
      RecordCount = rs.RecordCount
    End if
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Function