Switching from a subform on one tab, to a subform on another tab, finding a record

I have a form with a tab control. One tab contains a data entry single form for delivery dispatches. Another tab contains a continuous form that lists all the dispatches for an individual location, chosen by a combo box on that form. The continuous form has an object containing the dispatch id. The single form has a hidden text box containing the dispatch id. If I'm on the continuous form, on a particular record, how can I switch to the single form, displaying the dispatch corresponding to the current record on the continuous form?

Thank you
Tom LobbAccess database developerAsked:
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.

Gustav BrockCIOCommented:
Though not two continuous subforms, this article may help you:

Synchronizing Multiple Subforms in Access
Dale FyeOwner, Developing Solutions LLCCommented:
Assuming your have controls:


And that the single form subform is not filtered based on a master child relationship to the main form,

I generally do is something like using the click event of a button on the continuous form, or the double click event of the record selector on that form:
Private Sub cmd_EditRecord_Click

    with me.parent.sfSingle.Form.RecordsetClone
        .FindFirst "DispatchID = " & me.txt_DispatchID
        if .nomatch then
            msgbox "no matching record"
            me.parent.sfSingle.bookmark = .bookmark
            me.parent.tabMain = 1               'where you replace 1 with the tab page # associated with the single form.
    end with

End Sub

Open in new window


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
Tom LobbAccess database developerAuthor Commented:
Thanks for the feedback.

The first part is working. But on the line, 'Me.Parent!frm_Dispatch.Bookmark = .Bookmark', I get the error 'Object doesn't support this property or method'. Could this be that the record source of the single form is a query, using a few tables?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Dale FyeOwner, Developing Solutions LLCCommented:

Me.Parent!frm_Dispatch.form.Bookmark = .Bookmark

note that i added the reference to the "form" object between the subform control name (frm_Dispatch) and the .Bookmark property.

Tom LobbAccess database developerAuthor Commented:
That did it. Thanks very much.
Dale FyeOwner, Developing Solutions LLCCommented:
Don't forget to close out your question.
Tom LobbAccess database developerAuthor Commented:
Thanks for the help
Dale FyeOwner, Developing Solutions LLCCommented:
glad to help
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.