Determining which subform is active

Question

All of the 5 subforms  on my order entry form have their own button which opens the SelectFabric  form.  When this form  (the fabric Select form) closes the info needs to be written back to that subform from which the button was clicked.  I was thinking some kind of a case statement, but I don't know how to determine which subform is active at the time the button is clicked.  Here is the code as it currently exists, but of course it only works for the OrderDraperyLines Sub form.  There are 4 more.  Thanks!

Private Sub Form_Close()

If Not IsNull(Me.cboColor) Then

Forms.orderentry.OrderDraperyLineItems.Form.Controls("FabricID") = Me.cboColor

End If

If Not IsNull(Me.Repeat) Then

Forms.orderentry.OrderDraperyLineItems.Form.Controls("Repeat") = Me.Repeat

End If

If Not IsNull(Me.Group) Then

Forms.orderentry.OrderDraperyLineItems.Form.Controls("Group") = Me.Group

If Not IsNull(Me.FabWidth) Then

Forms.orderentry.OrderDraperyLineItems.Form.Controls("FabricWidth") = Me.FabWidth

End If

End If

Forms.orderentry.OrderDraperyLineItems.Form.txtLining.SetFocus

End Sub

Answer 1

1.  I'd assume that Marshall's statement 'as long as the fabric select form is only opened from one of your subforms' doesn't hold good, as your original question only makes sense if one of a set of subforms  can arbitrarily be the one you need to reference when assigning values to its controls.  To use this approach you'd need to pass a reference to the subform to the FabricSelect form as its OpenArgs property when opening it.  The OpenArgs property can then be used to reference the subform when assigning the

2.  If you adopt the alternative of opening the FabicSelect form in dialogue mode, however, you cannot assign a value from it after it's been closed as he suggests  What you have to do in that situation is not close the dialogue form but hide it.  This also causes code execution in the calling procedure to recommence execution.  You'd then close the dialogue form after assigning the value from it:

   DoCmd OpenForm "FabricSelect", WindowMode:=acDialog   

   Me.Repeat = Forms!FabricSelect.Repeat

   DoCmd.Close acForm, "FabricSelect"

Hiding a dialogue form like this rather than closing it is a simple and efficient method, and I'd recommend you try it.

3.  Coming back to the method I suggested, which is a solution I've used successfully in similar contexts a number of times, the sequence of events is crucial.  Having declared the object variable publically you must initialize it by means of the Set command so that it retunes a reference to the subform before you use the variable when assigning a value to a control in the subform.

Answer 2

Yes, I am referencing the OrderLineItem subform in the module of a form other than its OrderEntry parent form.  The subform is being referenced from the onClose event of the SelectFabric form which I opened by clicking on a button from the Orderlineitem Subform.  I added the lines as follows to my mdlCommon module.

Option Compare Database

Public frmCurrentSub As Form

Unfortunately, I am still doing something wrong because I am getting the message Runtime error 91.  Object Variable or With Block variable not set.

thanks Again.

Answer 3

If you are referencing the subform in the module of a form other than its parent form the frmCurrentSub should be declared publically in the declarations area of a standard module so as to expose it throughout the database:

    Public frmCurrentSub As Form

Answer 4

thanks Ken,

Here what I did... unfortunately, I am getting a variable not defined error with the focus resting on the procedure to close the FabricSelect form highlighting the FrmCurrentSub as Variable not defined

 I added the line  Dim frmCurrentSub As Form at the top the the OrderEntry (Parent form) module

Next I added the code

Private Sub OrderDraperyLineItems_Enter()

Dim frmCurrentSub As Form

End Sub

then I modified the on close event for the fabricSelect form like this:

Private Sub Form_Close()

If Not IsNull(Me.cboColor) Then

frmCurrentSub.Form.Controls("FabricID") = Me.cboColor

End If

---

---

Thanks for taking a look.

Answer 5

In the parent form's module's declarations area declare a module level object variable:

    Dim frmCurrentSub As Form

In each subform control's Enter event procedure set the object variable to return a reference to the 'current' subform:

    Set frmCurrentSub = Me.ActiveControl.Form

You can then reference the 'current' subform like this:

    frmCurrentSub.Form.Controls("FabricID") = Me.cboColor

NB: a subform control is a control in a parent form's Controls collection which houses a subform