Looping trough combo boxes on two subforms

  • Thread starter Thread starter Anth Stever
  • Start date Start date
A

Anth Stever

I have a form with two subforms (each sbf is on a separate page of a tab
control - does that make a difference?) and each of the subforms has combo
boxes. I've got hyperlink labels above the combo boxes to edit the contents
of the combo boxes (right now they just bring up datasheets - later they
will be forms). The effect I'd like is to have a user be able to edit a
combo box and when they get back to the form the contents of the combo box
is refreshed to show any new items or have deleted ones gone. Ideally, I'd
like to have a generic function that I can call from some event (which one?)
to handle this. Seems like a common task yet I haven't found a function
"floating out there" yet...

Thanks!

/\nth
 
Anth, if you find a function that handles all this, please let us know.

The AfterUpdate and AfterDelConfirm events of my forms call a function in a
standard module. Late in development, this function gets a Select Case for
each bound form in the app. Each case checks for any forms (including
subforms) that have a dependency on this lookup list, and (if loaded)
performs a Requery of the combo. Case Else prints the name of the unhandled
form to the debug window in case you missed one. The function is simple, but
lengthy.

IMHO, it's worth the effort for the very flexible interface this approach
provides. I don't think it would be possible to write something that
auto-discovers the dependencies based on the RowSource of the combos.
Sometimes the RowSource is dynamically altered in code, and often it
contains query statements that mean not all apparent dependencies are real.
 
One approach I considered was to programmatically walk through all the combo
boxes on the form (and its subforms) On Activate... The code was beyond me.
Would that work?
 
Anth said:
I have a form with two subforms (each sbf is on a separate page of a tab
control - does that make a difference?)
Click to expand...

No, it does not make a difference.
and each of the subforms has combo
boxes. I've got hyperlink labels above the combo boxes to edit the contents
of the combo boxes (right now they just bring up datasheets - later they
will be forms). The effect I'd like is to have a user be able to edit a
combo box and when they get back to the form the contents of the combo box
is refreshed to show any new items or have deleted ones gone.
Click to expand...

The code to use a form to edit the combo box's row source
table is just:

DoCmd.OpenForm "theform", acNormal, _
WindowMode:=acDialog
Me.subform1.Form.combobox.Requery
Ideally, I'd
like to have a generic function that I can call from some event (which one?)
to handle this. Seems like a common task yet I haven't found a function
"floating out there" yet...
Click to expand...

Doesn't seem to warrent a generic function, but if you
really feel the need, it'll have to have arguments for the
name of the form to open, the name of the form that contains
the subform, the name of the subform control and the name of
the combo box:

Public Sub EditCombo(ComboForm As String, _
MainForm As String, SubForm As String, _
ComboBox As String)
DoCmd.OpenForm ComboForm, acNormal, _
WindowMode:=acDialog

Forms(MainForm).Controls(SubForm).Form.Controls(ComboBox).Requery
End Sub
 
Yes, you could use the Activate event of a form to requery all combos on it
and its subforms (recursively for sub-subforms).

If you only need to do this for one form and the combos contain small lists,
that may be all you need. As a generic solution, though, it does not look
efficient to me. If your app. commonly has multiple forms open, simply
switching to a different form and back does not imply a change of data, and
requerying large combos can be time-consuming, causing a delay when
switching forms. Further, the Activeate event won't catch the cases where a
form's combo is dependent on itself (e.g. if there is a combo used for
record navigation in the header of the form).

If you think it's worth the effort to write a generic function, your code
would need to loop through the Controls collection of the form. If the
ControlType = acComboBox (or acListbox?), Requery. For acSubform, the code
would need to call itself again (i.e. recursively), since this could go to 7
levels deep.
 
Back
Top