Full Version: Custom vba Navigation x of y inaccurate on sub form
UtterAccess Forums > Microsoft® Access > Access Forms
fritz
I have a peculiar result when using the following to create a x of y record count lable in a custom navigation bar in a sub form.

CODE
Private Sub Form_Current()
nbsp;   If Me.NewRecord Then
        Me!lblNavigate.Caption = "New Record"
    Else
        With Me.RecordsetClone
            .Bookmark = Me.Bookmark
            Me!lblNavigate.Caption = "Record " & _
                    .AbsolutePosition + 1 _
                    & " of " & .RecordCount
        End With
    End If
End Sub


The Access nav bar has a slight delay when displaying the number of records in a sub form while the above code displays right away and always "1" record regardless of the number of sub records. Clicking through the sub records seems to work as expected, however.

I'm thinking the .RecordsetClone may be set (if that is the right word) too soon.

Any suggestions or better ways to handle custom navigation?

Attached is a simple example - Reservation names in parent and payment types (visa, cash...) in child.

Harry

PS I just realized the same problem happens when using the code on the parent form - what am I missing?
Edited by: fritz on Sun Jul 20 21:07:09 EDT 2008.
HiTechCoach
To get the correct record count, you need to use .MoveLast before you can get the .RecordCount
e.RecordsetClone.MoveLast
DoEvents
Then Me.RecordsetClone.RecordCount will be accurate.
fritz
great, thanks. Is it necessary to .MoveFirst again?
arry
HiTechCoach
Hary,
epends on what you are needing to do.
You will need to do a .MoveFirst only if you need the recordset to have as the current record the first record.
fritz
I've got it working, although I'm not understanding why .MoveFirst works when the user clicks to advance to the next record. I would have thought it would make the first record loaded and result in 1 of x again. Could you possibly explain?
CODE
            With Me.RecordsetClone
nbsp;               .MoveLast
                .MoveFirst
                .Bookmark = Me.Bookmark
                  Me!Navlbl.Caption = (.AbsolutePosition + 1) &  " of  " &  .RecordCount
            End With

Edited by: fritz on Mon Jul 21 3:06:53 EDT 2008.
HiTechCoach
You code is working with a copy, called a "clone", of the form's recordset. Any changes in the position within the "clone" recordset do not automatically change the form's actual recordset also.

You really do not need the .MoveFirst before the move to the .Bookmark. It is just extra processing that is not needed. You do not need to be on the first record of a recordset to set the bookmark to a new position.


Maybe this will help:

CODE
            ' use "clone" of the form's recorset  
nbsp;           With Me.RecordsetClone
                ' move to the last record to update the .RecordCount
                .MoveLast
                ' position  the "clone" recordset to the same record as the form's recordset using the Bookmark
                .Bookmark = Me.Bookmark
                  '  update the label using the "clone" recordset's properties
                  Me!Navlbl.Caption = (.AbsolutePosition + 1) &  " of  " &  .RecordCount
            End With



I think that the "WITH ... End With " may sometimes make code harder to follow unless you are very familiar with what the code is supposed to do.


This may help:
CODE
' working with a "clone" of the form's recorset  
                ' move to the last record to update the .RecordCount
                Me.RecordsetClone.MoveLast
                ' position  the "clone" recordset to the same record as the form's recordset using the Bookmark
                Me.RecordsetClone.Bookmark = Me.Bookmark
                  '  update the label using the "clone" recordset's properties
                  Me!Navlbl.Caption = (Me.RecordsetClone.AbsolutePosition + 1) &  " of  " &  Me.RecordsetClone.RecordCount


Hope this helps ...
fritz
Excellent - thanks Boyd! Makes things much clearer, especially putting "RecrodsetClone" in front of .Bookmark - I was indeed missing whose Bookmark was referenced.
arry
HiTechCoach
You're welcome!
Glad we could assist.
uarulez2.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.