recording selected items from a multiple selection list box in a table (Access 2010)

Question

I'm making a serie of forms in Access 2010 and got stucked in the following problem: in a form, I have a multiple selection list box that I use to get information about assisted government income (Food Stamps, SSI, SSD, etc). I would like to know how can I recover and record the items selected in a table. I populated my list with information from another table (assited_income_table). I do understand that I need to loop through the listbox and get the selected items and after that run an update query, but I don't know how to do that.

Thanks

Answer 1

"Jana_Mcg" wrote in message

news:*** Email address is removed for privacy ***...

> I'm making a serie of forms in Access 2010 and got stucked in the

> following problem: in a form, I have a multiple selection list box that I

> use to get information about assisted government income (Food Stamps, SSI,

> SSD, etc). I would like to know how can I recover and record the items

> selected in a table. I populated my list with information from another

> table (assited_income_table). I do understand that I need to loop through

> the listbox and get the selected items and after that run an update query,

> but I don't know how to do that.

This comes up often enough that I saved my write-up on the subject (but

haven't yet gotten around to posting it on my website's Tips page).  Here it

is:

*** Storing Multiple Selections From A List Box ***

The best way to store multiple items in a single field is not to do it at

all.  Instead, use multiple records in a related table to represent these

items.  It's a principle of relational database design that a single field

(column) holds only one datum.

An Access subform is designed to display and edit multiple records from a

related table (these records being related to the record currently displayed

on the main form), and does it with no code at all.  A list box isn't

designed to do this, but for small "pick-lists" I do like the multiselect

list box.  However, you need to use code to read the related records for

each new main record and select the appropriate items in the list box, and

then whenever the list box is updated you need to use code to update the set

of records in the related table.

Here's code from a sample form that represents "family members", with a list

box named "lstHobbies" that represents, for each family member, that

person's hobbies from the list of all possible hobbies.

'----- start of code -----

Option Compare Database

Option Explicit

Private Sub ClearHobbySelections()

Dim intI As Integer

With Me.lstHobbies

For intI = (.ItemsSelected.Count - 1) To 0 Step -1

.Selected(.ItemsSelected(intI)) = False

Next intI

End With

End Sub

Private Sub Form_Current()

Dim rs As DAO.Recordset

Dim intI As Integer

' Clear all currently selected hobbies.

ClearHobbySelections

If Not Me.NewRecord Then

Set rs = CurrentDb.OpenRecordset( _

"SELECT HobbyID FROM tblFamilyMembersHobbies " & _

"WHERE MemberID=" & Me.MemberID)

' Select the hobbies currently on record for this MemberID.

With Me.lstHobbies

Do Until rs.EOF

For intI = 0 To (.ListCount - 1)

If .ItemData(intI) = CStr(rs!HobbyID) Then

.Selected(intI) = True

Exit For

End If

Next intI

rs.MoveNext

Loop

rs.Close

Set rs = Nothing

End With

End If

End Sub

Private Sub lstHobbies_AfterUpdate()

On Error GoTo Err_lstHobbies_AfterUpdate

Dim db As DAO.Database

Dim ws As DAO.Workspace

Dim strSQL As String

Dim blnInTransaction As Boolean

Dim varItem As Variant

' Make sure the current member record has been saved.

If Me.Dirty Then Me.Dirty = False

Set ws = Workspaces(0)

Set db = ws.Databases(0)

ws.BeginTrans

blnInTransaction = True

' Delete all hobbies now on record.

strSQL = "DELETE FROM tblFamilyMembersHobbies " & _

"WHERE Memberid = " & Me.MemberID

db.Execute strSQL, dbFailOnError

' Add each hobby selected in the list box.

With Me.lstHobbies

For Each varItem In .ItemsSelected

strSQL = _

"INSERT INTO tblFamilyMembersHobbies " & _

"(MemberID, HobbyID) VALUES (" & _

Me.MemberID & ", " & .ItemData(varItem) & ")"

db.Execute strSQL, dbFailOnError

Next varItem

End With

ws.CommitTrans

blnInTransaction = False

Exit_lstHobbies_AfterUpdate:

Set db = Nothing

Set ws = Nothing

Exit Sub

Err_lstHobbies_AfterUpdate:

MsgBox "Error " & Err.Number & ": " & Err.Description, _

vbExclamation, "Unable to Update"

If blnInTransaction Then

ws.Rollback

blnInTransaction = False

End If

Resume Exit_lstHobbies_AfterUpdate

End Sub

Private Sub lstHobbies_BeforeUpdate(Cancel As Integer)

Dim intI As Integer

' Don't allow hobbies to be updated before a MemberID has

' been generated.

If IsNull(Me.MemberID) Then

MsgBox "Please enter other information for this family " & _

"member before choosing hobbies.", , _

"Define Member First"

Cancel = True

Me.lstHobbies.Undo

' Clear the user's selection.

ClearHobbySelections

End If

End Sub

'----- end of code -----

As you see, there's a fair amount of code involved, because we're using the

list box to do something it wasn't built to do, but it works quite nicely.

---

