Counter textbox on form not updating (1 Viewer)

[SyntaxSocialist]

I have a form (frmEdit)

On that form is a subform (subMain)

On that subform is a footer, and in that footer is a textbox (txtCounter) whose control source is: =Count(*)

On frmEdit I have placed a textbox (txtCounterDisplay). I have created a subroutine (Private Sub CountRecords()) to control the value displayed in txtCounterDisplay:

Private Sub CountRecords()

    Me.txtCounterDisplay.Value = Me.CurrentRecord & "/" & [subMain].Form.[txtCounter]

End Sub

Problem: No matter what I try, txtCounterDisplay will only display "1/" at first. All I have to do to get it to display properly (e.g.: "1/1096") is change the current record of the form using some navigation buttons I have set up, whose associated click events are outlined below. It seems to me that txtCounterDisplay isn't displaying properly at first because txtCounter (in subMain) hasn't finished counting all the records. So how do I update txtCounterDisplay after that initial counting for txtCounter is complete?

Private Sub btn1st_Click()

    DoCmd.GoToRecord Record:=acFirst
    
End Sub

Private Sub btnPrev_Click()

    DoCmd.GoToRecord Record:=acPrevious

End Sub

Private Sub btnNext_Click()

    DoCmd.GoToRecord Record:=acNext

End Sub

Private Sub btnLast_Click()

    DoCmd.GoToRecord Record:=acLast

End Sub

 

[SyntaxSocialist]

Update:

So I can get txtCounterDisplay to update properly (accurately and immediately) if I simply use:

Private Sub CountRecords()

    Me.Recordset.MoveLast
    Me.Recordset.MoveFirst
    Me.txtCounterDisplay.Value = Me.CurrentRecord & "/" & Me.Recordset.RecordCount
    
End Sub

Bot WOW do I take a performance hit with that .MoveLast and .MoveFirst. And it also seems to mess with the functionality of my First/Previous/Next/Last buttons. Anyone have any ideas?

 

[pr2-eugin]

SyntaxSocialist.. Try using the RecordsetClone rather than Recordset, this will reduce the performance issue a bit.. However is still not a great way.. As Allen Browne puts it..

Don't MoveLast unless you really need to: this will be slow with a large recordset or a recordset drawn across a network. RecordCount will always be at least 1 if records exist, so there is no need to MoveLast if you only want to know if you have records to work with.

Why not just use the default Record Navigators?

 

[mdlueck]

In order to get your custom event to fire, you need to call the custom event.

Change the Private keyword to Public

Then after the calls to DoCmd, next LOC place a:

Call Me.CountRecords()

So....

Private Sub btnPrev_Click()
  DoCmd.GoToRecord Record:=acPrevious
  [COLOR=Blue][B]Call Me.CountRecords()[/B][/COLOR]
End Sub

[COLOR=Blue][B]Public[/B] [/COLOR]Sub CountRecords()
  Me.txtCounterDisplay.Value = Me.CurrentRecord & "/" & [subMain].Form.[txtCounter]
End Sub

 

[SyntaxSocialist]

Try using the RecordsetClone rather than Recordset, this will reduce the performance issue a bit.. However is still not a great way.... Why not just use the default Record Navigators?

Thanks, eugin, I'll see about giving that a try. My goal was originally to make the form behave somewhat like a split form (but actually using a split form placed too many restrictions on me). So I'm settling for a compromise where users can navigate through a subform using the default navigation buttons and through the main form's bound controls using the custom buttons.

In order to get your custom event to fire, you need to call the custom event.

Change the Private keyword to Public

Then after the calls to DoCmd, next LOC place a:

Call Me.CountRecords()

So....

Private Sub btnPrev_Click()
  DoCmd.GoToRecord Record:=acPrevious
  [COLOR=Blue][B]Call Me.CountRecords()[/B][/COLOR]
End Sub

[COLOR=Blue][B]Public[/B] [/COLOR]Sub CountRecords()
  Me.txtCounterDisplay.Value = Me.CurrentRecord & "/" & [subMain].Form.[txtCounter]
End Sub

Thanks mdlueck. I've given this a shot, but no luck. I still just get "1/" and no totals. And I was calling CountRecords() from an OnCurrent event. It's clear that it's firing (since I get the "1/"), it's just not doing it the way I'd expect/want. Shouldn't a private subroutine be fine as long as it's being called from within the same module (as is the case here)?

 

[mdlueck]

Thanks mdlueck. I've given this a shot, but no luck. I still just get "1/" and no totals.

Then there must be a problem with:

[subMain].Form.[txtCounter]

Is the name of the subform control on the main/parent form really "subMain"?

Is there a valid / populated control on the subform "txtCounter" which shows the value you wish to use as the total?

Shouldn't a private subroutine be fine as long as it's being called from within the same module (as is the case here)?

Then you can not use the Me. qualifier. In order to use the Me. qualifier, it must be Public.

I prefer to code calls within a Form in Me. context to assist me with seeing clearly where the code is that I am making the call to... on form Me!

 

[SyntaxSocialist]

Then there must be a problem with:

[subMain].Form.[txtCounter]

Is the name of the subform control on the main/parent form really "subMain"?

Is there a valid / populated control on the subform "txtCounter" which shows the value you wish to use as the total?

Yup. I know it's fine because it shows the proper totals after I change the current record. It just doesn't display immediately (as soon as the form is opened), even if I employ some kind of delay.

 

[SyntaxSocialist]

K, got it to work the way I wanted with the following code in the frmEdit module:

Private Sub CountRecords()

    Dim rst As DAO.Recordset
    Dim endCount As Long
    Set rst = Me.RecordsetClone
    
    If rst.RecordCount > 0 Then
        rst.MoveLast
        endCount = rst.AbsolutePosition + 1
            'because .AbsolutePosition starts at 0
        rst.MoveFirst
        Me.txtCounterDisplay = Me.CurrentRecord & "/" & endCount
    Else
        Me.txtCounterDisplay = "0/0"
    End If

End Sub