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.
Greg989Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TracyVBA DeveloperCommented:
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.
0
JezWaltersCommented:
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!  :-)
0
TracyVBA DeveloperCommented:
You can use something like this:

This will record the current record you're on:
currentRecordID = Me.CurrentRecord

Then when you come back to the Main form, after the refresh do this:
DoCmd.GoToRecord , , acGoTo, currentRecordID

This will goto whatever record you were on, before you left the main form.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

JezWaltersCommented:
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!  :-)
0
Greg989Author Commented:
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....
0
JezWaltersCommented:
You'll have to re-open the question to accepted a different solution (or a different combination of solutions), but using Bookmarks is actually quite similar to what broomee9 proposed.
Declare a module-level Variant variable outside all Functions/Subs at the top of the Form's module and call it (say) mvarBookmark:
Dim mvarBookmark As Variant
It's then just a simple matter of setting this value before you open your popup form:
mvarBookmark = Bookmark
and assigning the Form's Bookmark property to the stored value when you want to return to the previous record:
Bookmark = mvarBookmark
0
Greg989Author Commented:
JezWalters- Thanks for the bookmark help. That appears to be a more robust solution.
0
JezWaltersCommented:
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.
0
Greg989Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.