none
What subform is cursor on RRS feed

  • Question

  • Hello,

    I have a button on a main form that will generate an email to a customer by their account number. The account number can be on any of four subforms. The subforms represent the progress of an order (1. not yet sent - 2. at the vendor - 3. in stock ready to bill - 4. sent to customer). Each subform can have many orders, so I need to find the one that the cursor is in.

    Originally, I thought we would only send this based on information on one subform, so this works OK for one form:

    strAccountID = Nz(Forms![frmInvFirmOrders]![fsubFirmOrdNotYetSent].Form![CustomerID], "")

    But, now, we would like to do this with more than one subform. So, I need a way to know where the cursor is (was?) when the button is clicked. Of course, when you click the button on the main form, the focus has shifted, so I can't use that.

    My idea is that the user will click on the order they want to report on, then click the "send email" button on the main form.

    Thank you for any help!

    Albert


    Albert S

    Wednesday, March 21, 2018 8:44 PM

Answers

  • Hi Albert S,

    You had mentioned that below code is not working.

    strAccountID = "Forms![frmInvFirmOrders]!" & strCursorForm & ".Form![CustomerID]"

    Please refer the example below.

    Private Sub Command4_Click()
    Dim strAccountID, strCursorForm, ctrl_name As String
    
        strCursorForm = "Form1" ' ---Form 1 is sub form
        ctrl_name = "Text31"    ' ---Text 31 is textbox on sub form
        strAccountID = Me.Form(strCursorForm).Controls(ctrl_name).Value
        Me.Text2 = strAccountID ' ---Text 2 is the textbox on main form
    End Sub
    

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Albert S Thursday, March 22, 2018 4:49 PM
    Thursday, March 22, 2018 2:08 AM
    Moderator
  • strAccountID = "Forms![frmInvFirmOrders]!" & strCursorForm & ".Form![CustomerID]"

    Hi Albert,

    When the mainform is clicked, you can find the (last clicked) subform by:

        strAccountID = Me(strCursorForm).Form.ActiveControl

    If strCursorForm = "", then you can generate a message that a customer has not yet selected.

    I hardly use constructions like  Forms![frmInvFirmOrders],  but instead I use cur_form (the current form), assigned at the opening of the form. This has the advantage that you can generalize code, to be used for many forms. So my code would be like:

        strAccountID = cur_form(strCursorForm).Form.ActiveControl

    Imb.


    • Edited by Imb-hb Thursday, March 22, 2018 7:58 AM typo
    • Marked as answer by Albert S Thursday, March 22, 2018 4:49 PM
    Thursday, March 22, 2018 7:58 AM

All replies

  • Hi Albert,

    If all your subforms have different control names, then it's easy enough to find out which control had the focus before the user clicked on the button using Screen.PreviousControl.Name

    However, if all the subforms are the same, then it may be necessary to find out the name of the subform instead of the control.

    In that case, this function from Leigh Purvis might help to do that.

    Cheers!

    Wednesday, March 21, 2018 8:59 PM
  • But, now, we would like to do this with more than one subform. So, I need a way to know where the cursor is (was?) when the button is clicked. Of course, when you click the button on the main form, the focus has shifted, so I can't use that.

    Hi Albert,

    I work quite a lot with many subforms on a main form that are exactly the same.

    On the main form I have a hidden control that is filled with the name of the subform (or a code to construct the name of the subform) that is filled in the Enter event of the subform. Just before filling you can grab the name of the previous subform. After filling it contains the name (or code) of the "active" subform.

    Imb.

    Wednesday, March 21, 2018 9:17 PM
  • Ok, this approach looks good. I put a field for the name of the subform. Now, how do I get the name of the subfield concatenated so I get the CustomerID number:

    strCursorForm = Nz(Me.txtSubformName, "")

    This doesn't work:

    strAccountID = "Forms![frmInvFirmOrders]!" & strCursorForm & ".Form![CustomerID]"

    Thank you for the help,

    Albert


    Albert S

    Wednesday, March 21, 2018 11:45 PM
  • Hi Albert S,

    You had mentioned that below code is not working.

    strAccountID = "Forms![frmInvFirmOrders]!" & strCursorForm & ".Form![CustomerID]"

    Please refer the example below.

    Private Sub Command4_Click()
    Dim strAccountID, strCursorForm, ctrl_name As String
    
        strCursorForm = "Form1" ' ---Form 1 is sub form
        ctrl_name = "Text31"    ' ---Text 31 is textbox on sub form
        strAccountID = Me.Form(strCursorForm).Controls(ctrl_name).Value
        Me.Text2 = strAccountID ' ---Text 2 is the textbox on main form
    End Sub
    

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Albert S Thursday, March 22, 2018 4:49 PM
    Thursday, March 22, 2018 2:08 AM
    Moderator
  • strAccountID = "Forms![frmInvFirmOrders]!" & strCursorForm & ".Form![CustomerID]"

    Hi Albert,

    When the mainform is clicked, you can find the (last clicked) subform by:

        strAccountID = Me(strCursorForm).Form.ActiveControl

    If strCursorForm = "", then you can generate a message that a customer has not yet selected.

    I hardly use constructions like  Forms![frmInvFirmOrders],  but instead I use cur_form (the current form), assigned at the opening of the form. This has the advantage that you can generalize code, to be used for many forms. So my code would be like:

        strAccountID = cur_form(strCursorForm).Form.ActiveControl

    Imb.


    • Edited by Imb-hb Thursday, March 22, 2018 7:58 AM typo
    • Marked as answer by Albert S Thursday, March 22, 2018 4:49 PM
    Thursday, March 22, 2018 7:58 AM
  • Hello,

    Thank you all for the quick answers and help. Referencing the forms as you mention works a lot better. A lot of our code needs to be updated.

    I updated the code to reference the current form and everything worked.

    Take care,

    Albert


    Albert S

    Thursday, March 22, 2018 4:51 PM