Counting the records

  • Thread starter Thread starter Maracay
  • Start date Start date
M

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
 
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
 
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
 
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 said:
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
Click to expand...

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.
 
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.
 
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.
 
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
 
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.
 
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
 
Back
Top