Calling

[Guest]

Access 2000
I found this function on this site by one of the MVPs, which I typed into a
form module.

Public Function GetRecordCount()
Dim rst as DAO.recordset
Dim lngCount as Long

Set rst = CurrentDb.OpenRecordset("qryHowMany")

With rst.
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With

Debug.Print "Number of Records = " & lngCount
End Function

How do I call it? I opened the Immediate Window and typed ? GetRecordCount
and got the message "Sub or function not defined."

Then I added (still in the form module)

Private Sub Assigned()
Call GetRecordCount
End Sub

Same message. Obviously, I don't know what I'm doing!

Thanks,
Howard

 

[David C. Holley]

It appears to be a snooper (my own term for code used to snoop/look
around/experiement with a database and/or code). To run the code, type
' ?getRecordCount() '
In the immediate window. To use it in code, you would simply assign the
value to a variable such as a = getRecordCount()

 

[Tim Ferguson]

How do I call it? I opened the Immediate Window and typed ?
GetRecordCount and got the message "Sub or function not defined."

Functions on forms will not be visible unless the form is running; and
even then you have to tell Access where to look:

? Form_MyForm.GetRecordCount()
37

You generally would put code on form when it's only going to be called by
a user using that form. If you need it generally, then put it in a normal
module (and don't call the modue GetRecordCount).

But isn't it quicker and easier just to do something like

MyNumber = DCount("*", "qryHowMany")

Just a quick question for whomever wrote original code:-

Set rst = CurrentDb.OpenRecordset("qryHowMany")

why open a dynaset rather than a forward only snapshot?

With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With

and what is the .MoveFirst method for?



Best wishes


Tim F

 

[Guest]

Thanks, Tim

Yes, I agree that your "quicker and easier" code is better. I'll use that.

 

[Guest]

Tim,

I did not write the code, but I think I know what the intent was, but it is
backwards.

I typically do a movelast, move first, to sync up and get a proper record
count after I first check for a 0 record count. In many cases, before moving
the current record pointer, the recordcount will come back as 1 when there
are many more than 1 record. Why, I don't know. The code in question would
error if the recordset is empty.