current record count and total record count on form (1 Viewer)

[joe789]

Hi Folks,

I currently have a form with a tab control on it, I have disabled the navigation bar on the bottom of the form to force compliance with specific controls and data flow on the form and tabs and didn't want the user to circumvent that security by using the navigation buttons of first record, previous record, next record, last record, or new record that come on the navigation bar on all forms unless disabled via the form properties. I have placed my own command buttons on a specific tab in the form that I want the users to use to navigate thru the records.

However, the problem with the above method is that I really would like the current record out of total records information label/box that comes standard on the navigation form!

Is there anyway that I can easily slap a label or textbox onto the specific tab in question that would display the current record count out of the total records in the database?

Any help would be appreciated,

Thanks,

Joe

 

[Beetle]

Here's an example using a text box control. This code would go in the Current event of the form;

Private Sub Form_Current ()

With Me
    !txtRecordDisplay = "Record No. " & .CurrentRecord & " of " & .RecordsetClone.RecordCount
End With

End Sub

You may or may not want to expand on that by enabling/disabling some of your navigation buttons depending on whether you are already at the beginning or end of the recordset. Example;

Private Sub Form_Current ()

With Me
    !txtRecordDisplay = "Record No. " & .CurrentRecord & " of " & .RecordsetClone.RecordCount
    !cmdFirst.Enabled = .CurrentRecord <> 1 And Not .NewRecord
    !cmdPrevious.Enabled = .CurrentRecord <> 1 And Not .NewRecord
    !cmdNext.Enabled = .CurrentRecord <> .RecordsetClone.RecordCount And Not .NewRecord
    !cmdLast.Enabled = .CurrentRecord <> .RecordsetClone.RecordCount And Not .NewRecord
End With

End Sub

 

[siCIVIC1986]

Here's an example using a text box control. This code would go in the Current event of the form;

Private Sub Form_Current ()
 
With Me
    !txtRecordDisplay = "Record No. " & .CurrentRecord & " of " & .RecordsetClone.RecordCount
End With
 
End Sub

You may or may not want to expand on that by enabling/disabling some of your navigation buttons depending on whether you are already at the beginning or end of the recordset. Example;

Private Sub Form_Current ()
 
With Me
    !txtRecordDisplay = "Record No. " & .CurrentRecord & " of " & .RecordsetClone.RecordCount
    !cmdFirst.Enabled = .CurrentRecord <> 1 And Not .NewRecord
    !cmdPrevious.Enabled = .CurrentRecord <> 1 And Not .NewRecord
    !cmdNext.Enabled = .CurrentRecord <> .RecordsetClone.RecordCount And Not .NewRecord
    !cmdLast.Enabled = .CurrentRecord <> .RecordsetClone.RecordCount And Not .NewRecord
End With
 
End Sub

Hi I used your code which enables and disables record selector buttons and displays how many records in the database and this works fine however when I add a record it seems to go a bit bonkers and say that its record 6 of 5 and all the buttons are disabled.

I have attached screenshot for reference.

Thanks

Simon Knott

 

[Beetle]

The reason it displays like that is because you are on a new record that hasn't been saved yet. You could just hide the text box when on a new record. Just add the following line to your code;

With Me
    !txtRecordDisplay.Visible = Not .NewRecord [COLOR="Red"]'<<New line[/COLOR]
    !txtRecordDisplay = "Record No. " & .CurrentRecord & " of " & .RecordsetClone.RecordCount
    !cmdFirst.Enabled = .CurrentRecord <> 1 And Not .NewRecord
    !cmdPrevious.Enabled = .CurrentRecord <> 1 And Not .NewRecord
    !cmdNext.Enabled = .CurrentRecord <> .RecordsetClone.RecordCount And Not .NewRecord
    !cmdLast.Enabled = .CurrentRecord <> .RecordsetClone.RecordCount And Not .NewRecord
End With

 

[Futures_Bright]

I've got this in a text box on a sub form; how do I go about updating it whenever the parent form record changes? At the moment it always shows 1 of 1 when I first jump to a record and nothing I seem to do works for this.

I've got closest by setting the field on the top form - it works when stepping through the code but not otherwise. The below code is from an unbound drop-down to find the record to go to afterupdate:

Dim strRecordCount As String

strRecordCount = ""

Me.RecordsetClone.FindFirst "[AudID] = " & Me![AudIDcbo]
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.Refresh
strRecordCount = Me.CheckListFsub.Form.CurrentRecord & " of " & Me.CheckListFsub.Form.Recordset.RecordCount
'
Me!CheckListFsub.Form!RecordCountersub = strRecordCount

 

[MarkK]

I would do something like this . . .

Private Sub Form_Current()
    Me.tbNavText = Me.GetRecordOfRecordsText(Me.Recordset)
End Sub

Function GetRecordOfRecordsText(rst As dao.Recordset) As String
   With rst.Clone
      .MoveLast
      GetRecordOfRecordsText = rst.AbsolutePosition + 1 & " of " & .RecordCount
   End With
End Function

Let us know if you can make that work

 

[Futures_Bright]

Hi MarkK,

I'm getting an "Argument is not optional" and the debugger is highlighting "GetRecordOfRecordsText" in the private sub. Not an error I've come across yet, and unfortunately this is a database design I've inherited so I'm not entirely sure where to start looking!

I notice that Option Explicit isn't in the code; would this cause this problem (already aware it can cause other problems, now on my to-do list but not got time to get this right now!); Do I need to make it a public function if it's going to be triggered by a parent form?

Edit:
Almost there, I made a mistake and forgot to put the (Me.Recordset) in! FYI Me.tbNavText = GetRecordOfRecordsText(Me.Recordset) works too.

Edit 2:
Final solution I put in a little work-around to make the first number show correctly (it changed 1 to 0 when first going to a record):

Private Sub Form_Current()
    Me.tbNavText = Me.GetRecordOfRecordsText(Me.Recordset)
End Sub

Function GetRecordOfRecordsText(rst As dao.Recordset) As String
Dim strCurrent As String
strCurrent = ""

   With rst.Clone
      .MoveLast
               If rst.AbsolutePosition + 1 < 1 then
                       strCurrent = 1
               Else
                       strCurrent = rst.AbsolutePosition + 1
               End If
      GetRecordOfRecordsText = strCurrent & " of " & .RecordCount
   End With
End Function