I've got a subform that allows the user to browse records in a table, then when the user selects a record in the subform, various fields on the main form are populated with the data from the record; the user can edit these and if they click the 'submit' button, the changes they have made will be applied back to the table.

The subform then has to be refreshed to show the changes, but this makes it return to the first record.

I had thought that I could store the bookmark value from the subform when a record is selected (got this working OK, this code is in the subform's module, as the event to populate the main form's fields is in the OnClick event - when the user selects a record), and after the requery operation, reset the bookmark to the stored value...

...but I can't get it to work;
Me![MySubform].Bookmark = StoredValue
doesn't work (this code needs to be in the main form as that's where the 'Submit' button is, which does the requery action)

This can't be too difficult, can it?

Thanks in advance

Mike

Mike,

Could you not use a Recodsetclone to set the value of the subform. Note that this isn't exactly the correct syntax(I'm on lunch on a machine without Access) but try something like.
Store the primaryID of the subform table in variable StoredVal
Dim Db as DAO.Database
Dim MyRst as DAO.Recordset#
Set Db = CurrentDB()
Set MyRst = Db.OpenRecordSet("SubformTable")
MyRst.FindFirst(where MyPrimaryID = StoredVal)'I know this isn't the right syntax
Subform.BookMark = Rst.BookMark
db = nothing
Myrst = nothing

Sorry it's a bit sketchy!

Ian

Hmmmm... thanks, I've never used Recordsetclone, I'm not even too clear on how it works, my problem though was simply that I don't seem to be able to address the bookmark property of the subform from the main form, so I'm not at all sure if Subform.BookMark = Rst.BookMark is going to work either.

I'll give it a try though, and thanks once again for the response.

Mike

i've had a second thought that might be a bit inefficient but might work.

Use a doloop to loop until the primary key in the table is the same as the one you stored.

Do until Me![PrimaryKeyField] = StoredVal
Docmd.GotoRecord,acNext
if Me![PrimaryKeyField] = StoredVal then exit do
loop

Try that if you aren't sure on Recordsets.

Ian