Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Recordset Refresh (2000)

    On a subform I hid the navigation buttons and then added this textbox: ="Record " & [CurrentRecord] & " of " & RecordSet.RecordCount. I noticed when I open the main form that the above code would show Record 1 of 1, when there were more than 1 records (1 of 3 or 1 of 18). So I added to the OnLoad event of the subform, the following:
    Private Sub Form_Load()
    Me.Recordset.MoveLast
    Me.Recordset.MoveFirst
    End Sub
    It seems to work with the very first record on the Main form. Meaning the subform now says 1 of 8, but when I navigate to another record on the Main Form my subform reverts back to 1 of 1. Any ideas?
    Thanks,
    Deb

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

    Re: Recordset Refresh (2000)

    See <post#=30813>post 30813</post#> by Charlotte for an alternative approach.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordset Refresh (2000)

    Thanks for repsonding Hans, but I'm confused as to how that works. When I go into my subform form properties OnCurrent event it give me
    Private Sub Form_Current()

    End Sub
    So I'm not sure how to incorporate Charlotte's code because she calls her sub procedure CountRecords(). Any help on how I use that code?
    Thanks,
    Deb

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

    Re: Recordset Refresh (2000)

    The idea is to copy the CountRecords procedure from Charlotte's post into the form module, then to call CountRecords from the On Current event:

    Private Sub Form_Current()
    CountRecords
    End Sub

    and similarly for the After Insert event.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordset Refresh (2000)

    Ok. I guess the part of the form's module is confusing me. I'm not sure where that is. If you haven't guessed I'm not all that familiar with VBA but I am schedule to take a class next month.
    Thanks,
    Deb

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

    Re: Recordset Refresh (2000)

    The "form module" is the place where you create the event procedures for the form and its controls, e.g. the On Current and On Load event of the form. You can place other code related to the form in that module, besides event procedures.

    So just create the On Current event procedure Sub Form_Current() the usual way, and type or paste Charlotte's code in the same window, say below the other procedures. It might look like this:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Option Explicit
    Option Compare Database

    Private Sub Form_Current()
    CountRecords
    End Sub

    Private Sub Form_AfterInsert()
    CountRecords
    End Sub

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

    <img src=/w3timages/blueline.gif width=33% height=2>

    Replace txtRecNo by the name of the text box that should display 1 of 8 etc. The control source of this text box should be blank, since the code will set the text.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordset Refresh (2000)

    Thanks Hans,
    That's sorta what I thought but hadn't seen any code that way and wasn't sure. I put all the code in, I now have the CountRecords Sub in the form module, and I call it OnCurrent and AfterInsert, everything shows up correctly, but I have the problem the guy was having from Charlotte's post. This is a subform that I'm using this with. The record count shows up correctly for the very first Main Form record. when i move to record 2 is only shows 1 of 1 until I click the subforms next record button. I added CountRecords to the OnOpen event of the subform and that still didn't help. Any thoughts?
    Thanks,
    Deb

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

    Re: Recordset Refresh (2000)

    Try the following:
    - Open the main form in design view.
    - Click once on the subform (don't click a second time, for then you will select something in the subform)
    - Note the name of the subform control in the Properties window.
    - Select View | Code to open the form module for the main form.
    - Create an event procedure for the On Current event of the main form, and substitute the name of the subform for SubformName:

    Private Sub Form_Current()
    On Error Resume Next
    With Me.[SubformName].Form.RecordsetClone
    .MoveLast
    .MoveFirst
    End With
    End Sub

    - Note: if there already was a Form_Current procedure, don't create a second one, but add the lines between Private Sub ... and End Sub above to the existing procedure.

    This should force the subform to recalculate the number of subform records each time the user moves to another record in the main form.

    (The code in the On Open event of the subform will not be executed, for the subform is not really "open".)

  9. #9
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordset Refresh (2000)

    I added your code and it didn't help. The first record comes up ok but after that everything is still 1 of 1. I double checked everything to make sure I didn't it correctly. Any other ideas?
    Thanks,
    Deb

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

    Re: Recordset Refresh (2000)

    Sorry, no. It works OK for me even without the latest addition, both on a main form and on a subform. As usual, you can post a stripped down version of the database if you like, so that others can take a look.

  11. #11
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Recordset Refresh (2000)

    Here's a scaled down version. it's the frmDPLEntry. Thanks for your help.
    Deb
    Attached Files Attached Files

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

    Re: Recordset Refresh (2000)

    I'm baffled. I'm sure I'm missing something obvious, but I can't get it to work for your form and subform. I thought there might be something corrupt, but even after rebuilding everything from scratch in a new database, no go. Exactly the same method still works without a problem in several existing databases of mine, but I can't find what makes the difference. I'm really sorry. Is using the built-in navigation buttons really out of the question?

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Recordset Refresh (2000)

    I have had a play with this and got it to work, but I am not exactly sure what I changed.

    I was doing something similar the other day, and was having trouble getting it to behave, so I declared a recordset object, then set it equal to me.recordsetclone, and that fixed the problem I was having.

    Then in the oncurrent event of the components form, I set it again, then movelast.

    John
    Attached Files Attached Files
    Regards
    John



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

    Re: Recordset Refresh (2000)

    The method in my original post was DAO-based, is that what you used? If you're using ADO, you have to do it differently, since ADO has a Recordset.Clone method but it produces another recordset, not a RecordsetClone, which is a DAO object.
    Charlotte

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Recordset Refresh (2000)

    Yes, I used DAO.
    Regards
    John



Posting Permissions

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