• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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!








0
codequest
Asked:
codequest
  • 4
  • 3
  • 3
2 Solutions
 
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now