How to save a value selected in a list box? 1

[JimRich]

I have a list box that works fine. My problem is that I need the selected value in the box stored in a varaible. I have tried the following in an on click procedure:
dim varC as string
varC = Forms![Find Skills}!listFindSkills.column(2) and
varC = me.listFindSkills

I get varC = Null

Any help will be appreciated.


JimRich

 

[scriverb]

If it is a listbox which has its Multi Select property set to None the the following VBA code will give you the value selected in the listbox:

dim varC as string
varC = Forms![Find Skills}!listFindSkills.value

If the Multi Select property is set to Simple or Extended then more than one item in the listbox could be selected and you would have to loop through the ItemsSelected property to retrieve the data:

Dim frm As Form, ctl As Control
Dim itemV As Variant
Dim varC As String
Set frm = Forms![Find Skills]
Set ctl = frm!listFindSkills
For Each itemV In ctl.ItemsSelected
varC = ctl.ItemData(itemV)
'Do something with value here
Next itemV

Post back with questions if you have some.




Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jdegeorge]

Bob

I have a similar question, I think. I don't want to store the values in a variable. Rather, I want to store them in a table.

I've got a form with the field MyField from MyTable. This is the control source for a list box, and the row source is a field from a 2nd table (My2ndField in My2ndTable).

I want to be able to save the multi-selected values from My2ndField in MyField. I'm thinking that I should probably replace your "Do something with value here" but I can't figure out how to do it. Can you help?

Jim DeGeorge

 

[scriverb]

jdegeorge: I have never used a Multi-select listbox to store data through its Control Source. I have tested this a little and it seems that when selecting multiple items from the listbox there is no data stored in the Control Source's underlying tables field. Only when you have select None for the MultiSelect property is that data saved.

Now just what do you want to save and where do you want to save it from the multi-select listbox? I don't think you can store multiple selections in any fashion for use at a later time. You can access all of the items selected by looping through the ItemsSelection collection for the ListBox and performing some VBA code to store the selections in other fields of a recordsource. Is this what you are looking for?

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jdegeorge]

Bob

I have a table of tasks and I'm trying to associate dependencies. For example, if I'm entering a task into the database, I want to let the user select the other dependent tasks and store those in a list. Sort of mimicking the MS Project dependency field.

Is this possible?

Jim DeGeorge

 

[scriverb]

This sounds like a one-to-many table relationship. On the One side is the table Tasks and on the many is a table Dependencies. Yes, then after all of the selections are made from the list box we can execute VBA code that will create X number of Dependencies records that will like back to the Tasks record by a task number. You can then query these tables for reports and forms as you like.

Does this sound like what you want to do?

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jdegeorge]

Bob

You're a genius, and it's so simple! NORMALIZATION!!!

What I'll wind up with is tblDependencies with 2 fields

fldTaskMain
fldTaskDependencies

The data would look like this

fldTaskMain fldTaskDependencies
Task001 Task002
Task002 Task004
Task003 Task001
Task003 Task002
Task004 Task005
Task005 Task001

etc., etc.

That'll do it. I'll just have to create a sub-form for this and I'll be in good shape. Thanks, and enjoy the star!

Jim DeGeorge

 

[scriverb]

Great!!! I am glad to be of help to you.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???