How do I add a record counter to my form

G

gagecres

I would like to add my own record counter, like "Record # of #", to my form.
How can I do this?
 
L

Linq Adams via AccessMonster.com

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


Private Sub Form_Current()
Me.Caption = "Record " & CurrentRecord & " Of " &
RecordsetClone.RecordCount & " Records"
End Sub

This code places the info in the form's title area. To place it in a Label,
substitute

Me.LableName.Caption

for

Me.Caption.

To place it in a TextBox, substitute

Me.TextBoxName.Value

for

Me.Caption.
 
L

Linq Adams via AccessMonster.com

Sorry, meant to add this caveat:

This only displays the position of a record within the RecordSource of the
form. It has nothing to do with its position within the underlying table.
Additions and deletions will change this, assuming the RecordSourse is
requeried after each of these is done.
 
G

gagecres

This worked great! Thanks a lot. One question though. I get an error on
the "DoCmd.GoToRecord , , acNext" line when I first open the form. I assume
this is because there are no records in the database yet. How do I fix this?
 
G

gagecres

I also noticed that the total records number always starts out as 1, even if
I have more than 1 record in the subform associated with the current record
on the parent form. For example, it says "Record 1 of 1" even though there
are a total of 3 records. However, when I go to the next record in the
subform it updates to the correct number of total records (i.e. "Record 1 of
3"). How do I get it to say "Record 1 of 3" for the subform record when I
first get to the parent record?
 
G

Gina Whipp

gagecres,

I use on the On_Current event of the form...

With Me.RecordsetClone
.MoveLast
Me.txtPage = Me.CurrentRecord & " of " & .RecordCount & " line(s)"
End With

Make sure to place a text box on your form names... *txtPage*
--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I also noticed that the total records number always starts out as 1, even if
I have more than 1 record in the subform associated with the current record
on the parent form. For example, it says "Record 1 of 1" even though there
are a total of 3 records. However, when I go to the next record in the
subform it updates to the correct number of total records (i.e. "Record 1 of
3"). How do I get it to say "Record 1 of 3" for the subform record when I
first get to the parent record?
 
G

Gina Whipp

gagecres,

Why are you placing *DoCmd.GoToRecord...* on the On_Load event?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

This worked great! Thanks a lot. One question though. I get an error on
the "DoCmd.GoToRecord , , acNext" line when I first open the form. I assume
this is because there are no records in the database yet. How do I fix
this?
 
G

gagecres

I still get an error saying: "No current record." When I debug it, it
highlights ".MoveLast".
 
G

Gina Whipp

Please post the exactly the code you are using here... And if you have no
reason for the DoCmd.GoToRecord lines, remove them from the On_Load event.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

I still get an error saying: "No current record." When I debug it, it
highlights ".MoveLast".
 
L

Linq Adams via AccessMonster.com

Sorry, the line

DoCmd.GoToRecord , , acNext

was a typo. Should have read

DoCmd.GoToRecord , , acLast

To account for no records being in the recordsource, try this revision

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

Private Sub Form_Current()

Dim intX As Integer

intX = DCount("*", "TableOrQueryName")
If intX < 1 Then
Me.Caption = "There Are No Records"
Else
Me.Caption = "Record " & CurrentRecord & " Of " & RecordsetClone.
RecordCount & " Records"
End If

End Sub
 
G

gagecres

Here is the code I am using:

With Me.RecordsetClone
.MoveLast
Me.txt_Rec_Count.Value = Me.CurrentRecord & " of " & .RecordCount &
" line(s)"
End With

Also, I commented out the OnLoad lines
 
G

gagecres

It is still not showing the correct total number. Here is my code:

Private Sub Form_Current()
intX = DCount("*", "Heirs Table")

If intX < 1 Then
Me.txt_Rec_Count.Value = "0"
Else
Me.txt_Rec_Count.Value = "Heir # " & CurrentRecord & " of " &
RecordsetClone.RecordCount
End If
End Sub

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

Gina Whipp

Is me txtRec_Count a numeric field? OR just and unbound text box? Please
remove *.Value* Is this a form or subform and does it have any records?

What message if any do you get?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Here is the code I am using:

With Me.RecordsetClone
.MoveLast
Me.txt_Rec_Count.Value = Me.CurrentRecord & " of " & .RecordCount &
" line(s)"
End With

Also, I commented out the OnLoad lines
 
B

BruceM via AccessMonster.com

Try checking the RecordCount of RecordsetClone, and breaking up the code a
little:

Debug.Print Me.RecordsetClone.RecordCount

Me.RecordsetClone.MoveLast
Me.txt_Rec_Count = Me.CurrentRecord & " of " & Me.RecordsetClone.RecordCount
& " line(s)"

Rather than using the Load event, perhaps something like this:

Dim lngCount as Long
Dim strCount as String

lngCount = Me.RecordsetClone.RecordCount

If lngCount = 0 Then
strCount = "First Record"
Else
strCount = Me.CurrentRecord & " of " & Me.RecordsetClone.RecordCount & "
line(s)"
End If

Me.txt_Rec_Count = strCount
Here is the code I am using:

With Me.RecordsetClone
.MoveLast
Me.txt_Rec_Count.Value = Me.CurrentRecord & " of " & .RecordCount &
" line(s)"
End With

Also, I commented out the OnLoad lines
Please post the exactly the code you are using here... And if you have no
reason for the DoCmd.GoToRecord lines, remove them from the On_Load event.
[quoted text clipped - 60 lines]
 
G

gagecres

txt_Rec_Count is an unbound text box. I removed ".Value" and still got the
same No Current Record error. The form is a subform and yes it does have
records in it.
 
G

gagecres

This did the trick. Thanks a lot!

BruceM via AccessMonster.com said:
Try checking the RecordCount of RecordsetClone, and breaking up the code a
little:

Debug.Print Me.RecordsetClone.RecordCount

Me.RecordsetClone.MoveLast
Me.txt_Rec_Count = Me.CurrentRecord & " of " & Me.RecordsetClone.RecordCount
& " line(s)"

Rather than using the Load event, perhaps something like this:

Dim lngCount as Long
Dim strCount as String

lngCount = Me.RecordsetClone.RecordCount

If lngCount = 0 Then
strCount = "First Record"
Else
strCount = Me.CurrentRecord & " of " & Me.RecordsetClone.RecordCount & "
line(s)"
End If

Me.txt_Rec_Count = strCount
Here is the code I am using:

With Me.RecordsetClone
.MoveLast
Me.txt_Rec_Count.Value = Me.CurrentRecord & " of " & .RecordCount &
" line(s)"
End With

Also, I commented out the OnLoad lines
Please post the exactly the code you are using here... And if you have no
reason for the DoCmd.GoToRecord lines, remove them from the On_Load event.
[quoted text clipped - 60 lines]

--



.
 
G

gagecres

Spoke too soon.

gagecres said:
This did the trick. Thanks a lot!

BruceM via AccessMonster.com said:
Try checking the RecordCount of RecordsetClone, and breaking up the code a
little:

Debug.Print Me.RecordsetClone.RecordCount

Me.RecordsetClone.MoveLast
Me.txt_Rec_Count = Me.CurrentRecord & " of " & Me.RecordsetClone.RecordCount
& " line(s)"

Rather than using the Load event, perhaps something like this:

Dim lngCount as Long
Dim strCount as String

lngCount = Me.RecordsetClone.RecordCount

If lngCount = 0 Then
strCount = "First Record"
Else
strCount = Me.CurrentRecord & " of " & Me.RecordsetClone.RecordCount & "
line(s)"
End If

Me.txt_Rec_Count = strCount
Here is the code I am using:

With Me.RecordsetClone
.MoveLast
Me.txt_Rec_Count.Value = Me.CurrentRecord & " of " & .RecordCount &
" line(s)"
End With

Also, I commented out the OnLoad lines

Please post the exactly the code you are using here... And if you have no
reason for the DoCmd.GoToRecord lines, remove them from the On_Load event.
[quoted text clipped - 60 lines]

.

--



.
 
G

gagecres

The code you gave me works fine if the first parent record that appears on
the form has records in the subform. If that first parent record has no
records in the subform, that's when I get the "No Current Records" error as
soon as I open the database and the debugger highlights the ".MoveLast"
statement.
 
G

Gina Whipp

gagecres,

Since all of the code you received here should work, let's check a few
othwer things...

1. This subform isn't continuous is it?
2. How is the subform bound to the main form?
3. Is the code on the subform or the main form?
4. Is the code on the On_Current event of the subform?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

txt_Rec_Count is an unbound text box. I removed ".Value" and still got the
same No Current Record error. The form is a subform and yes it does have
records in it.
 
G

Gina Whipp

Okay, then let's forget that and use this...

Create a text box and copy/paste the below...

=IIf([CurrentRecord]>(Count(*)),'No Record',('Contract ' & [CurrentRecord] &
' of ' & Count(*))) & " line(s)"


Note, where is says 'No Record', you can have it say anything or nothing.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

The code you gave me works fine if the first parent record that appears on
the form has records in the subform. If that first parent record has no
records in the subform, that's when I get the "No Current Records" error as
soon as I open the database and the debugger highlights the ".MoveLast"
statement.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top