Move to a subform record

[Dreamweaver]

this works but the record is set as the first record regardless of the sort order

Dim R As DAO.Recordset
On Error GoTo HandleErr

    If Not IsNothing(Me.OpenArgs) Then
        Set R = Me.Sub_ZfrmTracks.Form.RecordsetClone
            R.FindFirst "[TrackID] = " & Me.OpenArgs
            Me.Sub_ZfrmTracks.Form.Bookmark = R.Bookmark
    End If

HandleExit:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select

So I tried This

Dim R As DAO.Recordset
On Error GoTo HandleErr

    If Not IsNothing(Me.OpenArgs) Then
       Me.Sub_ZfrmTracks.SetFocus
        DoCmd.GoToRecord , , acGoTo, Me.OpenArgs
    End If

HandleExit:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select

I I get a can't goto record Error

In Picture form I select the record found as below

When the recording form opens It moves the subform so the record I want it in the subform window as below (Circled)

 

[bob fitz]

When you get to the line:

DoCmd.GoToRecord , , acGoTo, Me.OpenArgs

What value do you have in Me.OpenArgs

 

[Dreamweaver]

The TrackID which is a long
I did set a debug to check what I was getting but will check again

 

[Gasman]

Mick,

That OpenArgs will just be the offset?, not a particular record?

 

[Dreamweaver]

OK So I need to get a record count and the location in recordset for the one I want Thanks Gasman

 

[Gasman]

OK So I need to get a record count and the location in recordset for the one I want Thanks Gasman

I don't think so.?
If I understand you, when you use the first code the record you want is at the top of the subform.? I believe that is to be expected?

If for some reason you want it in the middle of the form for some reason, then find the record, move back a set number using that offset method, then go to that record.

I wanted to do something similar for an ESF and did it this way

    DoCmd.RunCommand acCmdRecordsGoToLast
    DoCmd.GoToRecord acDataForm, Me.Name, acPrevious, 5 ' Needed for a continuous form as only last record shows.
    DoCmd.RunCommand acCmdRecordsGoToLast

that way I saw the last 5 records in the subform, which only would display five ate a time.?

HTH

 

[Dreamweaver]

Thanks @Gasman This code worked for me

Dim R As DAO.Recordset
Dim I As Long, T As Long
On Error GoTo HandleErr
I = 1
    If Not IsNothing(Me.OpenArgs) Then
        T = Me.OpenArgs
        Set R = Me.Sub_ZfrmTracks.Form.RecordsetClone
        With R
            If .RecordCount > 0 Then
                Do While Not .EOF
                    If !TrackID = T Then Exit Do
                    I = I + 1
                .MoveNext
                Loop
            End If
        End With
        Debug.Print I
        Me.Sub_ZfrmTracks.SetFocus
        DoCmd.GoToRecord , , acGoTo, I
    End If

HandleExit:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
            Resume HandleExit
        Resume
    End Select

 

[Gasman]

That seems a lttle convoluted to me.? as you know the disk and track number amd ID, so you could just find that record then move back a few records like I did?
In your case that would appear to be about 11 ?

However, at least you have a working solution.

 

[Dreamweaver]

I do have disc and track numbers and could find disc2 track 14 as long as I know how many tracks are in disc 1 plus what if they miss a track or mess up the numbering my system doesn't have to take the user into account.

If I have missunderstood please put me right

 

[Dreamweaver]

This is what I wanted @Gasman

It finds the record and then moves to that record so it's in the display section of the subform I can't think Of any other reliable way to do it?

 

[Gasman]

But that is the first visible record in the subform?
I thought you wanted to show it further down the form.?

Doesn't the FindFirst do that for you anyway?

 

[Dreamweaver]

It is after the goto but it's about record 37 as there are 2 discs and as luck has it I have the track name first in the tab order so it is selected as above.

Thats what I wanted as I can use the edit system Without having to scroll down the list.

I did try the find first but it did find the record and put it top of the list but it didn't maintain the order I am wondering if I could get a pointer to the record I wanted in the list Not track ID but something like me.record???

 

[Gasman]

Well, I would expect the FindFirst just works on the order of the records to the form?
So if they are in the correct order in the first place, then that would not be an issue?

If users are entering these, then they *could* enter 1-1, then 2-1, then 1-2, then 2-2 and the autonumber iD would not be of much use.?

However you have the disc and track number, so you can oder by those.?

This is all theory, as not something I have had to do though?

 

[Dreamweaver]

A lot of what I've been doing with this project is different to the way I used to design them, at least it seems to be responding much better even though I only have 20k tracks