Counting the records

[Maracay]

Hi guys

I want to create a text box containing the numbers of records on my record
set, but I don’t know what instruction to put on my control source.

Thanks

 

[Mark]

I reckon you need some VBA here. A simple function should do the trick.
Remember, a Function typically returns something - in your case the count of
records in a table (recordset).

Here's an example that I have done (today)!!

Function NoCustNotes() As Integer
'returns the total number of Customer Notes
Dim mySQL As String
Dim myDB As DAO.Database
Dim mySet As DAO.Recordset

varSQL = "Select * from tblCustomerNotes"

Set myDB = CurrentDb
Set mySet = myDB.OpenRecordset(varSQL)

If mySet.BOF = True Then
'there are no contact - so return 0
NoCustNotes = 0
Else
mySet.MoveLast
NoCustNotes = mySet.RecordCount
End If
End Function


you then display the results in a text box - the text box will contain a
control source property of =NoCustNotes() - i.e. the function name

Hope that helps

 

[Tom van Stiphout]

On Mon, 16 Feb 2009 06:47:20 -0800, Maracay

Do you mean you want to display the count of records of the recordset
that the form is bound to? That's already displayed next to the
navigation buttons? If so, you can use:
=Me.RecordsetClone.RecordCount

-Tom.
Microsoft Access MVP

 

[Mark]

hey that it - no need for the VBA coding I mentioned in an earlier reply

 

[Maracay]

Hi guys

For some reason =Me.RecordsetClone.RecordCount is not working, the textbox
is in the header form, and the form is a continuous form maybe is that the
reason why is not working?

Thanks

 

[Linq Adams via AccessMonster.com]

That shouldn't make any difference, but for some reason that I've never been
able to pinpoint, sometimes (but not always) you have to move to the end of
the recordset then back to the beginning before Me.RecordsetClone.RecordCount
will work properly:

Private Sub Form_Load()
DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acFirst
End Sub

 

[Mike Painter]

That shouldn't make any difference, but for some reason that I've
never been able to pinpoint, sometimes (but not always) you have to
move to the end of the recordset then back to the beginning before
Me.RecordsetClone.RecordCount will work properly:

Private Sub Form_Load()
DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acFirst
End Sub

I've wondered about this.
If it was a simple as adding .movelast and .movefirst to the code that
returns a count you would think they would have done it by now.

 

[Dale Fye]

I'm assuming that you have disabled the navigation buttons, and are using the
scroll bar or some other mechanism to move between records.

Where did you put this code, or did you put it in the control source of a
textbox?

Whenever I disable the navigation buttons, and want to display the number of
records, I put some code in either the forms load event, or the current event
(if I want to know which record I'm on), and I generally use a label, rather
than a textbox to display the data.

Private Sub Form_Load

me.lbl_RecCount.Caption = me.recordsetclone.recordcount & " records"

end sub

Private Sub Form_Current

me.lbl_RecCount.caption = me.currentrecord & " of " _
& me.recordsetclone.recordcount

End sub

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[Linq Adams via AccessMonster.com]

As I said, Mike, sometimes it works without moving to last then back to first,
and sometimes it doesn't! I've never been able to figure out why it's a
sometime thing!

I actually use code similar to Dale's, utilizing the Form_Load and
Form_Current events, except I use

Me.Caption = Me.CurrentRecord & " of " & Me.RecordsetClone.Recordcount

which places the information in the form's Title Bar.

 

[efandango]

Dale, using your code, I have this:

Private Sub Form_Current()
Me.txt_RecordCount = Me.CurrentRecord & " of " & Me.RecordsetClone.RecordCount
End Sub

How can I adjust the code to give me:

Record 1 of 2 (or more)

at the moment it only gives me 1 of 1.

This is on a subform with a Parent/Child setup where there can be more than
one record in the subform.

 

[Beetle]

Try moving throught the RecordsetClone first to get an accurate
record count.

Private Sub Form_Current()

With Me.RecordsetClone
.MoveLast
.MoveFirst
Me.txt_RecordCount = Me.CurrentRecord & " of " & .RecordCount
End Sub

 

[efandango]

I get 'No Current Record' Error when there is no sub record for a particular
record.

Some of my Parent records do not have child records.

 

[Beetle]

Try this;

Private Sub Form_Current()

With Me.RecordsetClone
If Not .EOF Then
.MoveLast
.MoveFirst
Me.txt_RecordCount = Me.CurrentRecord & " of " & .RecordCount
End If
End Sub