Solved How to get the Form name that triggered an event.

[raziel3]

I have 3 forms that basically does the same data entry one for Inventory (deInventory), Expenses (deExpenses) and Foreign Purchases (deForeign). I have an event set up on each of them to detect available vendor credits.

Private Sub SUBTOTAL_AfterUpdate()
Dim rs As DAO.Recordset
Dim CreditCheck As String
Dim MsgBoxAns As String

CreditCheck = "SELECT VENCR.VENDISP, SUM(VENCR.AMOUNT) " & _
                "FROM VENCR " & _
                "WHERE VENCR.VENDISP = " & SQLQuote(txtVENDISP) & _
                "GROUP BY VENCR.VENDISP " & _
                "HAVING Sum(VENCR.AMOUNT)<> 0"
              
Set rs = CurrentDb.OpenRecordset(CreditCheck, dbOpenSnapshot)

If rs.RecordCount > 0 Then
    If MsgBox("Vendor Credits Available", vbYesNo, "Apply Credits?") = vbYes Then
        DoCmd.OpenForm "popVENDCR", acNormal
        Forms!popVENDCR.Form.txtVENDISP = VENDISP
    End If
  
End If

rs.Close
End Sub

After the user selects whatever I have another event on the popVENDCR to transfer the credit amount being claimed to the data entry forms.

Private Sub btnAPPCR_Click()
If txtCRUSED > txtCRTTL Then
    MsgBox "Cannot Apply Credits More Than What Is Available", vbCritical, "Credit Application Error"
    txtCRUSED = ""
    txtCRUSED.SetFocus
Else
    Forms![deInventory].Form.txtCMAMT = txtCRUSED
    DoCmd.Close acForm, "popVENDCR", acSaveYes
End If
End Sub

The question. On this line Forms![deInventory].Form.txtCMAMT = txtCRUSED, instead of doing 3 different pop up forms, how can I get Forms![deInventory] to be dynamic so whatever form that triggered the pop up form, txtCRUSED will be transferred to that form's field?

 

[gemma-the-husky]

I think you will have to pass the form name into the sub, or have the same sub in each form, and then you can just use me.name to get the name of the form.

Maybe screen.activeform.name might give you the name of the current form.

You might want to use a numeric value instead of the form name, to avoid wasting space in the database.

 

[Pat Hartman]

Pass in a reference to the Calling Form using the OpenArgs.

 

[MarkK]

I typically solve this problem as demonstrated. This ensures better separation of concerns. Note that clicking OK in the popup only hides the form, doesn't close it, so the function can return a value from the popup.

 

[MajP]

At the top of the Called form put the following

Public ReturnControl as Access.control

In the calling form pass a reference to the control to get the return value

DoCmd.OpenForm "popVENDCR", acNormal
Forms!popVENDCR.Form.txtVENDISP = VENDISP
set Forms!popVENDCR.ReturnControl = me.txtCMAMT

Then in the popup

Forms![deInventory].Form.txtCMAMT = txtCRUSED
becomes
ReturnControl = txtCrused

 

[raziel3]

Once again @MajP for the win. Your solutions are so simple and easy to implement. Much thanks.

 

[MajP]

If you open the form ACDIALOG you will have to pass the name of the control because code execution stops and you would not be able to modify the ReturnControl. That line of code would not execute until after you close the pop up. Unfortunately you cannot pass Object references in open args.