Can I update all combo boxes on all loaded forms?

[lambertsm]

Been looking through the forums and learning a lot but don’t see this
particular problem addressed . . .
I am trying to update my combo boxes when new data is added. Usually, the
updates need to happen in a form that is not the current focus. (So that
when the current form closes the other forms will be updated) I can find
all sorts of information on how to do this if I know the specific forms to
update. However, I’ve got a whole lot of forms (50+) with multiple paths to
reach other and I would rather not have to think through every single form
path the user took to get there that I would then need to update when a piece
of data is added. Also, I’ve been running into problems when I do
specifically define the forms to update because if there is more than one way
to get to the form where I am adding data I get an error message that the
other form (the alternate path) isn’t open to update it.

Basically when I add data I would like to be able to call up some sort of
public code that finds all my open forms and then updates everything.

I found this code that uses a pretty interesting approach of bookmarking all
the open forms, and then closing them to reopen again at the bookmarked
record:
http://www.mvps.org/access/forms/frm0056.htm
Unfortunately, this won’t work for me as often users are mid-add when they
have to go to a subform to add linked data. Example: In the middle of
adding a new product they find they also need to add a new package code, so
they click on add a new package, add it, and then (hopefully) come back to
the original screen to find their new package in the combo box.

I was thinking some sort of combination of a function that lists all open
forms and then a function that requeries them all. Found some cool requery
code here from Dirk Goldgar:
http://www.accessmonster.com/Uwe/Forum.aspx/access-formscoding/7363/Need-list-of-all-loaded-forms
But I don’t know how to find and pass through each open form in the code.

Thanks in advance for any ideas you may have. I am pretty new to all this
and basically have a trial and error approach to programming, but I feel like
I am just hitting my head against the wall with this one.

Shauna

 

[Allen Browne]

Hi Shauna.

What I do is to create a single function that handles the updating of any
combos that are open on screen.

My default form is set up with its Form_AfterUpdate set to:
Call NotifyCombos(Me.Name)
and its Form_AfterDelConfirm set to:
Call NotifyCombos(Me.Name, Status)
Consequently, each form calls this generic function as it gets created.

Once all the forms are in place, I then fill in the details of the
NotifyCombos() function. It's a massive Select Case statement that handles
all forms in the application. This kind of thing:

Public Function NotifyCombos(strSourceForm As String, _
Optional iStatus As Integer = acDeleteOK)
Dim strForm As String 'Name of form under consideration.

If iStatus = acDeleteOK Then
Select Case strSourceForm
Case "frmClient"
strForm = "frmOrder"
If IsLoaded(strForm) Then
Forms(strForm)!ClientID.Requery
End If

Case ...

End Select
End If
End Function

 

[Klatuu]

Look in VBA Help for the AllForms collection.
The example shows how to loop through all your forms and determine whether
they are open.

 

[lambertsm via AccessMonster.com]

Thanks to both of you for your help!