Record Navigation Button Code for bound Subform Dataset

How do I code Next or Previous record button actions on my main form to move its associated subfrom row selection?

I have tried the following code but this does not stop navigation going outside the linked record set

    If Not Me.[SubformName].Form.Recordset.BOF And Not Me.[SubformName].Form.Recordset.EOF Then
End If
ARamptonIT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
better upload a  copy of your db..
Dale FyeOwner, Developing Solutions LLCCommented:
Create a new subroutine in a standard (not form) code module and add the following code.  Then, in each of the custom command buttons on the main form, use the Click event to call this subroutine, something like:

Private Sub cmd_SubFirst

    Call SubformNav(me.subformControlName.Form, FirstRecord)

End Sub

'This is air-code, so it may require some tweaking.

'Define the available values for use in the subroutine'

Enum dfMoveTo
    FirstRecord = 0
    PreviousRecord = 1
    NextRecord = 2
    LastRecord = 3
End Enum

Public Function SubformNav(frm as Form, MoveWhere as dfMoveTo)

    Dim rs as DAO.Recordset
    Dim lngRecCount as Long

    On Error goto ProcError

    'Open the recordsset as a clone of the form'
    Set rs = frm.recordsetclone

    'Determine the number of records in the recordset'
    lngRecCount = rs.RecordCount

    'Move the recordset pointer to the same as the subform
    rs.bookmark = frm.bookmark

    Select Case MoveWhere
        Case 0
            if rs.absoluteposition <> 0 then 
                frm.Bookmark = rs.bookmark
            end if
        Case 1
            if rs.absoluteposition <> 0 Then
                frm.Bookmark = rs.Bookmark
            end if
        Case 2
            if rs.Absoluteposition = lngRecCount - 1 Then
                frm.bookmark = rs.Bookmark
            end if
        Case 3
            if rs.Absoluteposition = lngRecCount - 1 Then
                frm.bookmark = rs.Bookmark
            end if
        Case Else
            msgbox "invalid parameter"
    End Select

    if not rs is nothing then
        set rs = nothing
    end if
    Exit Sub

    msgbox err.number & vbcrlf & err.Description, vbOKOnly, "Error moving to record in subform"
    Resume ProcExit

End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ARamptonIT ManagerAuthor Commented:
I have tried this and other scripts but I can't get the moved record focus to be one away from the selected row (script finds the last record viewed by user when it is loaded)
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeOwner, Developing Solutions LLCCommented:
Can you post a sample of the form, subform, and the data you are using.

Create a new MDB file (make sure it is an mdb), then import only the code, tables, queries, and forms necessary to display the problem you are having.  Clean up your data so you are not posting any proprietary or personal info.  Then open the form and make sure you are still encountering the same problem.  If so, compact the new database, then post it here for someone to look at.
ARamptonIT ManagerAuthor Commented:

I have retried your example and have it working now after changing the  =  to  <  in
Case 2 & 3
            if rs.Absoluteposition = lngRecCount - 1 Then

What I still don't understand is why I have to go through the recordsetclone process

Why won't movenext select the next record after the currently selected row on my subform recordset?
Dale FyeOwner, Developing Solutions LLCCommented:
I don't know, probably no way to figure it out short of looking at your application.
Rey Obrero (Capricorn1)Commented:
see my first post...;-)
ARamptonIT ManagerAuthor Commented:
Slight error in sample code

Sample code a little more complex than I needed but enough to follow
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.