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
Who is Participating?
Dale FyeCommented:
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

Rey Obrero (Capricorn1)Commented:
better upload a  copy of your db..
ARamptonAuthor 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)
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Dale FyeCommented:
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.
ARamptonAuthor 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 FyeCommented:
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...;-)
ARamptonAuthor Commented:
Slight error in sample code

Sample code a little more complex than I needed but enough to follow
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.