Move to a subform record (1 Viewer)

Dreamweaver

Well-known member
Local time
Today, 09:51
Joined
Nov 28, 2005
Messages
2,466
this works but the record is set as the first record regardless of the sort order
Code:
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

Code:
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
2020-12-19 (1).png


When the recording form opens It moves the subform so the record I want it in the subform window as below (Circled)
2020-12-19 (2).png
 

bob fitz

AWF VIP
Local time
Today, 09:51
Joined
May 23, 2011
Messages
4,717
When you get to the line:

DoCmd.GoToRecord , , acGoTo, Me.OpenArgs

What value do you have in Me.OpenArgs
 

Dreamweaver

Well-known member
Local time
Today, 09:51
Joined
Nov 28, 2005
Messages
2,466
The TrackID which is a long
I did set a debug to check what I was getting but will check again
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:51
Joined
Sep 21, 2011
Messages
14,038
Mick,

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

Dreamweaver

Well-known member
Local time
Today, 09:51
Joined
Nov 28, 2005
Messages
2,466
OK So I need to get a record count and the location in recordset for the one I want Thanks Gasman
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:51
Joined
Sep 21, 2011
Messages
14,038
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

Code:
    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

Well-known member
Local time
Today, 09:51
Joined
Nov 28, 2005
Messages
2,466
Thanks @Gasman This code worked for me
Code:
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

Enthusiastic Amateur
Local time
Today, 09:51
Joined
Sep 21, 2011
Messages
14,038
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. (y)
 

Dreamweaver

Well-known member
Local time
Today, 09:51
Joined
Nov 28, 2005
Messages
2,466
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
 
Last edited:

Dreamweaver

Well-known member
Local time
Today, 09:51
Joined
Nov 28, 2005
Messages
2,466
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?
2020-12-19 (3).png


2020-12-19 (4).png
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:51
Joined
Sep 21, 2011
Messages
14,038
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

Well-known member
Local time
Today, 09:51
Joined
Nov 28, 2005
Messages
2,466
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

Enthusiastic Amateur
Local time
Today, 09:51
Joined
Sep 21, 2011
Messages
14,038
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

Well-known member
Local time
Today, 09:51
Joined
Nov 28, 2005
Messages
2,466
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
 

Users who are viewing this thread

Top Bottom