Change subform source object based on another form

I have 2 forms - frm_search_contracts and frm_search_shortfalls that opens another form (frm_contracts_main), however the subform will be different depending on which form opens it.  If frm_search_contracts opens frm_contracts_main then the subform source object shoud be "fsub_contracts_reconcile".  If frm_search_shortfalls opens frm_contracts_main then the subform source object should be "fsub_contracts_shortfall".

Any ideas how I can put this in the forms code?
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.

Dale FyeOwner, Developing Solutions LLCCommented:
best method would be to use the OpenArgs argument of the OpenForm method.  This allows you to pass a value (the sourceObject of the subform to this new form, as a value).

Then use the Form_Load event of the second form to set the SourceObject of the subforms, something like:
Private Sub Form_Load

    if me.OpenArgs & "" <> "" Then
        me.subformControlName.SourceObject = me.OpenArgs
    end if

End Sub

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use the OpenArgs method to open your frm_contracts_main form, and then react to that. To do that, use an OpenForm method like this when you need to open frm_contracts_main:

DoCmd.OpenForm "frm_contracts_main", , , , , , "contracts"

Then in frm_contracts_main, in the Open event:

If Nz(Me.OpenArgs, "") <> "" Then
    CASE "contracts"
      Me.YourSubformControl.SourceObject = "fsub_contracts_reconcile"
    Case "shortfalls"
      '/ the other subform
  End Select
End If

You'd have to do the same when you open the contracts from from the shortfalls form, but you'd pass in "shortfalls" as the OpenArgs


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
scs-contractsAuthor Commented:
Great thanks!  I have one more question, can you pass 2 parameters.  I want to also pass the control source for the form.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can pass in multiple items in the OpenArgs method. To do that, you would then parse those multiple items in the receiving object's Open or Load event. For example, if I wanted to pass in the CustomerID and AddressID, I might do this:

DoCmd.OpenForm "frm_contracts_main", , , , , , "FORMOCO|8898"

Note I've separated them with a vertical bar. So in the receiving object's Load event, I use Split to get to the component parts:

Dim CustomerID As String
Dim AddressID As Integer
If Nz(Me.OpenArgs, "") <> "" Then
  CustomerID = Me.OpenArgs.Split("|")(0)
  AddressID = CInt(Me.OpenArgs.Split("|")(1)
End If

The Split function does exactly what it says - it "splits" a string value at each occurrence of the passed in delimiter (in this case the vertical pipe). The syntax I used simply grabs the first item in the resulting array (at the 0 position) for the CustomerID, and the second item for the AddressID.

Split Function:

You can also use Global Variables to do this, if you'd prefer. IMO it's more difficult to manage, since you must be very, very diligent to always set/reset those variables (otherwise you end up with the wrong data in the wrong place).
scs-contractsAuthor Commented:
awesome!  Thanks!
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.