Use VBA to set or select a specific row in a bound combo box

[Vmusic]

Hi,
I'm using MS Access 2002. I have a form with a combo box built from a
query that returns one column, and that one column is the bound column.

How do you use VBA to programmatically change which row in the combo
box is selected??
I looked at the forum and the answers aren't very clear. Of course the
MS Access help file is worthless.

Thanks!!
Vmusic

[pietlinden]

me.cboPickSomething = "SomeValue From the List"

[Vmusic]

No... that's not it.

My question was:
How do you use VBA to programmatically change which row in the combo
box is selected??

I don't want to set the value to some arbitrary string - I want to use
VBA code to select one of the valid rows or items in a bound combo box

Please.......
Thanks
Vmusic
pi********@hotmail.com wrote:

me.cboPickSomething = "SomeValue From the List"

[Bob Quintal]

"Vmusic" <ak*****@irisicom.netwrote in
news:11*********************@s13g2000cwa.googlegro ups.com:

No... that's not it.

My question was:
How do you use VBA to programmatically change which row in the
combo box is selected??

I don't want to set the value to some arbitrary string - I
want to use VBA code to select one of the valid rows or items
in a bound combo box

Please.......
Thanks
Vmusic
pi********@hotmail.com wrote:

>me.cboPickSomething = "SomeValue From the List"

Yes that is it. it's the only way that works. You as a
programmer simply need to examine the list, extract the string
you want from the list, and then do as you were told

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Vmusic]

No, No, No..... what kind of .... is this programming language

Look I have a combo box. It is populated from data that runs from a
query- and it is bound to a column in a table.

When the user changes or selects a different value than what is
initially displayed - the BeforeUpdate or AfterUpdate method fires.

I want to prompt the user to confirm they REALLY want to change their
value - If they don't confirm - or say no, then I want to change the
value back to what it originally was. In order to change the value back
- I have to programattically change the ListIndex or some stupid
property in the combo box.

How do you use VBA to programmatically change which row in the combo
box is selected??

I do not want to set it to some arbitrary string value. I want the
actual row selected to be programmatically changed... because it's
bound to a value in a table - which itself is a lookup.

There HAS TO be a way to programmatically change the selected row in a
combo box?

Sorry - it seems like a simple question/ task.. but I have yet see a
clear answer, maybe even with a VBA code example

PLEASE don't tell me that if I write the code in the BeforeUpdate
method, and Exit Sub if they say no, that the change won't take place.
It does....

Thanks!!!!!
Vmusic
Bob Quintal wrote:

"Vmusic" <ak*****@irisicom.netwrote in
news:11*********************@s13g2000cwa.googlegro ups.com:

No... that's not it.

My question was:
How do you use VBA to programmatically change which row in the
combo box is selected??

I don't want to set the value to some arbitrary string - I
want to use VBA code to select one of the valid rows or items
in a bound combo box

Please.......
Thanks
Vmusic
pi********@hotmail.com wrote:

me.cboPickSomething = "SomeValue From the List"

Yes that is it. it's the only way that works. You as a
programmer simply need to examine the list, extract the string
you want from the list, and then do as you were told

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[pietlinden]

Sorry, first prerequisite to a helpful answer is a proper definition of
the problem.

[David W. Fenton]

"Vmusic" <ak*****@irisicom.netwrote in
news:11*********************@75g2000cwc.googlegrou ps.com:

When the user changes or selects a different value than what is
initially displayed - the BeforeUpdate or AfterUpdate method
fires.

I want to prompt the user to confirm they REALLY want to change
their value - If they don't confirm - or say no, then I want to
change the value back to what it originally was. In order to
change the value back - I have to programattically change the
ListIndex or some stupid property in the combo box.

How do you use VBA to programmatically change which row in the
combo box is selected??

As long as the focus remains on the control, and the combo box is
bound to a field, you have access to the OldValue property.

Otherwise (unbound combo boxes are not uncommon, though there'd be
no reason to confirm a change, seems to me), you could cache the
original value in a variable in the OnEnter event of the combo box,
and use that to change it back.

