[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-05
9
Medium Priority
?
560 Views
Last Modified: 2013-11-28
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?
0
Comment
Question by:mlaurin
  • 5
  • 4
9 Comments
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 36918369
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
 

Author Comment

by:mlaurin
ID: 36918544
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36918587

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:mlaurin
ID: 36918779
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36918857
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
 

Author Comment

by:mlaurin
ID: 36919465
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
 

Author Comment

by:mlaurin
ID: 36919542
Wait - I am trying something with your first suggestion, it might work.  Think I made an error.  Will get back to you soon.
0
 

Author Comment

by:mlaurin
ID: 36919655
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36919828
glad I could help.

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses
Course of the Month18 days, 21 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question