How to with a combo box

[Gord]

When clicking an item from a drop down list on a combo box, how does one
make another combo box make a selection from it's own list? No matter what
event I use for my code in the first combo box, seems to always generate an
error when I try and refer to the other combo box.

I'm aware of the necessity of setting the focus on the second box before
referring to it, unless one is using the default value. I'm too tired to
type out everything I've been playing around with (I've been at it for a
while now) and no matter what I always get some sort of error or unwanted
results. I'm hoping this is not something unusual and that someone can
simply show me a generic example of what might work.

Thanks,

Gord

[Rick Brandt]

Gord wrote:

When clicking an item from a drop down list on a combo box, how does
one make another combo box make a selection from it's own list? No
matter what event I use for my code in the first combo box, seems to
always generate an error when I try and refer to the other combo box.

I'm aware of the necessity of setting the focus on the second box
before referring to it, unless one is using the default value.

Actually that is incorrect. You only need to set focus to use the Text
property and that is almost never the one you want. You want the Value
property which can be set without first setting focus.

I'm
too tired to type out everything I've been playing around with (I've
been at it for a while now) and no matter what I always get some sort
of error or unwanted results. I'm hoping this is not something
unusual and that someone can simply show me a generic example of what
might work.

All it should take is...

Me.ComboBoxName = SomeValue

Is it a multi-column ComboBox? That can change things since you might need
to set the value to one found in a column that is not the one you see.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Steve]

Typically the first combobox has the value of the primary key in some table.
We'll call it CustomerID in TblCustomer. Then the second combobox has the
value of the related foreign key in another table. We'll call it CustomerID
in TblOrder. So in the first combobox you choose a customer and in the
second combobox you choose from a list of orders by that customer. The
rowsource of the first combobox is a query based on TblCustomer. CustomerID
is the first column in the query and the Bound Column property of the
combobox is 1. The combobox is named CustomerID. The rowsource of the second
combobox is a query based on TblOrder. OrderID is the first column in the
query, CustomerID is the second column in the query and the Bound Column
property of the combobox is 1. The combobox is named OrderID. The criteria
for CustomerID is Forms!NameOfYou rForm!CustomerI D.

Put the following code in the AfterUpdate event of the first combobox:
Me!OrderID.Requ ery
Me!OrderID.SetF ocus
Me!OrderID.Drop Down

When you select a customer in the first combobox, the second combobox will
dropdown and only display
orders by the customer you selected. Focus will be on the second combobox.

Steve

"Gord" <x1******@telus .netwrote in message
news:FKnvk.1160 1$nu6.3003@edtn ps83...

When clicking an item from a drop down list on a combo box, how does one
make another combo box make a selection from it's own list? No matter
what event I use for my code in the first combo box, seems to always
generate an error when I try and refer to the other combo box.

I'm aware of the necessity of setting the focus on the second box before
referring to it, unless one is using the default value. I'm too tired to
type out everything I've been playing around with (I've been at it for a
while now) and no matter what I always get some sort of error or unwanted
results. I'm hoping this is not something unusual and that someone can
simply show me a generic example of what might work.

Thanks,

Gord

[Gord]

I just figured it out.

I can click one combo box and set the other combo's value by applying the
appropriate column value from the combo just clicked. This may set the
listindex of the second combo to an incorrect record but by setting up a
public boolean I can determine which combo was clicked. I'll use the
listindex from that combo to verify the record I want.

Thanks for your efforts.

Gord

[Rick Brandt]

Gord wrote:
....

Maybe I misunderstand, but I'm not finding to be true what you say
about a combo box selecting the first value in its bound column that
has duplicates "even if you make the selection manually". When I
drop the box and click on a selection I'm finding that the listindex
property sets to the row I clicked on and not on the first one with a
matching value in its bound column. ...

Just tested in Access 97 and 2003. Given a Combo with columns...

John Doe 1
Jane Doe 2
Tom Doe 3
Harry Doe 4

....with the bound column being the second one, The Combo displays "John" no
matter which row I select from the drop-down. That is what I was describing
in case it wasn't clear.

You are correct that if I display the ListIndex property in a MsgBox in the
AfterUpdate event it will reflect the index of the row I selected, but if I
check the ListIndex again (after AfterUpdate has completed), it always
reflects the ListIndex value of the first row in the list with the bound
value I picked. In the example above that value is zero.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com