How can I force a combobox 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
 
Frank Rudolph said:
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
Click to expand...

I think you'll need to do two things: (1) set the combo box's value to
its first list item, and (2) explicitly call the combo box's AfterUpdate
event procedure (if that's where your code is stored that you refer to
as an "onChange" action). Incidentally, please don't confuse the
"Change" event with the "AfterUpdate" event; they're different events,
and though they can often be used for similar things, you're usually
better off using the AfterUpdate event.

Also incidentally, "Name" is a *terrible* name for a control or a field.
Many objects in Access have a Name property, and by naming your own
objects "Name", you run the risk of confusing Access when you refer to
them. I suggest you change this. In the code I'm about to suggest,
I've used "cboName" as the name of your combo box.

Your code in Code_AfterUpdate might look something like this:

Private Sub Code_AfterUpdate()

With Me!cboName
.RowSource = "NameType"
.Value = .ItemData(0)
End With

Call cboName_AfterUpdate

End Sub
 
Did you try:

1. Simply setting the Combo Box to the value from VBA code, as in

Me![cboYourComboName] = whatevervariableholdsthename

or

2. Setting the default value of the Combo to a fully qualified
reference to ItemData(0), then Requerying it?

If you need to follow up, please do so here in the newsgroup, not by
e-mail. Thanks.

Larry Linson
Microsoft Access MVP
 
Back
Top