Dirk Goldgar, MS Access MVP

Access tips: www.datagnostics.com/tips.html

I dont mean to piggy back on this thread... but this should be exactly what I need and cant get it to work. I did try to create a testDB for the above code and from what was able to do with it, it still doesn't record the selected items from the listbox in the junction table assigned. What I am trying to do is similar so I'll use the same items as an example, and maybe someone can tell me what I'm missing.

I have a FamilyT, HobbyT and FamilyHobbyT. On the Form I have the record source set to the FamilyHobbyT, a text box with the FamilyMembers name, and a list box showing the items in the HobbyT. I did enter sample data to the tables, and what I would like to happen is when the Form is opened it shows the corresponding FamilyMember's hobbies highlighted in the listbox, with the possibility to add/remove hobbies and have that update the data in the FamilyHobbyT. 

The records are currently showing individually (record 1 of 7; only 2 family members have data and multiple hobbies) and the hobbies are not highlighted in the listbox. Also, if I move to a new record and select a different familymember it will save that familymember in the FamilyHobbyT but the Hobby column is left blank.

Answer 2

ok so I was able to fix part of it(see underlined part for my fix)... here's where I'm still stuck (bolded)...

===code===

Private Sub TitleList_AfterUpdate()

    Dim n As Integer

    Dim strCriteria As String

    Dim strSQL As String

    With Me.TitleList

        For n = .ListCount - 1 To 0 Step -1

            strCriteria = "JobOrderID = " & Nz(Forms("JobOrderF").JobOrderID, 0) & " And TitleID = " & .ItemData(n)

            If .Selected(n) = False Then

                ' if item has been deselected then delete row from table

                If Not IsNull(DLookup("JobOrderID", "JOTitleT", strCriteria)) Then

                   strSQL = "DELETE * FROM JOTitleT WHERE " & strCriteria

                   CurrentDb.Execute strSQL, dbFailOnError

                End If

            Else

                ' if item has been selected then insert row into table

                 If IsNull(DLookup("JobOrderID", "JOTitleT", strCriteria)) Then

                   strSQL = "INSERT INTO JOTitleT (JobOrderID, TitleID) " & "VALUES(" & Forms("JobOrderF").JobOrderID & "," & .ItemData(n))"

                   CurrentDb.Execute strSQL, dbFailOnError

                End If

            End If

        Next n

    End With

End Sub

===end code===

I get a syntax error when trying to select/deselect an item from the list. The only thing I can think of is that the JOTitleT has a autonumber primarykey(JOTitleID) as well as the JobOrderID(number) and TitleID(number)?

Answer 3

Thanks Ken! That's exactly what I was looking for!

I am using the list box on a secondary popup form that the user can edit/select new items and all items need to be shown in list vs having multiple dropdowns. 

I do need help with one more thing that I just cant get right...

In my DB this procedure is going to be a popup form that will only have the List box visible and a hidden box that should have the default value of the main form. (Forms![JOT]![JobID])

I was able to adjust your code but I can only set it to either have the ID as the default value listed above and the list box doesn't pull the right data, or the data is right for the ID but I have to manually enter the ID im looking for. Ideally the ID box will be hidden under the listbox so the user can not change it, only add/remove items from the list box.

===Code===

Private Sub Form_Current()

    Dim varitem As Variant

    Dim strCriteria As String

    Dim n As Integer

    ' synchronize JOform

    ' with current record

Me.JobOrderName = [Forms]![JobOrderF]![JobOrderID]

    With Me.TitleList

        ' deselect all items in list box

        For n = .ListCount - 1 To 0 Step -1

            .Selected(n) = False

        Next n

        .Requery

        ' loop through list box items and select items where JO assigned to Title

        For n = .ListCount - 1 To 0 Step -1

            strCriteria = "JobOrderID = " & Nz(Me.JobOrderID, 0) & " And TitleID = " & .ItemData(n)

            .Selected(n) = Not IsNull(DLookup("JobOrderID", "JOTitleT", strCriteria))

        Next n

    End With

End Sub

===end code===

I know the bolded areas are what is wrong, what I cant figure out is how to have the JobOrderName field default to the JobOrderID and call it correctly in the code. I do have the default value set in the form properties, but if I enter Me.JobOrderID instead of  Me.JobOrderName = [Forms]![JobOrderF]![JobOrderID] it will only show the first ID and has to be changed manually.

...and I assume that the second part of the below code also errors out because of the naming issue...when adding a new item in the list box the code errors out on the underlined section. 

===code===

                ' if item has been selected then insert row into table

                 If IsNull(DLookup("JobOrderID", "JOTitleT", strCriteria)) Then

                   strSQL = "INSERT INTO JOTitleT (JobOrderID, TitleID) " & _

                        "VALUES(" & Me.JobOrderID & "," & .ItemData(n) & ",1)"

                   CurrentDb.Execute strSQL, dbFailOnError

===end code===

Answer 4

For future reference the correct procedure in a situation like this is to post a new thread and include a hyperlink to the earlier thread to which you wish to refer.

