How can I force an Access combobox to automatically select from its select set?

  • Thread starter Thread starter Frank Rudolph
  • Start date Start date
F

Frank Rudolph

I have a cascading sequence of events that I want to have happen in an
Access form.
When I select from the SELECT set of a combo box, called "code" I create a
new SELECT set for a "name" field that is a one element select set.
I did this by programming an onChange action that says:

Private Sub Code_AfterUpdate()
Name.RowSource = "NameType"
End Sub

'NameType" is a query that results in a single seletion that becomes the new
select set for the 'name' combo box, whose control source happens to be a
field called 'name'. This select set has only one element in it, which is
the new name corresponding to the 3-digit code entered in the code combo
box.

Selecting this new value of name in turn starts a cascade of other queries
that generates new select sets for several other fields.

The whole process works just fine if after changing the 'code' field, I then
pull down the select set (with only one element) for name and select it,
because the onChange action of 'name' triggers the queries for the dependent
fields.

But the requirement I'm trying to satisfy says that the name field should
automatically assume the value in its single element select set without me
having to pulldown and select.

I wonder if there isn't some VBA "magic" that I can put in the onChange
event subroutine Code_AfterUpdate() that forces the 'name' combo box to
assume the value of the only item in its select set.

You help would be GREATLY appreciated.

- Frank Rudolph
 
The whole process works just fine if after changing the 'code' field, I then
pull down the select set (with only one element) for name and select it,
because the onChange action of 'name' triggers the queries for the dependent
fields.
Click to expand...

You should be using the AfterUpdate event - which fires when a
selection is made - rather than the Change event - which fires AT
EVERY KEYSTROKE in the field.
But the requirement I'm trying to satisfy says that the name field should
automatically assume the value in its single element select set without me
having to pulldown and select.
Click to expand...

Simply set the value of the combo box to the proper value of its bound
column, and perform your action *there* (rather than in any event on
the combo box). None of the combo's events will fire unless the user
manually interacts with the combo; if you're running code already,
just perform whatever action you wish right in that code!
 
Back
Top