On close event of pop of form to requery an "open main form" instead of giving form name (Forms!MainFormName.ComboBoxName.Requery)?

I have a main form that has a combo box.  If an item is not in the combo box list, you double click it and it opens a pop up form to add the info.  The pop up form has an On Close event that requeries the combo box on the main form so that the item will show up in the list.

Private Sub Form_Close()
Forms!MainFormName.ComboBoxName.Requery
End Sub

That part works.  
My problem is that I actually have 6 main forms that have the identical combo box that I want to open a single pop up form.  Since the On Close event of the pop up form refers to a specific form name I would need 6 pop up forms to accomplish this.  Is there a way to word it so that it requeries whatever form is open at that time?
mlaurinAsked:
Who is Participating?
 
Dale FyeCommented:
Easiest way to do this is to include an OpenArg (the last argument) in the Docmd.OpenForm method which indicates the name of the form that called the popup.

Then, in the Open event of the popup, you could set the value of a variable that is private within that form, something like:

Private frm as Form

Private Sub Form_Load

    set frm = forms(me.Openargs)

End Sub

Then, in the close event, you use:

Private Sub Form_Close

    frm.controls("comboboxname").requery

End Sub


0
 
mlaurinAuthor Commented:
Thanks fyed for your response.  Having trouble with making it work.

I think my problem is that I do not understand the first part of your instruction:
"include an OpenArg (the last argument) in the Docmd.OpenForm method which indicates the name of the form that called the popup."

where, how, what?? - sorry but I am unclear on what this means and what I do with it.

Currently when you double click the drop-down it opens the pop up form:

Private Sub AnalystId_DblClick(Cancel As Integer)
DoCmd.OpenForm "AddAnalyst"
End Sub

I think you mean I need to add something here?



0
 
Dale FyeCommented:

The syntax for the OpenForm method looks like:

Docmd.OpenForm FormName, [View], [FilterName], [WhereCondition], [DataMode], [WindowMode], [OpenArgs]

Only the FormName is required, but if you add the acDialog as the WindowMode as I did below, your users will not be able to go back to the main form until you close your popup.  If you add the name of your calling form as the last argument (as I have done below), then you can use that argument to set your frm variable in your popup form.

docmd.OpenForm "FormName", , , , , acDialog, "frmMain"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mlaurinAuthor Commented:
Ok - I put that in the dblclick event of the dropdown control on the main form:

Private Sub AnalystId_DblClick(Cancel As Integer)
DoCmd.OpenForm "AddAnalyst", , , , , acDialog, "DataEntryContinuous"
End Sub

The pop of form opens, but when I close it get the following error:
Run-time error '91':
Object variable or With block variable not set

When I click on debug it takes me to this part "frm.Controls("AnalystId").Requery" of the following code that is in the Form Load and Form Close events.

Option Compare Database
Private frm As Form
Private Sub Form_Load()
    Set frm = Forms(Me.OpenArgs)
End Sub
Private Sub Form_Close()
frm.Controls("AnalystId").Requery
End Sub


0
 
Dale FyeCommented:
My guess is that "DataErrorContinuous" is not the name of the form that is opening the "AddAnalyst" popup form.

You could actually use the following line to make sure you pass the actual name of the form, and would be able to reuse this line without further editing:

DoCmd.OpenForm "AddAnalyst", , , , , acDialog, me.name

Is the control (I assume a combo box) name on the calling form "AnalystID" or is that the field name of the field that is bound to the control?  You should be using the name of the control, and it will have to be the same on all of the calling forms.

Try this:

Private Sub Form_Close()

    Dim ctrl as control

    For each ctrl in frm.controls

        debug.print ctrl.name

    Next

End Sub

That should print the name of each of the controls on your calling form
0
 
mlaurinAuthor Commented:
Hi - well, still not working.

DataEntryContinous is the main form name.  On the main form is the control (dropdown) named "AnalystId".  Click on the control "AnalystID" to open the pop up form named "AddAnalyst".

So I put your code in the open from the control "AnalystID":
Private Sub AnalystId_DblClick(Cancel As Integer)
DoCmd.OpenForm "AddAnalyst", , , , , acDialog, Me.Name
End Sub

On the pop up form "AddAnalyst" I have the following:
Option Compare Database
Private frm As Form
Private Sub Form_Load()
    Set frm = Forms(Me.OpenArgs)
End Sub
Private Sub Form_Close()
Dim ctrl As Control

    For Each ctrl In frm.Controls

        Debug.Print ctrl.Name

    Next
End Sub

I get the same error as before when closing the pop up form:
Run-time error '91':
Object variable or With block variable not set
0
 
mlaurinAuthor Commented:
Wait - I am trying something with your first suggestion, it might work.  Think I made an error.  Will get back to you soon.
0
 
mlaurinAuthor Commented:
My apologies - your first solution works! When I put in the code, I did not go back to the form properties and choose it for the On Load.  Thank you so much!!
0
 
Dale FyeCommented:
glad I could help.

The second example was just a test to see whether it was recognizing the frm object.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.