Results 1 to 7 of 7

Thread: Record counter

  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    Orlando, FL
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Record counter

    I am creating a subform within a subform within a master form. I have all the forms created but the record navigator buttons are confusing. Because of the layout of the forms, the record navigators are right above each other and it is confusing as to which one controls which subform. I have created my own command buttons that move to the next, previous, first, and last records. Like the navigator buttons I also would like to have a Record x of y counter but can't figure this out. How do I create this calculated. I seem to run in a problem with one of my memo fields in the query the form is based on. I get a message "Can't group on Memo or OLE object" when I try to use the Count function in the Total.

    Help.

  2. #2
    New Lounger
    Join Date
    Feb 2001
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record counter

    you could use something like this

    Private Sub Form_Current()
    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    rst.MoveLast
    With rst
    rst.Bookmark = Me.Bookmark
    Me.lblRecNo.Caption = (rst.AbsolutePosition + 1) & " of " & rst.RecordCount
    End With
    End Sub

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

    Re: Record counter

    Actually, it's even easier. Stick this code into your form or subform and call it from the Current event or the AfterInsert event. All it requires is an unbound textbox named txtRecNo to hold your # of # expression. The CurrentRecord property of the form will return the index of the current record in the recordset and the recordcount will return the number of records behind the form.

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

    Charlotte

  4. #4
    New Lounger
    Join Date
    Feb 2001
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record counter

    yes it is easier, but if I might add, you should also add

    Private Sub Form_Open(Cancel As Integer)
    DoCmd.GoToRecord acActiveDataObject, , acLast
    DoCmd.GoToRecord acActiveDataObject, , acFirst
    End Sub

    This way the textbox will display the recordcount for the first record when the form opens.

    PaulF

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

    Re: Record counter

    I wouldn't suggest using the runcommand method rather than the code you posted earlier. You still need something like that to get the initial record count, but you only need to call it once at form open. After that, the routine I posted takes care of it.
    Charlotte

  6. #6
    Lounger
    Join Date
    Feb 2001
    Location
    Orlando, FL
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record counter

    That's great. I am going to have to take a VB class to learn this stuff. One thing however. This is a subform and when the subform is openend by itself it reads correctly "1 of 9". However, when the main form is openend, the subform will read "1 of 1" until I go to the next record then it will read "2 of x", x being the correct number of records in that subset. I then can go from record to record and it displays correctly. What do I do?

  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 counter

    Same thing you do in the main form. Copy the code from the parent form to the subform (if you put it in the main form to start with, that is) and use it the same way. I do this all the time when I want custom navigation buttons on subforms so that I can locate them where I want. You still have to "prime the pump" by getting the recordcount to start with.
    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
  •