Link to home
Start Free TrialLog in
Avatar of scs-contracts
scs-contracts

asked on

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?
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

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

ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of scs-contracts
scs-contracts

ASKER

Great thanks!  I have one more question, can you pass 2 parameters.  I want to also pass the control source for the form.
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: https://msdn.microsoft.com/en-us/library/office/gg278528.aspx

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).
awesome!  Thanks!