VBA code for counting number of records loaded in a form (1 Viewer)

brunces · Mar 7, 2007

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:

Code:

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 · Mar 7, 2007

How about:

Code:

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 · Mar 7, 2007

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

ghudson · Feb 4, 2014

RuralGuy said:
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 · Feb 4, 2014

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

Saphirah · Apr 21, 2020

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

Micron · Apr 21, 2020

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

Saphirah · Apr 21, 2020

Micron said:
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 · Apr 21, 2020

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

Code:

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 · Apr 21, 2020

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

Cronk · Apr 21, 2020

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

Pat Hartman · Apr 21, 2020

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.

Micron · Apr 22, 2020

Something wrong with what I said??

strive4peace · Apr 22, 2020

Micron said:
Something wrong with what I said??

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

Micron · Apr 22, 2020

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 · Jul 11, 2023

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

Saphirah said:
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!

VBA code for counting number of records loaded in a form (1 Viewer)

brunces

Registered User.

ByteMyzer

AWF VIP

RuralGuy

AWF VIP

ghudson

Registered User.

RuralGuy

AWF VIP

Saphirah

Active member

Micron

AWF VIP

Saphirah

Active member

strive4peace

AWF VIP

Minty

AWF VIP

Cronk

Registered User.

Pat Hartman

Super Moderator

Micron

AWF VIP

strive4peace

AWF VIP

Micron

AWF VIP

HalloweenWeed

Member

Similar threads

Users who are viewing this thread