Pausing for Combo box input

  • Thread starter Thread starter Dennis Snelgrove
  • Start date Start date
D

Dennis Snelgrove

Is there a command or method that will cause program execution to stop in a
combo-box until the user has made a choice, then the programming will move
on? I've got a form with several levels of a
Department/Division/Section/Location hierarchy, each level consisting of a
"Many-to-Many" relationship. What I've got working so far is for a user to
choose a Location (the most detailed level of the tree) and the VBA will
auto-fill each successive level if that level only has one matching record.
The idea is that about 75%-80% of the relationships are in reality
"One-to-Many" and since I'm an incredibly lazy encoder, it made more sense
to start at the most detailed level and let the computer do the work of
back-filling the previous levels of the hierarchy whenever possible. In
those instances of a level belonging to more than one previous level (a true
Many-to-Many) it would do a DropDown on the level's combo-box, wait for the
encoder to chose the appropriate record, then continue on. As it stands now,
I've got all this working, but I've got to Tab through each of the
combo-boxes in order to ensure that Access will stop if a combo-box has more
than one record to chose from. Is there a way to cause the combo-box to
pause until the encoder has made a choice, then move on within the same
Subroutine, rather than using a bunch of separate Event Procedures?

Thanks for any help...

Dennis
 
Dennis said:
Is there a command or method that will cause program execution to stop in a
combo-box until the user has made a choice, then the programming will move
on? I've got a form with several levels of a
Department/Division/Section/Location hierarchy, each level consisting of a
"Many-to-Many" relationship. What I've got working so far is for a user to
choose a Location (the most detailed level of the tree) and the VBA will
auto-fill each successive level if that level only has one matching record.
The idea is that about 75%-80% of the relationships are in reality
"One-to-Many" and since I'm an incredibly lazy encoder, it made more sense
to start at the most detailed level and let the computer do the work of
back-filling the previous levels of the hierarchy whenever possible. In
those instances of a level belonging to more than one previous level (a true
Many-to-Many) it would do a DropDown on the level's combo-box, wait for the
encoder to chose the appropriate record, then continue on. As it stands now,
I've got all this working, but I've got to Tab through each of the
combo-boxes in order to ensure that Access will stop if a combo-box has more
than one record to chose from. Is there a way to cause the combo-box to
pause until the encoder has made a choice, then move on within the same
Subroutine, rather than using a bunch of separate Event Procedures?
Click to expand...


It sounds like you're trying to control all the combos from
the lowest level combo's AfterUpdate event. If so, since
Access is event driven, you're going to run into some
ridiculously convouluted logic. Instead, you should let
each level deal only with the next level up so no one level
has to wait on another. Here's an AfterUpdate event that
may or may not help you:

Private Sub Combo0_AfterUpdate()
With Me.Combo2
.Requery
.SetFocus
If .ListCount = 1 Then
.Value = .ItemData(0)
Combo2_AfterUpdate
Else
.Value = Null
.Dropdown
End If
End With
End Sub
 
Sorry for taking so long to get back on this, Marshall.

This works perfectly. It's upped the encoding speed by almost a factor of 4.
Thank you very much for your help.
 
Dennis said:
Sorry for taking so long to get back on this, Marshall.
Click to expand...

Not a problem.
This works perfectly. It's upped the encoding speed by almost a factor of 4.
Click to expand...

That's great. I love it when it not only works, but makes
things better for the users too.
Thank you very much for your help.
Click to expand...

You're very welcome.
--
Marsh
MVP [MS Access]


 
Back
Top