Multiple Selections In List Boxes

[SERT1]

How do I get a list box in a form to create records in a table based on multiple selections in a list box? i.e. -

Field: list box: area
Field: text box: date

Three areas selected plus one date to create 3 records in a table:

North 03/22/2002
South 03/22/2002
East 03/22/2002

I'm having a problem because when you select "Multi Select" to "1" in the properties of the list box, it automatically sets the value to "Null" and then for some reason it won't accept it in my table. Hopefully I've explained it well enough.

Thanks.

 

[WildHare]

You will need to scan through the ItemsSelected collection of the listbox and grab each value in turn. There are numerous examples of this floating around, but I rarely use LBs myself to I don't have one specific for you.

However, and this is just a thought, it appears from a cursory glance that your list box might have just four possible values. In this case, might it be easier to handle this with four checkboxes and four textboxes??

[x] North
[ ] South
[x] East
[x] West


Remember, you're unique - - just like everyone else
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[SERT1]

Actually, I was just trying to simplify, there's actually about 30 choices to choose from, with data from 8 other fields that will be identical. So in the interest of saving time for the user having to enter the same thing 10 or 15 times, I figured a multiple selection list box would be good to update the table.

 

[WildHare]

Ah, ok. Yeah, I'd put 30 in a LB, I guess. One never knows with a post here whether the sample is complete, an illustration using simplified data, or completely wrong in the first place..

Have we ans. yr question, or do you need more info? Remember, you're unique - - just like everyone else
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[SERT1]

I will need more information. How do I scan the "items selected"? ...and then what?

 

[jebry]

Hi!

Here's how to look through the ItemsSelected and add a record using the information to a table:

Dim varItem As Variant
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("YourTable", dbOpenDynaset)

For Each varItem In Me!YourListBox.ItemsSelected
rst.AddNew
rst!ListBoxField = Me!YourListBox.Column(0, varItem)
rst!AnotherField = Me!SomeTextBox
etc.
rst.Update
Next varItem

Set rst = Nothing

The code above assumes that the information you want to store is in the first column of the list box. If not, you will need to change the 0 to a 1 for the second column, etc.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[SERT1]

Thanks Jeff, I tried your solution, and it creates one row in the table with all the data except the data in the list box (area). That field remains empty. Also, if I open the form again and make other selections, it just overwrites that one row of data in the table with the "area" field still blank. Any more suggestions?

 

[jebry]

Hi!

If you could, would you post the actual code you used. This shouldn't be overwriting an existing record because of the .AddNew. If I could see what you have used, I may get some ideas about where to go from there.

Jeff Bridgham
bridgham@purdue.edu

 

[SERT1]

Okay Jeff, here it is:

Private Sub Command10_Click()

Dim Area As Variant
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Data", dbOpenDynaset)
For Each Area In Me!Area.ItemsSelected
rst.AddNew
rst!Area = Me!Area.Column(0, Area)
rst!Date_Noted = Me!Date_Noted
rst!Date_Comp = Me!Date_Comp
rst!Hazard = Me!Hazard
rst!Manager = Me!Manager
Next Area
Set rst = Nothing

End Sub



This is the data that came back in the table:

Area Date_Noted Date_Comp Hazard Manager
3/21/2002 3/21/2002 B Ed

I had actually selected 3 items, and the item that had been there previously got overridden by this row.

 

[jebry]

Hi again!

Two problems, see changes below:

Private Sub Command10_Click()

Dim varArea As Variant
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Data", dbOpenDynaset)
For Each varArea In Me!lstArea.ItemsSelected
rst.AddNew
rst!Area = Me!lstArea.Column(0, varArea)
rst!Date_Noted = Me!Date_Noted
rst!Date_Comp = Me!Date_Comp
rst!Hazard = Me!Hazard
rst!Manager = Me!Manager
rst.Update
Next varArea
Set rst = Nothing

End Sub

You had too many things called Area and Access was confusing them. I changed the variant to varArea and the list box to lstArea to distinguish them from the field. And you forgot the rst.Update at the end of the fields you were adding.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[SERT1]

Jeff, you're a god!! Thanks so much, it works now. This is a great forum.

Thanks again,
Maureen

 

[jebry]

Hi Maureen!

Glad its working! Your Welcome!

Jeff Bridgham
bridgham@purdue.edu

 

[SERT1]

OOps Jeff, one thing I just noticed. It's putting in multiple records, but only putting in the name of the last item selected.

Area Date_Noted Date_Comp Hazard Manager
NDT 3/20/2002 3/20/2002 C Fred
NDT 3/20/2002 3/20/2002 C Fred
DQC 4/1/2002 4/2/2002 D Ed
DQC 4/1/2002 4/2/2002 D Ed

I had selected 2 different areas for each of those entries.

 

[jebry]

Hi again!

Well, I can't think of anything off the top of my head. If you want, you can email your database to me to look at(zipped of course). I'll try it out and see what I can find. If the information is sensitive, then make a copy and delete the info before sending it.

hth
Jeff Bridgham
bridgham@purdue.edu