VBA code for counting number of records loaded in a form

[brunces]

Friends,

Please, how can I create a VBA code which counts the number of records loaded in a form?

In a form (TextBox), I can use ... Count(*) ... but when it comes to VBA code, this doesn't work.

I also cannot use ... DCount("*", "Table") ... because this form is opened from a button in another form which contains filters, so that DCount wouldn't bring me the exact number of loaded records only.

So, what I really need is something like this:

Private Sub MyButton_Click()

   Dim varTotal As Integer
   varTotal = (Code for counting the number of records loaded in this form)
   MsgBox "Number of loaded records: " & varTotal

End Sub

I hope you can help me. Thank you very much for your attention.

Hugs,

Bruno

 

[ByteMyzer]

How about:

Private Sub MyButton_Click()

   Dim varTotal As Integer
   varTotal = [b][i]Me.RecordsetClone.RecordCount[/i][/b]
   MsgBox "Number of loaded records: " & varTotal

End Sub

 

[RuralGuy]

You should probably do:
Me.RecordsetClone.MoveLast
before:
varTotal = Me.RecordsetClone.RecordCount
The RecordCount is not guaranteed accurate until the .MoveLast.

 

[ghudson]

You should probably do:
Me.RecordsetClone.MoveLast
before:
varTotal = Me.RecordsetClone.RecordCount
The RecordCount is not guaranteed accurate until the .MoveLast.

Thank you RuralGuy! That has been quirking me until I found your Me.RecordsetClone.MoveLast suggestion to fix a form that would always give me a total record count of 1 in my custom X of Y counter using Me.RecordsetClone.RecordCount when the form is first opened. I cannot use DoCmd.RunCommand acCmdRecordsGoToLast and DoCmd.RunCommand acCmdRecordsGoToFirst because of other triggers I have in the OnOpen event. Thanks for making me day!

 

[RuralGuy]

You're certainly very welcome and welcome back. I have not seen you posting in a long while.

 

[Saphirah]

An easier approach for this case without a recordset would be DCount("*","tbl_Of_Desire", Me.Filter)

 

[Deleted member 147267]

Saphirah, just wondering if you noticed that the last post before yours was over six years ago?

 

[Saphirah]

Saphirah, just wondering if you noticed that the last post before yours was over six years ago?

Yeah, but i noticed this was the first result on google, so i wanted to add it after i found a cleaner solution
It is not that i wanted to "help the author", but everyone who will stumble upon this in the future

 

[strive4peace]

Saphirah , Welcome! Good you want to share.

This might be even better: put a textbox to count in the form header or footer (visible can be No if you don't want it to show) ... then the message could reference it

dim sMsg as string
sMsg = "Number of loaded records: " & me.controlname_countRecords
MsgBox sMsg,,"Number of records"

where
controlname_countRecords is the Name property of the control with Count(*)

 

[Minty]

I can't check, but if the form is opened with a where clause rather than a filter this approach might not work?

 

[Cronk]

Why not turn the form's navigation buttons on? That will display change if a filter is applied.

 

[Pat Hartman]

I'm with Cronk. I don't feel the need to write code to produce objects that Access has built in. So, unless the customer insists on spending money to have me code something he can get for free, I use Access objects in all cases. Once the label got added in 2007 (my suggestion), that eliminated the confusion if there were nested forms and all had scroll bars.

 

[Deleted member 147267]

Something wrong with what I said??

 

[strive4peace]

Something wrong with what I said??

not at all! Just that you're right ...

 

[Deleted member 147267]

I see. It's just that a 'wow' emoji would be something I'd use that's akin to "are you kidding?" or "you really believe that/would say that?" I was concerned you thought my comment was out of place.
Thanks.

 

[HalloweenWeed]

An easier approach for this case without a recordset would be DCount("*","tbl_Of_Desire", Me.Filter)

Yeah, but i noticed this was the first result on google, so i wanted to add it after i found a cleaner solution
It is not that i wanted to "help the author", but everyone who will stumble upon this in the future

Yeah thanx, appreciated!