codequest
asked on
Point subform at particular record (part 2)
This is a continuation of
https://www.experts-exchange.com/questions/21216751/Point-subform-at-particular-record.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")
--------------------------
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
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").S
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").SubFromContr
(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!
https://www.experts-exchange.com/questions/21216751/Point-subform-at-particular-record.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")
--------------------------
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
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").S
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").SubFromContr
(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!
Did you try this?
Me![Object_ID].SetFocus
DoCmd.FindRecord IdVal
Me![Object_ID].SetFocus
DoCmd.FindRecord IdVal
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
eghtebas:
I tried it, like this:
Public Sub GoToRecordSelected(IdVal As string)
Me.Controls("Object_ID").S
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"
I tried it, like this:
Public Sub GoToRecordSelected(IdVal As string)
Me.Controls("Object_ID").S
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"
ASKER
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.Form
Thanks!
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.Form
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
'====================== scroll the form so the selected record is at the top (harfang's)
Set frm1 = Me!a275_Module_List_for_Ed
' try to scroll downwards
For intTest = 1 To frm1.Recordset.RecordCount
With frm1.Recordset
.AbsolutePosition = .AbsolutePosition + intTest
.AbsolutePosition = .AbsolutePosition - intTest
End With
Next intTest
==========================
Thanks for the help!
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
'====================== scroll the form so the selected record is at the top (harfang's)
Set frm1 = Me!a275_Module_List_for_Ed
' try to scroll downwards
For intTest = 1 To frm1.Recordset.RecordCount
With frm1.Recordset
.AbsolutePosition = .AbsolutePosition + intTest
.AbsolutePosition = .AbsolutePosition - intTest
End With
Next intTest
==========================
Thanks for the help!
To speed things up, replace:
For intTest = 1 To frm1.Recordset.RecordCount
with
For intTest = 1 To frm1.Recordset.RecordCount
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 :)
For intTest = 1 To frm1.Recordset.RecordCount
with
For intTest = 1 To frm1.Recordset.RecordCount
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 :)
ASKER
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
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.
It may be a little crude, but what I did was this
For intTest = 1 To frm1.Recordset.RecordCount
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.
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:)
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:)
Set MyForm=Forms("MainForm")
DoCmd.GoToRecord acDataForm, "mySubFrom", acGoTo, MyForm!SubFromControl.Form