Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Record count not correct on subform (XP, 2000)

    I have subforms nested in subforms and my own navigation controls. I also have a X of Y teaxt box so the user can see whic record they are on and the tolatl number of records. Problem is that I may have 9 records but the subform shows 1 of 1. When I go to record 2, it shows 2 of 9.

    I have the following code in the on load event:
    RunCommand acCmdRecordsGoToLast
    RunCommand acCmdRecordsGoToFirst

    My text box showing the records contains the following:

    = [CurrentRecord] & " of " & RecordSet.RecordCount

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Record count not correct on subform (XP, 2000)

    Instead of moving to the last record in the form, move to the last record in the form's recordset:

    Private Sub Form_Load()
    Me.Recordset.MoveLast
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record count not correct on subform (XP, 2000)

    Thnaks Hans. Obviously I would want the form to display the current record and to have an accurate count of the forms in that record set. So, should I change the onload event to the following?

    Private Sub Form_Load()
    Me.Recordset.MoveLast
    Me.Recordset.MoveFirst
    End Sub


    I added the Me.Recordset.MoveFirst in order to get back to record 1.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Record count not correct on subform (XP, 2000)

    Yes, that is a good idea. (I only verified that using Me.Recordset.MoveLast would make the text box display the correct number of records, but you do need to move back. Sorry for omitting that.)

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record count not correct on subform (XP, 2000)

    Okay, it seems to work in isolation. that is, if I open the form by itself (not as a subform). However, when I open the parent form, the subforms do not show the correct count of records until I move to the next record on the subform, then it updates.

    The parent form does show the correct record count but not the subforms. Is it possible to place additional code in the ParentForm OnLoad event to force the subforms to update? Something like:

    Forms!ParentForm.SubForm.Recordset.MoveLast
    Forms!ParentForm.SubForm.Recordset.MoveFirst

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Record count not correct on subform (XP, 2000)

    The code would look like this:

    With Me.SubFormName.Form.Recordset
    .MoveLast
    .MoveFirst
    End With

    The .Form between SubFormName and .Recordset is important. I don't know if it would be necessary also to have code in the On Current event of the main form, for on the system I'm using at the moment (Access 2002 with Jet 4.0 SP-8), the record count is correct without the MoveLast and MoveFirst code.

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record count not correct on subform (XP, 2000)

    Thanks. Does this code belong on the subform's OnLoad event?

    I wonder if I have an old version of Jet? I tried loading and running this DB on a computer with Access2003 and it seemed to explode! VBA errors and VBA windows popping open all over the screen.

    How would I determine what version of Jet I am using? and how to update? I am using Office XP pro right now.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Record count not correct on subform (XP, 2000)

    The subform's On Load event is not executed. The code should go into the main form's On Load event.
    Do a search for jet 4.0 version or something like that, you should find a link to the relevant MSKB article.
    I don't have Access 2003, so I have no idea about errors occurring there.

  9. #9
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record count not correct on subform (XP, 2000)

    With some manipulation, I got it to work. If I place the code in the main form On Load event, it only works for the initial display of the subform but does not update the subform as I move from record to record in the main form. So... I placed the code with the code used in my record navigation buttons and now it works well.

    Since I need to do this for many subforms, it it possible to have more than one subform identified in the "with" portion of the code or do I need to replicate the entire "with" for each of the subforms for that mainform?

    Thank you Hans.

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Record count not correct on subform (XP, 2000)

    In reference to question concerning Jet db engine version, recommend see this MSKB article:

    How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine

    As a general rule having latest version of Jet installed helps avoid some mysterious bugs. However, I'm still using Access 2K at work & home so have no specific advice for odd errors encountered in ACC 2003.

    HTH

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Record count not correct on subform (XP, 2000)

    You need to repeat the code for each subform, but you can shorten it by defining procedures in the form module:

    Private Sub RecCount(ctl As SubForm)
    With ctl.Form.Recordset
    .MoveLast
    .MoveFirst
    End With
    End Sub

    Private Sub AllRecCount()
    Dim ctl As Control
    For Each ctl In Me.Controls
    If ctl.ControlType = acSubform Then
    RecCount ctl
    End If
    Next ctl
    Set ctl = Nothing
    End Sub

    You can call AllRecCount wherever you need to refresh the record count of all subforms.

  12. #12
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record count not correct on subform (XP, 2000)

    Thanks, that is a good idea. When you say defining procedures in the form module, what exactly do you mean by a form module? The attached picture shows all the modules in my DB. SHould I create a new module named form?
    Attached Images Attached Images

  13. #13
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record count not correct on subform (XP, 2000)

    Okay, got it... I think. If I place the code in any of the form modules, will I be able to call the subroutine from any other form module? That is what I would like to be able to do.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Record count not correct on subform (XP, 2000)

    By form module, I mean the module containing the event code for a form. All the modules in your screenshot whose name starts with Form_ are form modules. So you should not create a new module! Put the code in the module belonging to the form.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Record count not correct on subform (XP, 2000)

    I thought you had one main form with lots of subforms. If you have lots of main forms with lots of subforms, it is better to put the procedures in a standard module, not in a specific form module, and modify them slghtly. You can create a standard module by clicking "New" in the Modules section of the database window, or by selecting Insert | Module in the Visual Basic Editor. Here is the revised code (changes are bold):

    Public Sub RecCount(ctl As SubForm)
    With ctl.Form.Recordset
    .MoveLast
    .MoveFirst
    End With
    End Sub

    Public Sub AllRecCount()
    Dim ctl As Control
    For Each ctl In CodeContextObject.Controls
    If ctl.ControlType = acSubform Then
    RecCount ctl
    End If
    Next ctl
    Set ctl = Nothing
    End Sub

    You can call the modified version of AllRecCount from any form.

Posting Permissions

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