Number of Records in text box?

[deagio]

Hi,

I was just wondering if it is possible to display the number of records on a form in a text box.

Thanks!

James

 

[trucktime]

Records

In the text box put:

=Count(*)

Or a little bit fancier: =Count(*) & " Records"

To display X of Y Records:

Put a textbox named 'txtCurrent' on the form.
The following code goes in the ON Current Event of the form.

*****************************
Private Sub Form_Current()
Dim sCurrent As Integer
Dim sTotal As Integer

sCurrent = Me.CurrentRecord
Me.RecordsetClone.MoveLast
sTotal = Me.RecordsetClone.RecordCount

If sCurrent > sTotal Then
sTotal = sTotal + 1
Else
End If

Me!txtCurrent = "Record " & sCurrent & " of " & sTotal

'Me!txtTotal = Me.RecordsetClone.RecordCount
End Sub
*****************************

Upon going to a new record the total increases by 1, although the
record has not been saved yet, this is to prevent of txtCurrent
showing something like: Record 10 of 9.

 

[deagio]

Record Number

Hi,

Thanks for that! I'm not quite sure where to paste the =Count(*) & " Records"
in the text box properties, I've tried the 'Default Value' and 'Control Source'.

Or should i paste it somwhere else?

Thanks again,

James

 

[ghudson]

If you want just the total number of records displayed in a text box then use the DCount function.
=DCount("*", "YourTableName") Check the help files for more info on using DCount.

If you want to display the record X of Y info in a label then try this...

Private Sub Form_Current()

If Me.NewRecord Then
Me.lRecordXofY.Caption = "New Record (" & DCount("*", "TableA") & " existing records)"
Else
Me!lRecordXofY.Caption = "Record " & [CurrentRecord] & " of " & DCount("*", "TableA")
End If
End Sub

The above is using a label named "lRecordXofY" and a table named "TableA".

 

[Lisad]

Number of records on a subform

I want to do a similar thing. I have a subform (Quotes) which pulls in all the quote information for each person in the main form (People). How can I display the number of records in just the subform, in a text box? I don't want all the records in the Quotes Table to be counted, just those for the person selected in the main form.

 

[Rich]

=Count(*) in the subform footer

 

[Lisad]

Run time error

Thanks for that. I actually used Trucktime's solution to display 1 Record of 4, however when I get to a record that does not have any subform record entries, I get a Run time error relating to the Me.RecordsetClone.MoveLast entry. What can I do to stop this?

 

[ghudson]

Try the DCount() function in your subform. Search the Access VBA help files for the DCount() function for more info and examples on how to use it with the main form to count only the records related to the main form.

 

[Lisad]

I just removed the Me.RecordsetClone.MoveLast as I read somewhere that it wasn't needed. This sorted out my Run time error.

HOWEVER.... now when I navigate to the next record in my main form, the Record Counts on both of my Subforms do not update with the accurate record number. If I move thru the records on the subforms themselves, the record count then displays the correct 'Record X of Y'. How can I sort this? (Sorry I am a beginner at all this!)

 

[ghudson]

Call the code in the OnCurrent event. You might have to requery the subform from the main forms OnCurrent event. Check the Access help files on how to correctly "requery" an object.

 

[Lisad]

Main form = Lead List
Subform = Quotes

I have had no joy. On my main form I have created an OnCurrent Event as detailed below. This gives me a syntax error and highlights the Private Sub Form_Current() line.

Private Sub Form_Current()
Forms!Lead List!Quotes.Form.Requery
End Sub

Any ideas? This is driving me mad!

 

[Mile-O]

Private Sub Form_Current()
Forms!Lead List!Quotes.Form.Requery
End Sub

Me.Quotes.Requery

Bear in mind that you need to use the name of the subform object and not the subform form.

 

[Lisad]

I tried this, but the Record count text box in my subform still does not update when I move to the next main record (in main form).

Any ideas?

 

[Rich]

You should Not have a Requery statement in any Form Current event, your form will go into an endless loop

 

[Lisad]

What should I do instead?