Solved: Requerying a combo box on parent form

[VWP1]

I have a continuous form, with a Label_Click() event, which opens a popup form.

The popup form is a "data entry only" form, and adds a record to the supporting table - the same table which feeds the combo box on the parent form (Row Source).

When I enter a new item in the popup form (which is adding a new record in the same table as the combo box), I close the popup form, and the combo box on the parent form doesn't display the new record.

SO...

I added a short procedure to the popup Form_Close() event:

Private Sub Form_Close()
DoCmd.Requery
End Sub

The combobox (named cboRecipeIngredients) doesn't have the new record after closing the popup form. I need it to.

I'm trying to experiment with Me.cboRecipeIngredients.Requery in the parent form, once the popup has been closed.

I cannot seem to quite get the combo box...ALONE...to requery. Requerying the entire form results in the form closing and opening, it seems, and that opens up a can of worms with my cursor on the particular recipe entering process.

Can the combo box...ALONE...be requeried?

[tcoombes]

You cannot use Me expression on the parent form at that point. If you are in the popup then the main form is still open so to requery it you need to fully qualify it.

ie

Forms![NameofParentForm]![NameofComboBox].Requery

Another alternative is to reset the rowsouce of the parent combo box by

Forms.NameOfParentForm.NameOfComboBox.RowSource = " Put Exactly the same as in the existing Rowsource Properties Box"

That second option is probably better than requery

Hope that helps

[VWP1]

---snippet---
Forms.NameOfParentForm.NameOfComboBox.RowSource = " Put Exactly the same as in the existing Rowsource Properties Box"
---end snippet---

If the above suggestion is used -I'm assuming in the VBA of the popup form - then, that popup form can only be used with that particular parent (main) form. What if (for convenience sake) I have another main form which uses that same popup form (because another main form may also have a combo box which needs this popup form)?

In other words, I've got a main/parent form called RecipeIngredients1 (part 1 of the recipe), and another main form called RecipeIngredients2...etc. Afterall, some recipes have multiple parts, and sometimes, even as many as 4 parts. And..., each main form has a combo box which lists the ingredients choices.

I would need a popup for each main form, if the VBA were to reference only one of the main forms. Is there a way to use one popup form, and accomplish this?

[tcoombes]

Yes but its more complex

you could use the syscmd to determine what forms are open then you can update all those combo boxes

Dim IsOpen as integer

IsOpen = SysCmd(SYSCMD_GETOBJECTSTATE, A_FORM, "FormName")
If IsOpen <> 0 Then
Then the form is open so you can update its combo

Forms![FormName]![ComboField].Requery
End If

Repeat this for each of the forms that could be open

If you use it on 4 forms you need to test which forms are open and then update those combo boxes

[hansup]

You can open the popup form with acDialog as the WindowMode option. That option will open the popup form with its Modal and PopUp properties are set to Yes. Modal will prevent any following code in the parent form from running until the popup form closes.

Try this code in your Label_Click() event procedure.

DoCmd.OpenForm "YourPopupForm",WindowMode:=acDialog
Me.cboRecipeIngredients.Requery

With this approach, the popup form does not need to know or care about the name of the parent from which called it.

[VWP1]

Thank you VERY much! I can apply this to limitless popup and main form pairings, now!:beerchug: