Results 1 to 14 of 14

Thread: Record Count

  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Record Count

    I am using Access 2000. In my subform I would like to be able to display "record # of total record count" rather than using the navigation bar. What code would produce that result and what event should it be attached to?
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record Count

    Here's a routine I used in an unbound form populated from an ADO recordset to do that:

    <pre>Private Sub SetRecordNum()
    'Reset the "# of #" displayed in the form footer
    [txtRecNum] = CStr(mrst.AbsolutePosition) & " of " _
    & mlngRecCount
    End Sub 'SetRecordNum()</pre>

    On bound subforms using DAO, it's easier.
    <pre>Private Function CountRecords()
    [txtRecNo] = "" & CurrentRecord & " of " _
    & Me.RecordsetClone.RecordCount
    End Function</pre>


    In the subform, call it in the Current and the AfterInsert events.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Count

    My subform was bound to a table so I created a text box named [txtRecNo]and used your code:

    Private Function CountRecords()
    [txtRecNo] = "" & CurrentRecord & " of " _
    & Me.RecordsetClone.RecordCount
    End Function

    I called this code from the Current and from the AfterInsert events of my subform.

    When I open my form, the first subform record reads 1 of 1 even though there are multiple subform records. Once I go to the next subform record then my [txtRecNo] text box properly displays the right number of total records such as 1 of 6. I tried calling the CountRecords function from other events but they all worked the same way.

    What do I need to add to the code so that the first record displays the correct number of total records?
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record Count

    You need to use the subform's open event to force the count the first time. Here's the DAO version:

    <pre> Dim rst As DAO.Recordset
    Dim lngCount As Long
    Set rst = Me.RecordsetClone
    With rst
    If Not .EOF Then
    .MoveLast
    lngCount = .RecordCount
    .MoveFirst
    End If
    End With
    rst.Close
    [txtRecCount] = CStr([CurrentRecord]) & " of " _
    & lngCount</pre>

    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Count

    With your help, I'm on the right track. If I open the subform by itself [txtRecNo] textbox is displaying correctly even on the first record, however; if I open my main form which initially opens to add new data with the subform not visible, and I decide to move to an old record in the mainform that contains multiple subform records, my subform becomes visible but it is still displaying 1 of 1 on the first subform record, even after I added this code to the subform's open event as per your instructions.

    Dim rst As DAO.Recordset
    Dim lngCount As Long
    Set rst = Me.RecordsetClone
    With rst
    If Not .EOF Then
    .MoveLast
    lngCount = .RecordCount
    .MoveFirst
    End If
    End With
    rst.Close
    [txtRecCount] = CStr([CurrentRecord]) & " of " _
    & lngCount

    I tried adding it to a couple other subform events with no luck. Do you have any other suggestions?
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Count

    The code I just pasted in from your code was modified to read [txtRecNo] to match my textbox's name and I didn't change it in the code I pasted in. Just wanted you to know that that is not the problem.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record Count

    Ah, the problem is that when you move back to a main form record without any related records in the subform, the current event of the subform doesn't fire and there is no currentrecord in the subform. Hence the bogus 1 of 1 message. Even requerying the subform control won't change it. You will have the same problem at the other end of the recordset when you try to add a new record to the parent form. Whatever the last count was is what still displays on the subform.

    Here's a solution. Use a modified RecordCount routine in the subform (I'm using my own subform and control names here):
    <pre>Public Function CountRecords()
    Dim rst As DAO.Recordset
    Dim lngCount As Long
    Set rst = Me.RecordsetClone
    With rst
    If Not .EOF Then
    .MoveLast
    lngCount = .RecordCount
    .MoveFirst
    Else
    lngCount = .RecordCount
    End If
    End With
    rst.Close
    'display the # of # string
    If lngCount = 0 Then
    [txtRecCount] = "0 of 0"
    Else
    [txtRecCount] = [CurrentRecord] & " of " & lngCount
    End If 'Me.RecordsetClone.RecordCount = 0
    Set rst = Nothing
    End Function 'CountRecords()</pre>


    Then in the OnCurrent event of the parent form, check to see if the subform's recordset has any records. If not, call the subform's RecordCount routine like this:
    <pre>Private Sub Form_Current()
    [txtRecCount] = CStr([CurrentRecord]) & " of " & Me.RecordsetClone.RecordCount
    If Me!fsubOrders.Form.RecordsetClone.RecordCount = 0 Then
    fsubOrders![txtRecCount] = "0 of 0"
    fsubOrders.Requery
    End If
    End Sub</pre>

    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Count

    I used the function from your last reply in my subform and simply changed the name of the textbox to match mine as displayed in red.

    Public Function CountRecords()
    Dim rst As DAO.Recordset
    Dim lngCount As Long
    Set rst = Me.RecordsetClone
    With rst
    If Not .EOF Then
    .MoveLast
    lngCount = .RecordCount
    .MoveFirst
    Else
    lngCount = .RecordCount
    End If
    End With
    rst.Close
    'display the # of # string
    If lngCount = 0 Then
    <font color=red>[txtRecNo]</font color=red> = "0 of 0"
    Else
    <font color=red>[txtRecNo]</font color=red> = [CurrentRecord] & " of " & lngCount
    End If 'Me.RecordsetClone.RecordCount = 0
    Set rst = Nothing
    End Function 'CountRecords()

    In my parent form I entered the following code in the OnCurrent event. The changes to your code that I made because of my form/field names is displayed in red in this message. Anything I added to your code to try and get it to work is displayed in blue.

    Private Sub Form_Current()
    <font color=blue>'Test to determine if subform contains a record
    If IsNull(Me!frmIncident.Form![StuNum]) Then </font color=blue>
    <font color=red>[txtRecNo]</font color=red> = CStr([CurrentRecord]) & " of " & Me.RecordsetClone.RecordCount
    If Me!<font color=red>frmIncident</font color=red>.Form.RecordsetClone.RecordCount = 0 Then
    <font color=blue>Me!</font color=blue><font color=red>frmIncident![txtRecNo]</font color=red> = "0 of 0"
    <font color=blue>Me!</font color=blue><font color=red>frmIncident!</font color=red>.Requery
    End If
    End Sub

    PROBLEMS: The parent form which is programmed to open for adding a new record, fails to open and the error message is "Run Time error #3021 - No Current Record" When I click the Debug button, the line in the function: .MoveFirst is highlighted. If I comment out that line the form will open. If I go to a record that has multiple subform records the count is wrong for the first record in the subform. Once I move to the second record in the subform and then move back to the first again, the count is correct.

    What am I doing wrong?
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  9. #9
    Lounger
    Join Date
    Apr 2001
    Location
    Stuttgart, Germany
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Count

    Oh, I forgot about the error message. To make sure the recordset has some records, you always should write:

    If Not rst.EOF <font color=red>And Not rst.BOF</font color=red> Then
    ...
    End If

    I guess, the error came because the recordset was on BOF.

  10. #10
    Lounger
    Join Date
    Apr 2001
    Location
    Stuttgart, Germany
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Count

    Judy,

    First, put your function in the OnCurrent event of the subform. It will fire each time the recordset changes, no matter if new record, deleting record or moving in the main form. There is no need for code in the main form.

    Then, here is some code again. There is an If in case there are no recs in the subform and there is a gimmick to show the right number if you're in a new rec on the subform.

    Dim rst As DAO.Recordset
    Dim lngCount As Long
    <font color=red>Dim fAtNew As Boolean</font color=red>

    'Just in case there are no recs in subform
    <font color=red>If Not IsNull(Me.RecordSource) Then</font color=red>
    Set rst = Me.RecordsetClone

    ' Check to see if you're on the new record or not.
    <font color=red>fAtNew = Me.NewRecord</font color=red>

    With rst
    If Not .EOF And Not .BOF Then
    .MoveLast
    lngCount = .RecordCount
    .MoveFirst
    End If
    End With
    rst.Close

    'If you're on a new rec, you should count it also,
    'the recordset doesn't contain it yet
    [txtRecCount] = CStr([CurrentRecord]) & " of " _
    & lngCount <font color=red>+ IIf(fAtNew, 1, 0)</font color=red>
    Else
    [txtRecCount]="0 of 0"
    <font color=red>End If</font color=red>

    Hope this helps :-)

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record Count

    Judy,

    I don't know exactly what differences there are between what you're doing and what I have working in a sample form, so I'm attaching a sample database with a couple of tables and a form and subform that demonstrate using this numbering. Maybe you can figure it out from there. The sample is in Access 2000, although the same approach will work in 97.
    Attached Files Attached Files
    Charlotte

  12. #12
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Count

    Charlotte, I think the problem was that my database was primarily for adding new records and the code from your database that I downloaded was mainly geared for looking up data. I learned alot by studying the code in your database though.

    I think one of my main problems was trying to figure out which event occurred in what order. I finally put a msgbox within several events to show me when they fired and imagine my surprise when the subform's OnCurrent event fired before the Main form's OnCurrent event and then the subform's OnCurrent event fired again.

    Thanks for all your help.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  13. #13
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Count

    Thanks Emilia. I finally got things working the way I want them to. I had to play around with the code a bit but it is now doing what is supposed to do. I did discover that when I added a new record, it didn't update the total record count by just calling the function or refreshing, I had to set the focus back to my main form and then back to my subform to get it to update.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Record Count

    When I set my sample form's DataEntry property to true, it appeared to work for me; but I'm glad you finally got it to behave.

    The OnCurrent event can fire multiple times in a form/subform setup, as you've discovered. It fires when you move between the parent and child forms because any changes to a record are saved when a form loses focus, and it fires when a form gains the focus as well, assuming there is a record, of course.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •