Greg989
asked on
MSAccess - Return to master form at record where left off
I have a columnar continuous form where I provide a button in header to open a single record tabular sub-form for data entry. The sub-form has an "OK" button to close the sub-form and return to the columna form. With the OK button "click" event on the sub-form I wish to not only close the sub-form but update (requery) the columnar form and return to the columnar form with the focus on the same record I was on when I started the whole process. I've tried all kinds of things and nothing seems to work. I found several ways to get the focus on the original record but I have to sacrifice the requery update and vice-versa. Can someone get me kick started. I'm in Access 2003 and trying to write the VBA for the "OK" button click event.
Tracy
Why don't you try storing the record item number you're on in a variable, then when you go back to the main form, just do a goto to that record number.
Bear in mind that the record numbers might change after you requery, if the number of records in the underlying data changes.
Storing the primary key value(s) and then executing the Recordset.FindFirst method should cope with this - unless the record has been deleted of course! :-)
Storing the primary key value(s) and then executing the Recordset.FindFirst method should cope with this - unless the record has been deleted of course! :-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I could be missing something (it's been a long day...), but won't the stored CurrentRecord value be wrong if records are subsequently deleted or inserted in the underlying record source?
If this is actually a problem, I guess storing/setting the form's Bookmark property would overcome this.
On the other hand, this could all be a non-issue! :-)
If this is actually a problem, I guess storing/setting the form's Bookmark property would overcome this.
On the other hand, this could all be a non-issue! :-)
ASKER
JezWalters: I am a bit concerned about broomee9's solution in light of your observation. So help me (I'm a newbie) understand specifically how I would write the VBA to use bookmark property instead. I think I can choose multiple solutions if your idea works out... and from the sounds of things it would be more robust. Thanks in advance....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
JezWalters- Thanks for the bookmark help. That appears to be a more robust solution.
If you feel that using the Form's Bookmark property is the right soluton, you should create a zero point General Community Support question requesting that the question be re-opened and then pick a different solution.
ASKER
Thanks to both broomee9 and JezWalters for the help on this one. I'll use broomee9's solution for my immediate issue and I'll use JezWalter's solution for all future applications.