Can I move a parent form without repainting the subform

My parent form displays key data for a single record and my subform displays summary data for multiple records. The two forms are NOT linked and there is no On Current event for the parent form. When I run the following code from the subform, the parent moves to the correct record but the subform refreshes and moves back to the top of the list. Is there any way to stop this from happening? I want the subform display to remain as it was before this code runs.

        Set rs = frm.Parent.Recordset.Clone
        rs.FindFirst "DetailId = " & DetailId
        If Not rs.EOF Then
            frm.Parent.Bookmark = rs.Bookmark
        End If
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Whatever is causing it is not in the code you posted.  What event do you have this code on?  Do you have a Requery or Recalc on either your sub form or main form?


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
Rob4077Author Commented:
Hi Ron, This code is triggered by the On Click event. There's other code in there but I have stepped through the code (F8 step by step) and it is at this line of code that the parent and subform refresh. I do have requeries in other parts of the code but I cannot see where they're being triggered
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
do you have any events in your subform which refreshing the data on how it's being displayed in subform?



where and how your data is being displayed in subform?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

So you really don't have any Master/Child links between the 2 forms, right?  Without seeing the form, it may be hard to figure this one out.  Can you upload a copy of the database with only the 2 forms and some records that don't contain any private info?

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Does the same thing happen if you move the parent form manually to a different record using the Navigation buttons?
Dale FyeOwner, Developing Solutions LLCCommented:
Just to clarify, the subform is not related to the parent form in any way?  You are simply using it as a navigation form, to allow you to select the record to display on the main form?

The query that is the Record Source for the subform does not have a reference to any of the controls on the main form?
Rob4077Author Commented:
Just confirming that the subform is not linked to the parent form. The only criteria used in the subform's recordsource is "IS NOT NULL" for one of the fields. Unfortunately it is a very complex setup. In fact I have several other subforms resting on the parent form and there's lots happening when records are selected.

This question was raised to verify that it should be possible to accomplish what I need, and all your comments make it clear that I am not expecting the impossible. Clearly there is something else in the inter-relationships that is causing this issue so I will have to dig deeper.

Thanks for your help confirming this.
Dale FyeOwner, Developing Solutions LLCCommented:
Subforms are "generally" used to display data that is related to the main form on the page.

I try not to put these "disconnected subforms" which can be used to select records for another form, in the details section of a form.  If I find a need to do something like this, I will generally create a main form which has no recordset, display this "selector" subform on the left side of the form, full height, and then display the form with the data as a second subform on the main form.  I find it less confusing to users and to myself.
Rob4077Author Commented:
Yes that makes sense. Unfortunately in this case it's more of a case of poor evolution as a result of spec creep than anything else. I should have anticipated more to start with to avoid problems now.
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.