You'll find an example of multi-select list box used in this way in StudentCourses.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

However, while it can be done, I would question why the conventional code-free interface of a form/subform is not used in preference to one which requires significant code to read/write the values to/from the list box.  Also the use of a list box does not allow the relationship type to have any non-key attributes; in my demo you'll see that the conventional subform interface allows the inclusion of a Status column in the table which models the relationship type, whereas the use of a list box does not.

Answer 5

"Jana_Mcg" wrote in message

news:*** Email address is removed for privacy ***...

> I'm making a serie of forms in Access 2010 and got stucked in the

> following problem: in a form, I have a multiple selection list box that I

> use to get information about assisted government income (Food Stamps, SSI,

> SSD, etc). I would like to know how can I recover and record the items

> selected in a table. I populated my list with information from another

> table (assited_income_table). I do understand that I need to loop through

> the listbox and get the selected items and after that run an update query,

> but I don't know how to do that.

This comes up often enough that I saved my write-up on the subject (but

haven't yet gotten around to posting it on my website's Tips page).  Here it

is:

*** Storing Multiple Selections From A List Box ***

The best way to store multiple items in a single field is not to do it at

all.  Instead, use multiple records in a related table to represent these

items.  It's a principle of relational database design that a single field

(column) holds only one datum.

An Access subform is designed to display and edit multiple records from a

related table (these records being related to the record currently displayed

on the main form), and does it with no code at all.  A list box isn't

designed to do this, but for small "pick-lists" I do like the multiselect

list box.  However, you need to use code to read the related records for

each new main record and select the appropriate items in the list box, and

then whenever the list box is updated you need to use code to update the set

of records in the related table.

Here's code from a sample form that represents "family members", with a list

box named "lstHobbies" that represents, for each family member, that

person's hobbies from the list of all possible hobbies.

'----- start of code -----

Option Compare Database

Option Explicit

Private Sub ClearHobbySelections()

Dim intI As Integer

With Me.lstHobbies

For intI = (.ItemsSelected.Count - 1) To 0 Step -1

.Selected(.ItemsSelected(intI)) = False

Next intI

End With

End Sub

Private Sub Form_Current()

Dim rs As DAO.Recordset

Dim intI As Integer

' Clear all currently selected hobbies.

ClearHobbySelections

If Not Me.NewRecord Then

Set rs = CurrentDb.OpenRecordset( _

"SELECT HobbyID FROM tblFamilyMembersHobbies " & _

"WHERE MemberID=" & Me.MemberID)

' Select the hobbies currently on record for this MemberID.

With Me.lstHobbies

Do Until rs.EOF

For intI = 0 To (.ListCount - 1)

If .ItemData(intI) = CStr(rs!HobbyID) Then

.Selected(intI) = True

Exit For

End If

Next intI

rs.MoveNext

Loop

rs.Close

Set rs = Nothing

End With

End If

End Sub

Private Sub lstHobbies_AfterUpdate()

On Error GoTo Err_lstHobbies_AfterUpdate

Dim db As DAO.Database

Dim ws As DAO.Workspace

Dim strSQL As String

Dim blnInTransaction As Boolean

Dim varItem As Variant

' Make sure the current member record has been saved.

If Me.Dirty Then Me.Dirty = False

Set ws = Workspaces(0)

Set db = ws.Databases(0)

ws.BeginTrans

blnInTransaction = True

' Delete all hobbies now on record.

strSQL = "DELETE FROM tblFamilyMembersHobbies " & _

"WHERE Memberid = " & Me.MemberID

db.Execute strSQL, dbFailOnError

' Add each hobby selected in the list box.

With Me.lstHobbies

For Each varItem In .ItemsSelected

strSQL = _

"INSERT INTO tblFamilyMembersHobbies " & _

"(MemberID, HobbyID) VALUES (" & _

Me.MemberID & ", " & .ItemData(varItem) & ")"

db.Execute strSQL, dbFailOnError

Next varItem

End With

ws.CommitTrans

blnInTransaction = False

Exit_lstHobbies_AfterUpdate:

Set db = Nothing

Set ws = Nothing

Exit Sub

Err_lstHobbies_AfterUpdate:

MsgBox "Error " & Err.Number & ": " & Err.Description, _

vbExclamation, "Unable to Update"

If blnInTransaction Then

ws.Rollback

blnInTransaction = False

End If

Resume Exit_lstHobbies_AfterUpdate

End Sub

Private Sub lstHobbies_BeforeUpdate(Cancel As Integer)

Dim intI As Integer

' Don't allow hobbies to be updated before a MemberID has

' been generated.

If IsNull(Me.MemberID) Then

MsgBox "Please enter other information for this family " & _

"member before choosing hobbies.", , _

"Define Member First"

Cancel = True

Me.lstHobbies.Undo

' Clear the user's selection.

ClearHobbySelections

End If

End Sub

'----- end of code -----

As you see, there's a fair amount of code involved, because we're using the

list box to do something it wasn't built to do, but it works quite nicely.

---

Dirk Goldgar, MS Access MVP

Access tips: www.datagnostics.com/tips.html