But to select a row, you have two choices:

1. set the value (the sensible way), OR

2. set it by listindex (this is convenient when you want to
automatically select, say, the first entry in the dropdown).

To do it the second way, you'd have to know which row in the list
had the value you wanted. Obviously, it makes much more sense to
just store the value and restore to that.

Which is basically what you were told, even if you didn't like the
answer.

BTW, I was tempted not to bother to answer because you were so
damned rude in your responses to the help you were given. Had you
simply described your task in full, you could have had a good answer
a long time ago.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Lyle Fairfield]

Well, I suppose I should know how to do this. But I don't. Why not? I
guess because if a user Clicks on A, and doesn't like that, he/she can
then Click on B. So I've never had any cause to want to say, "Did you
really mean A?" and if not, return the box to its pre-A state.

ListIndex can be very gnarly, or at least it has been in my experience.
In a listox, one can set ListIndex if the control has the focus, and if
it hasn't, one can call its SetFocus method and then set the ListIndex
value. But on a sleepy Sunday night, this does not seem to work for a
combo box. Maybe in the bright clear light of morning ....

Well, I suppose we could figure out some way ... but in the meantime
probably someone will contribute something simple that I (and you too,
maybe) have completely overlooked.

As time goes by and I get less and less enthralled by the Access UI, I
generally use little forms which I size and place as required in place
of combo and list boxes. In a few ways they are more responsive than
combo and list boxes, but not in all ... but that's another story.

[Bob Quintal]

"Vmusic" <ak*****@irisicom.netwrote in
news:11*********************@75g2000cwc.googlegrou ps.com:

No, No, No..... what kind of .... is this programming
language

Look I have a combo box. It is populated from data that runs
from a query- and it is bound to a column in a table.

When the user changes or selects a different value than what
is initially displayed - the BeforeUpdate or AfterUpdate
method fires.

I want to prompt the user to confirm they REALLY want to
change their value - If they don't confirm - or say no, then I
want to change the value back to what it originally was. In
order to change the value back - I have to programattically
change the ListIndex or some stupid property in the combo box.

The advice given is still valid.

When the user enters the combobox, the OnEnter event fires. You
store the existing value of the combobox to a variable. When the
user has finished changing the data, the before update event
fires. Then you decide the user is a bigger fool than you, When
he answers in the affirmative, you change it back, using the
statement given in previous messages.

However, because you explained your problem a little better, you
can actually use the cancel feature of the combobox (also of
text and list boxes) before update event.
If the user answers "No I wass being stupid, and I don't want to
chage the data," in your messagebox, you put the statement
cancel = true ' stops the event
me.combobox.undo ' reverse all changes since the box received
focus.

How do you use VBA to programmatically change which row in the
combo box is selected??

I do not want to set it to some arbitrary string value. I want
the actual row selected to be programmatically changed...
because it's bound to a value in a table - which itself is a
lookup.

There HAS TO be a way to programmatically change the selected
row in a combo box?

Sorry - it seems like a simple question/ task.. but I have yet
see a clear answer, maybe even with a VBA code example

PLEASE don't tell me that if I write the code in the
BeforeUpdate method, and Exit Sub if they say no, that the
change won't take place. It does....

Thanks!!!!!
Vmusic

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[David W. Fenton]

Bob Quintal <rq******@sPAmpatico.cawrote in
news:Xn**********************@66.150.105.47:

However, because you explained your problem a little better, you
can actually use the cancel feature of the combobox (also of
text and list boxes) before update event.
If the user answers "No I wass being stupid, and I don't want to
chage the data," in your messagebox, you put the statement
cancel = true ' stops the event
me.combobox.undo ' reverse all changes since the box received
focus.

That's the only real solution. I suggested using the BeforeUpdate
event and OldValue to revert, but you can't change the value in the
BeforeUpdate event, you can only accept it or cancel the edit, as
you describe above.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/