Point subform at particular record (part 2)

This is a continuation of

http://www.experts-exchange.com/Databases/MS_Access/Q_21216751.html

Original Question:
----------------------------------
I've got a subform on a main form.  The sub-form has the same source as the main form.  However, the sub-form is just a list of the "ID" and "Name" fields from the main form.   The subform is essentially a long scrolling list of these two columns from the underlying table.

When I arrive at a selected record in the main form, I want the top record in the sub-form to correspond to the "ID" value in the main form.   And I want to be able to scroll freely in the subform, from that point, throughout the entire table.

Any suggestions?  I need the triggering event (on current?)  and some way of setting the top record that shows in the subform.
----------------------------------


flavo's answer, which evolved into this:
----------------------------------
("On Current" event - fldID = field ID and fldVal = fieldvalue)
Forms("a210_Modules_Edit").a275_Module_List_for_Edit.Form.Recordset.FindFirst fldID & " = '" & fldVal & "'"
----------------------------------
got to the right record in the sub-form (good start!) but didn't position that record at the top of the form.

eghtebas' formulation:
----------------------------------
(in On Current event)
Me.SubFormControlName.Form.GoToRecordSelected(txtID)      'txtID is on the main form.
put above code in the oncurrent event of your main form.
---- plus -----
(in subfom code window)
Public Sub GoToRecordSelected(IdVal As string)
Me.Controls("Object_ID").SetFocus
DoCmd.FindRecord IdVal
end sub
----------------------------------

worked up to failing on the DoCmd with:

"a macro set to one of the current field's properties failed because of an error in a FindRecord action argument"


flavo's last suggestion was this
-----------------------------------
DoCmd.GoToRecord acDataForm, "mySubFrom", acGoTo, forms _("MainForm").SubFromControl.Forms.Recordset.AbsolutePosition

(flavo added: Im not sure how to specify a subform using this??)
-----------------------------------

at which point I had to punt for a while....

Any further suggestions welcome.

Thanks!








LVL 2
codequestAsked:
Who is Participating?
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.

Mike EghtebasDatabase and Application DeveloperCommented:
Dim MyForm As From
Set MyForm=Forms("MainForm")
DoCmd.GoToRecord acDataForm, "mySubFrom", acGoTo, MyForm!SubFromControl.Forms.Recordset.AbsolutePosition
0
Mike EghtebasDatabase and Application DeveloperCommented:
Did you try this?

Me![Object_ID].SetFocus
DoCmd.FindRecord IdVal
0
harfangCommented:
The problem is that without rather complex Windows API calls, we cannot manipulate scroll bars from Access. We can indirectly influence them, but not directly...

For example, you could always move to the first record before finding the one you want, hoping that it will thus be displayed first... Basically, the only information we have on the position of the selected row is CurrentSection.Top...

I used that in another question to manipulate the scroll bar indirectly: http:Q_21208279.html
The question is similar, as the goal was to synchronize the scrolling of two subforms based on the same recordset...

Take a look and tell me if it seems to suit you. We can adapt it to your case.

Cheers!
0

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

codequestAuthor Commented:
eghtebas:

I tried it, like this:

Public Sub GoToRecordSelected(IdVal As string)
Me.Controls("Object_ID").SetFocus
DoCmd.FindRecord IdVal
end sub
----------------------------------

it worked up to failing on the DoCmd with:

"a macro set to one of the current field's properties failed because of an error in a FindRecord action argument"
0
codequestAuthor Commented:
eghtebas:

In which event, for which form, would you run this:

Dim MyForm As From
Set MyForm=Forms("MainForm")
DoCmd.GoToRecord acDataForm, "mySubFrom", acGoTo, MyForm!SubFromControl.Forms.Recordset.AbsolutePosition

Thanks!
0
Mike EghtebasDatabase and Application DeveloperCommented:
With this, I was trying to show the correct syntax for:

forms_"MainForm").SubFromControl.Forms.Recordset.AbsolutePosition

You were asking for.  Place it as you have discussed with flavo.
0
codequestAuthor Commented:
Gents,

This combination of flavo's original input and harfang's latest suggestion does the job.   I can't get flavo/eghtebas' approach to repositioning the to work, to I'm going to roll it into another question...the comments for which I may not be able to respond to immediately.

Here's the current solution, which works exactly right, except that the "automatic scrolling" is a little slow... (VERY picky on that one...this works very well and I'm going to start using it.)

'====== position the subform to the right record (flavo's)
fldID = "Module_ID"
fldVal = Nz(Me("Module_ID"), "")
form1nm = "a210_Modules_Edit"
Forms(form1nm).a275_Module_List_for_Edit.Form.Recordset.FindFirst fldID & " = '" & fldVal & "'"

'====================== scroll the form so the selected record is at the top (harfang's)

    Set frm1 = Me!a275_Module_List_for_Edit.Form
    ' try to scroll downwards
   
    For intTest = 1 To frm1.Recordset.RecordCount - frm1.SelTop
        With frm1.Recordset
            .AbsolutePosition = .AbsolutePosition + intTest
            .AbsolutePosition = .AbsolutePosition - intTest
        End With
    Next intTest

=================================

Thanks for the help!
0
harfangCommented:
To speed things up, replace:
    For intTest = 1 To frm1.Recordset.RecordCount - frm1.SelTop
with
    For intTest = 1 To frm1.Recordset.RecordCount - frm1.CurrentRecord
        If frm1.SelTop = 1 Then Exit For

In the current code, you seem to have the subform scroll to the very last record each time :)
0
codequestAuthor Commented:
harfang,  that last one you suggested seems to still bang around a lot getting to the right position (lot of scroll bar movement through 490  records, takes 1 - 2 seconds).  

It may be a little crude, but what I did was this

    For intTest = 1 To frm1.Recordset.RecordCount - frm1.CurrentRecord
        If frm1.SelTop = 1 Then Exit For
        With frm1.Recordset
            .AbsolutePosition = .AbsolutePosition + intTest * 100
            .AbsolutePosition = .AbsolutePosition - intTest * 100
        End With
    Next intTest

and then trapped the "invalid argument" (#3001) error that it causes.   Repositions very quickly.
0
harfangCommented:
Hmm. In that case, why not just:

    Dim strBookmark As String

    strBookmark = frm1.Bookmark
    frm1.Recordset.MoveLast
    frm1.Bookmark = strBookmark

I.e. jump to last and come back :)

Ah, it is a difficult matter. I never found a good clean solution. If you have something acceptable, stick with it...

Cheere:)
0
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.

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.