Creating separate tables for multi-select list box selections

[Jacquiemal]

I have read from some of the other posts that it is necessary to create a
separate table for each of the selections made in a multi-select list box
from a form. I don't understand this concept! I have a form with 4
multi-select list boxes and many other fields that populate a table with a
simple query attached to it. How do I create this separate table for the
selections from the list boxes and link it to the main table and query? I am
not an Access programmer and am learning as I go along. Please explain step
by step and as simply as possible. Thanks for any and all help in advance!

 

[John W. Vinson]

I have read from some of the other posts that it is necessary to create a
separate table for each of the selections made in a multi-select list box
from a form. I don't understand this concept!

Me neither, because it is absolutely NOT the case. I can't imagine what the
person posting was thinking. Do you have a link to the post? Perhaps you
misinterpreted it...

I have a form with 4
multi-select list boxes and many other fields that populate a table with a
simple query attached to it. How do I create this separate table for the
selections from the list boxes and link it to the main table and query?

Again... you don't. It's neither necessary nor something you would want to
try.

I am
not an Access programmer and am learning as I go along. Please explain step
by step and as simply as possible. Thanks for any and all help in advance!

What is the structure of your tables? What do you mean by a query "attached"
to it - you can base a query on a table but you can't "attach" a query to a
table? What's the recordsource for your form? Where do you want the values
selected in the multiselect listboxes to end up? Whjy are you using
multiselect listboxes (which take a fair bit of VBA code to use) rather than
subforms, which require none at all?

John W. Vinson [MVP]

 

[Jacquiemal]

Me neither, because it is absolutely NOT the case. I can't imagine what the
person posting was thinking. Do you have a link to the post? Perhaps you
misinterpreted it...


Again... you don't. It's neither necessary nor something you would want to
try.


What is the structure of your tables? What do you mean by a query "attached"
to it - you can base a query on a table but you can't "attach" a query to a
table? What's the recordsource for your form? Where do you want the values
selected in the multiselect listboxes to end up? Whjy are you using
multiselect listboxes (which take a fair bit of VBA code to use) rather than
subforms, which require none at all?

John W. Vinson [MVP]

I have two tables; one that stores the data entered into the form (tblPSB
Request Database) and one that contains the values the populate the list
boxes (tblListBoxes). I want the multiple values selected from the list
boxes to end up in the corresponding fields of the main table (tblPSB Request
Database).

I use list boxes because, as I said, I am a novice user and I don't
understand subforms. Further, the director of the agency wants users of the
form to be able to select multiple items from a list.

I'm sure I'm using incorrec terminology when I say a query is "attached", so
please humor me. I just mean a query is based on that table.

I just would like to know how to store the multiple items selected from the
list boxes in the form in the main table. Any help would be very greatly
appreciated.

 

[Jacquiemal]

I have two tables: tblPSB Request Database that stores the data that is
entered into the form; and tblListBoxes that is the rowsource for the list
boxes. I would like the values selected from the multi-select list boxes to
be displayed in the main table (tblPSB Request Database).

I use list boxes becasue they were requested by the director I work for, and
I am unfamiliar with subforms.

I'm sure I'm using incorrect terminology when I refer to a query being
"attached". Please pardon me, I only mean a query is based on that table.

I just want to know how to get the values selected from the multi-select
list boxes to appear in the main table so I can be done with this form. I
was successful at building every other part of it and making it functional
until this very last part. Any help would be very much apperciated.

 

[John W. Vinson]

I have two tables; one that stores the data entered into the form (tblPSB
Request Database) and one that contains the values the populate the list
boxes (tblListBoxes). I want the multiple values selected from the list
boxes to end up in the corresponding fields of the main table (tblPSB Request
Database).

What are the relevant fields in these tables? How are they related, if at all?

I use list boxes because, as I said, I am a novice user and I don't
understand subforms. Further, the director of the agency wants users of the
form to be able to select multiple items from a list.

Take a look at the Orders form in the Northwind sample database which comes
with Access. It uses a subform; it lets users select multiple items from a
list; *it does what you want to do*. Subforms don't bite, the actually make
life easier for you as the developer and for the user. Learn to use them!

I'm sure I'm using incorrec terminology when I say a query is "attached", so
please humor me. I just mean a query is based on that table.

I just would like to know how to store the multiple items selected from the
list boxes in the form in the main table. Any help would be very greatly
appreciated.

If you really want to do it the hard way, here's some VBA code which will take
multiple selections from a listbox and store them in multiple rows of a child
table. Since I don't know the structure of your table I don't know if this is
relevant, but it's an example of what you will need to do to use multiselect
listboxes. Trust me... subforms are MUCH easier!

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, newly
cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
currently
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub



John W. Vinson [MVP]

 

[Jacquiemal]

If I can figure out subforms and rebuild my database in two days, then I'll
glady do that. Otherwise, I'll try your code and see what happens. Thanks
for your help

I have two tables; one that stores the data entered into the form (tblPSB
Request Database) and one that contains the values the populate the list
boxes (tblListBoxes). I want the multiple values selected from the list
boxes to end up in the corresponding fields of the main table (tblPSB Request
Database).

What are the relevant fields in these tables? How are they related, if at all?

I use list boxes because, as I said, I am a novice user and I don't
understand subforms. Further, the director of the agency wants users of the
form to be able to select multiple items from a list.

Take a look at the Orders form in the Northwind sample database which comes
with Access. It uses a subform; it lets users select multiple items from a
list; *it does what you want to do*. Subforms don't bite, the actually make
life easier for you as the developer and for the user. Learn to use them!

I'm sure I'm using incorrec terminology when I say a query is "attached", so
please humor me. I just mean a query is based on that table.

I just would like to know how to store the multiple items selected from the
list boxes in the form in the main table. Any help would be very greatly
appreciated.

If you really want to do it the hard way, here's some VBA code which will take
multiple selections from a listbox and store them in multiple rows of a child
table. Since I don't know the structure of your table I don't know if this is
relevant, but it's an example of what you will need to do to use multiselect
listboxes. Trust me... subforms are MUCH easier!

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, newly
cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
currently
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub



John W. Vinson [MVP]

 

[Jacquiemal]

I don't necessarily "want" to do it the "hard way", I want what works on a
database that's complete except for these last items. Thanks for your help.
I'll let you know how it turns out.

I have two tables; one that stores the data entered into the form (tblPSB
Request Database) and one that contains the values the populate the list
boxes (tblListBoxes). I want the multiple values selected from the list
boxes to end up in the corresponding fields of the main table (tblPSB Request
Database).

What are the relevant fields in these tables? How are they related, if at all?

I use list boxes because, as I said, I am a novice user and I don't
understand subforms. Further, the director of the agency wants users of the
form to be able to select multiple items from a list.

Take a look at the Orders form in the Northwind sample database which comes
with Access. It uses a subform; it lets users select multiple items from a
list; *it does what you want to do*. Subforms don't bite, the actually make
life easier for you as the developer and for the user. Learn to use them!

I'm sure I'm using incorrec terminology when I say a query is "attached", so
please humor me. I just mean a query is based on that table.

I just would like to know how to store the multiple items selected from the
list boxes in the form in the main table. Any help would be very greatly
appreciated.

If you really want to do it the hard way, here's some VBA code which will take
multiple selections from a listbox and store them in multiple rows of a child
table. Since I don't know the structure of your table I don't know if this is
relevant, but it's an example of what you will need to do to use multiselect
listboxes. Trust me... subforms are MUCH easier!

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, newly
cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
currently
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub



John W. Vinson [MVP]