setting the selected row in a subform

  • Thread starter Thread starter Martin Payne
  • Start date Start date
M

Martin Payne

Hi all,


New to forum, hoping you can help.


I have a datasheet view subform that I have configured so that, when
you double click on certain fields it will open up a second form that
allows you to modify the value for that record. When you submit the
second form, it updates the database and refreshes the parent form
(with subform).


My problem is that, after the refresh, the subform reverts to the top
record. So, if you were modifying the value of the 400th record, you
would have to scroll back down again to modify the 401st record, and so

on.


What I want to do is, in the code for the submit button of the second
form (where it already specifies the refresh of the parent form before
it closes), I want to set the active record in the subform of the first

form.


I already know what the record is because, in the double-click event
that opens up the second form, I set a hidden field with the value of
CurrentRecord of the subform. But, when the form is in form view, the
CurrentRecord property is read-only, so I can't use that property on
the reverse side.


I also tried the DoCmd.GoToRecord method, but that tells me that the
(sub)form is not open.


Any ideas?


Thanks
Martin
 
Martin
Check out Steve Leban's SetGetScrollBars.zip at
http://www.lebans.com/SelectRow.htm
It has the code which will allow you to requery/refresh a subform, and
have it restored to it's original position. It works well.

Or... pass a unique value ffrom the subform record you're at to a
variable, and after the referesh/requery, do a Find Record for that value.
The subform may appear to jump, but at least you'll be back on the
originating record.
hth
Al Camp
 
Martin Payne said:
Hi all,


New to forum, hoping you can help.


I have a datasheet view subform that I have configured so that, when
you double click on certain fields it will open up a second form that
allows you to modify the value for that record. When you submit the
second form, it updates the database and refreshes the parent form
(with subform).


My problem is that, after the refresh, the subform reverts to the top
record. So, if you were modifying the value of the 400th record, you
would have to scroll back down again to modify the 401st record, and so

on.


What I want to do is, in the code for the submit button of the second
form (where it already specifies the refresh of the parent form before
it closes), I want to set the active record in the subform of the first

form.


I already know what the record is because, in the double-click event
that opens up the second form, I set a hidden field with the value of
CurrentRecord of the subform. But, when the form is in form view, the
CurrentRecord property is read-only, so I can't use that property on
the reverse side.


I also tried the DoCmd.GoToRecord method, but that tells me that the
(sub)form is not open.


Any ideas?


Thanks
Martin
Click to expand...

Hi Martin, use the following (air code) as an example...

' assuming that the current record has a unique identifier
dim lngRecordID as long

' assign to variable the value of the unique identifier
lngRecordID=txtRecordID

' code to open other form and requery the subform

' find the previous active record using recordset clone
me.recordsetclone.findfirst "RecordID=" & lngRecordID
me.bookmark=me.recordsetclone.bookmark

Luck
Jonathan
 
Back
Top