subforms and recordsets

[dbustell]

In A2K I

- use field inputs on a mainform to dynamically create an SQL statement
- pass the SQL to an ADO .Open
- assign the resulting recordset to subform.form.recordset

where subform is in datasheet view on mainform.

When a line is selected on the subform a detailform is opened with that record. If changes are saved to the record, I requery the subform so that the visible changes are displayed. This causes the cursor to go back to the first record on the subform.

I want to have the cursor stay at (or near) the record selected on the subform. When I tried using the subform.bookmark, things got a little goofy.

Question:
What is the relationship between the subform.bookmark and
recordset.bookmark? And between the subform.currentrecord and recordset.currentrecord?

Can the recordset.currentrecord be something other than the one highlighted on the subform?

TIA
Donald

[charlotte]

Why don't you post the code you tried so we have a starting point. I'm not clear on whether you're trying to work with the subform's recordset's bookmark or whether you're trying to make the subform.bookmark syntax work. Also, if you are adding records to the subform's recordset, the bookmark won't work anyhow.

One thing to try would be to save the unique key of the current subform record to a parent level variable. Then when you requery the subform, you could use that value to do a find on the subform's recordset and set the subform's bookmark to the recordset's bookmark.

[dbustell]

Your secord paragraph pointed me in the right direction: I kept all the bookmark management in the main form and got things to work. I had to do some fiddling about and put in a couple temporary command buttons to determine what a bookmark value was at given times.

What was confusing me, and the focus of my original question, is that if you assign

subform.recordset = rst

then both

subform.activerecord = varbookmark

and

rst.bookmark = varbookmark

will move the cursor on the screen display of the subform, but the first one will not change the current record of the recordset.

I don't know if that is clear to you, but I got it working.

Thanks for your help.
Donald

[charlotte]

Glad you got it working. Your question about the bookmarks is actually pretty simple. ActiveRecord doesn't move the current record, it just points to another record in the recordset and lets you do stuff behind the scenes. If you want to make a particular bookmark your current record, you set the recordset's bookmark to the record's bookmark.

You can run into something similar on forms with multiple page breaks. if you set the focus to a control on an other page in code, that doesn't scroll the screen for you. The focus is on that page, but you can't see it.