Populate a text box or label with Record x of y 1

[SBLatta]

Hi, I found a thread I was hoping would solve may problem of how to put a "Record x of y" box on my form, but it's apparently too old for me to reply to directly. The solution which was posted on the thread is below, but I cannot figure out how to make the results show up on the form. I think I have all the VBA in place correctly, but the form is showing an Unbound Label named RecNum in design view, and in form view the label just shows the "A" placeholder. What am I missing?

fneily (Instructor) 20 Sep 06 10:25
At the top or whereever, put an unbound Label control (create a label and type the letter A so it is created). Call it RecNum
Open your form in design view and go ALT+F11 to go to VBA.

In the top section, put:
Option Compare Database
Option Explicit
Dim Records As DAO.Recordset
Dim TotalRecords

On the form's OnLoad event, put:
Set Records = Me.RecordsetClone
Records.MoveLast
TotalRecords = Records.RecordCount

On the form's OnCurrent event, put:
If Not Me.NewRecord Then
    Records.Bookmark = Me.Bookmark
Me![RecNum].Caption = "Record " & Records.AbsolutePosition + 1 & " of " & TotalRecords
Else
Me![RecNum].Caption = "New Record"
End If

Add two more sub's:
Private Sub Form_BeforeInsert(Cancel As Integer)
 Me![RecNum].Caption = TotalRecords + 1 & " pending..."
End Sub

Private Sub Form_AfterInsert()
 Records.MoveLast
 TotalRecords = Records.RecordCount
End Sub

 

[missinglinq]

Try this. It uses Me.Caption to place the record count in the title bar of the form, but, of course, you can replace this with the name of a label if yoy prefer:

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

Private Sub Form_Current()
 If Me.NewRecord Then
   Me.Caption = "Record  " & CurrentRecord & "  Of  " & RecordsetClone.RecordCount + 1 & "  Records"
 Else
   Me.Caption = "Record  " & CurrentRecord & "  Of  " & RecordsetClone.RecordCount & "  Records"
 End If
End Sub

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[TheAceMan1]

How are ya SBLatta . . .

I see a big flaw in the code provided.

[blue]When a record is saved, the recordset [purple]Records[/purple], is not [blue]requeried[/blue] to include/exclude that record![/blue]

Before I attempt to generate code, is there any reason why you can't use the [blue]navigation bar[/blue], which is what your trying to similuate?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[missinglinq]

Do you see any problem with the simpler code I provided, Aceman? It's been used successfully for a number of years now.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[fneily]

TheAceMan1 - works for me. Notice AfterInsert.

I'll bet that he didn't put the following in the Declarations Section of the form's code module.
Option Compare Database
Option Explicit
Dim Records As DAO.Recordset
Dim TotalRecords

 

[SBLatta]

Hi Fneily,

Looking at the VB code, the boxes at the top where I have those 4 lines typed in say: (General) (Declarations). The Form_OnLoad code is also showing under (General) (Declarations). On the left windows, under Alphabetic, the OnLoad has a blank field next to it, whereas OnConnect shows [Event Procedure].

I obviously have something not in the wrong place, but I have no idea how to fix it.

 

[SBLatta]

Hi Missinglinq,

I saw your code in the other thread, and really didn't want to restart a debate of the merits of yours vs fneily's here. I'm going to use his code, if I can get it working. I would appreciate any help you can give me in getting his code to work.

Hi TheAceman1,

I'm not using the navigation bar for two reasons. The first, it does not have a Delete Record button, which I need and if I'm going to make 1 button, I'd rather make a button bar that has everything in one place. The other is that the navigation bar uses little, tiny symbols for navigation and my users need larger buttons with easy to understand text labels on them. I wish Access included a way to customize the navigation bar so I could do those two things using the built in bar (maybe it does and I don't know how), but since I can't, I'm going to make my own buttons. I got all the navigation buttons in and working, but can't get the record counter to display.

 

[fneily]

Create a label in the header(or Footer) of your form and name it recnum. Select the form (grey square in upper left hand corner). Go to VBA(Alt+F11)
You can copy/paste this or, following the events of each sub, and place them on the appropriate event of the form.

Option Compare Database
Option Explicit
Dim Records As Recordset
Dim TotalRecords

Private Sub Form_Load()
Set Records = Me.RecordsetClone
Records.MoveLast
TotalRecords = Records.RecordCount
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![RecNum].Caption = TotalRecords + 1 & " pending..."
End Sub

Private Sub Form_AfterInsert()
Records.MoveLast
TotalRecords = Records.RecordCount
End Sub

Private Sub Form_Current()
If Not Me.NewRecord Then
Records.Bookmark = Me.Bookmark
Me![RecNum].Caption = "Record " & _
Records.AbsolutePosition + 1 & " of " & _
TotalRecords
Else
Me![RecNum].Caption = "New Record"
End If
End Sub

 

[SBLatta]

Thanks fneily, that worked! I don't know what was different, but pasting that code in got it running perfectly.

Thanks again!