How can I force a combo box to select automatically?

  • 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
 
1) The OnChange and AfterUpdate events aren't the same. The correct one to use is the
AfterUpdate, which it appears you are using.

2) Try not to use reserved words for names of controls, fields, etc. Name is a reserved
word. While it may not cause you a problem here, it may sometime in the future.

3) If there is only one item that is ever going to be in the combo box's drop down then
why use a combo box instead of a text box?

4) To select the first item in the combo box's drop down:

Me.cboMyCombo = Me.cboMyCombo.Column(0, 0)

You will need to adjust the first 0 to which ever column is the bound column if you have
more than one. The column value is zero based, so the first one is 0, the second one is 1,
etc.
 
Back
Top