Can I update all combo boxes on all loaded forms?

  • Thread starter Thread starter lambertsm
  • Start date Start date
L

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
 
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
 
Look in VBA Help for the AllForms collection.
The example shows how to loop through all your forms and determine whether
they are open.
 
Back
Top