List box change values

  • Thread starter Thread starter kena
  • Start date Start date
K

kena

I have a table which has two fields, "Strategy", text 10
and "YesNo", text 3.
I have set up a list box with multiselect set to simple
and want to change the value of "YesNo" to "yes' if the
strategy is selected and "no" if it is not.
I need help in writing a function (Or sub)to be used
after each item selected has been selected (or after I
exit the list box), which will change the value
of "YesNo" depending on whether it has been selected or
not.
Thanks for any help or directions

KenA
 
Kena,
I know it's difficult with written questions to always understand what a
poster is trying to do but before I can respond meaningfully... I'd need to
understand your setup better.
(example strategies... "Attack", "Retreat")
I'm wondering why you are bothering to textually indicate Yes or No when
a Strategy is selected from a list box." If that selection updates the
field value in a form table, then just the presence of that value in the
field means it was selected. Why do you need to go back to the Strategies
table and indicate that a particular strategy has been selected... somewhere
in your records?
And how do you maintain that Yes/No field correctly?
This setup doesn't seem to make sense... could you explain a bit more
about why your doing this Yes/No process?

hth
Al Camp
 
Thanks for the reply.
I need to store the value "yes/No" because the access
database is used as the data source for a mail merge from
which the user selects a specific record based upon a
unique identifer.
The selections in the list box are the descriptions and
the yes/no is the flag as to whether to include text
relating to that strategy or not.
If you can suggest another method, I would be very
interested.
 
Kena,
OK... well, I guess you know best... it just seemed a bit odd to me.

But anyhow, try this...
On the AfterUpdate event of the list box, do an Update Query against the
table that feeds the listbox, and update the appropriate Strategy Yes/No to
"YES".

Then Requery the listbox, and it should update with the just changed
value.

Is that what you wanted to do?
hth
Al Camp
 
Thanks Al,
I was going to use the code below, but am not sure what
to use to update the table with the value depending on
whether it was selected or not.
The code is as follows:

Sub Strategy_Option()
Dim frm As Form, ctl As Control
Dim varitm As Variant

Set frm = Forms![options] ' Form name
Set ctl = frm!Strategy ' Name of list box

For Each varitm In ctl.ItemsSelected

' This next line is the code Im having trouble with
YesNo = "Yes"

Next varitm
End Sub

Appreciate you help.
KenA
 
Thanks Al,
I was going to use the code below, but am not sure what
to use to update the table with the value depending on
whether it was selected or not.
The code is as follows:

Sub Strategy_Option()
Dim frm As Form, ctl As Control
Dim varitm As Variant

Set frm = Forms![options] ' Form name
Set ctl = frm!Strategy ' Name of list box

For Each varitm In ctl.ItemsSelected

' This next line is the code Im having trouble with
YesNo = "Yes"

Next varitm
End Sub

Appreciate you help.
KenA
-----Original Message-----
Kena,
OK... well, I guess you know best... it just seemed a bit odd to me.

But anyhow, try this...
On the AfterUpdate event of the list box, do an Update Query against the
table that feeds the listbox, and update the appropriate Strategy Yes/No to
"YES".

Then Requery the listbox, and it should update with the just changed
value.

Is that what you wanted to do?
hth
Al Camp




.
Click to expand...
Click to expand...
 
You can't dynamically change the items in a list box. Those items are
the result of a query against a table of values... in this case
tblStrategies with fields Strategy and YesNo. You must make the YesNo field
changes to the table that feeds the listbox.
I can only repeat my previous solution...

On the AfterUpdate event of the list box, do an "Update Query" against
the
table that feeds the listbox, and using the Strategy value you just entered
on the form as a criteria as to which Strategy record to update the
appropriate Strategy YesNo field to "Yes".
After the Update to the underlying table for your listbox items, Requery
the listbox to show the latest changes.

Read up on Update queries and how to use them in Help.
hth
Al Camp

Thanks Al,
I was going to use the code below, but am not sure what
to use to update the table with the value depending on
whether it was selected or not.
The code is as follows:

Sub Strategy_Option()
Dim frm As Form, ctl As Control
Dim varitm As Variant

Set frm = Forms![options] ' Form name
Set ctl = frm!Strategy ' Name of list box

For Each varitm In ctl.ItemsSelected

' This next line is the code Im having trouble with
YesNo = "Yes"

Next varitm
End Sub

Appreciate you help.
KenA
-----Original Message-----
Kena,
OK... well, I guess you know best... it just seemed a bit odd to me.

But anyhow, try this...
On the AfterUpdate event of the list box, do an Update Query against the
table that feeds the listbox, and update the appropriate Strategy Yes/No to
"YES".

Then Requery the listbox, and it should update with the just changed
value.

Is that what you wanted to do?
hth
Al Camp




.
Click to expand...
Click to expand...
 
Back
Top