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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?



Dale FyeOwner, Developing Solutions LLCCommented:

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"
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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


Dale FyeOwner, Developing Solutions LLCCommented:
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
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
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.
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!!
Dale FyeOwner, Developing Solutions LLCCommented:
glad I could help.

The second example was just a test to see whether it was recognizing the frm object.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.