Paired Multi-Select Listboxes Question BY Helen Feddema

G

Guest

Below I took the example from Helen Feddema. What I accomplished was a
playlist to play MP3's and the below code works.

However, I wanted to store the records under someones name. So what I did
was added a field to the TPlaylistSelect table called personsname. I also
made another table called TNAMES to store the persons name who wants to make
the playlist. So on my form I created a List box (list42) based on the
TNAMES table. Now when I open the form I select the name from the list box
then I can select records and add them to the play list.

Here is my question: When the record is written to the table TPlaylistSect
how can I also grab the valuse from List42 that is present on the form and
write it also to personsname field. I hope someone may have an answer. Keep
in mind it doesnt have to be a listbox I could always create a combo box to
do the same thing.

Thanks.




Option Compare Database
Option Explicit

Dim xlstAvailable As Access.ListBox
Dim xlstSelected As Access.ListBox
Dim strItem As String
Dim strSQL As String
Dim dbs As DAO.Database
Dim rstAvailable As DAO.Recordset
Dim rstSelected As DAO.Recordset
Dim rstPersonAvailable As DAO.Recordset
Dim strSearch As String
Dim varItem As Variant
Private Sub cmdAdd_Click()
'Written by Helen Feddema 11-2-2000
'Last modified 11-14-2000

On Error GoTo ErrorHandler

Set xlstSelected = Me![ResultMP3]
Set xlstAvailable = Me![List50]

'Check that at least one item has been selected
If xlstAvailable.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one item"
xlstAvailable.SetFocus
Exit Sub
End If

Set dbs = CurrentDb
Set rstSelected = dbs.OpenRecordset("TPlaylistSelect", dbOpenDynaset)
Set rstAvailable = dbs.OpenRecordset("TMP3", dbOpenDynaset)

For Each varItem In xlstAvailable.ItemsSelected
strItem = Nz(xlstAvailable.Column(0, varItem))

Debug.Print "Selected item: " & strItem

'Append selected item to Selected Items list
With rstSelected
strSearch = "[MP3] = " & Chr$(39) & strItem & Chr$(39)
.FindFirst strSearch
If .NoMatch = True Then
.AddNew
![MP3] = strItem
.Update

End If
End With

'Delete selected item from Available Items list
'With rstAvailable
'strSearch = "[MP3] = " & Chr$(39) & strItem & Chr$(39)
'.FindFirst strSearch
'If .NoMatch = False Then
' .Delete
'End If
'End With

Next varItem
rstSelected.Close
rstAvailable.Close
xlstAvailable.Requery
xlstSelected.Requery
 
D

Douglas J. Steele

Depending on which field of the listbox is the bound field, and assuming
that it's not a multi-select listbox, you'd simply refer to Me!list42 (or
Me.list42) to determine what value is currently selected in the listbox.

Note that if no name has been selected, Me!list42 will return Null.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BrianPaul said:
Below I took the example from Helen Feddema. What I accomplished was a
playlist to play MP3's and the below code works.

However, I wanted to store the records under someones name. So what I did
was added a field to the TPlaylistSelect table called personsname. I also
made another table called TNAMES to store the persons name who wants to
make
the playlist. So on my form I created a List box (list42) based on the
TNAMES table. Now when I open the form I select the name from the list
box
then I can select records and add them to the play list.

Here is my question: When the record is written to the table
TPlaylistSect
how can I also grab the valuse from List42 that is present on the form and
write it also to personsname field. I hope someone may have an answer.
Keep
in mind it doesnt have to be a listbox I could always create a combo box
to
do the same thing.

Thanks.




Option Compare Database
Option Explicit

Dim xlstAvailable As Access.ListBox
Dim xlstSelected As Access.ListBox
Dim strItem As String
Dim strSQL As String
Dim dbs As DAO.Database
Dim rstAvailable As DAO.Recordset
Dim rstSelected As DAO.Recordset
Dim rstPersonAvailable As DAO.Recordset
Dim strSearch As String
Dim varItem As Variant
Private Sub cmdAdd_Click()
'Written by Helen Feddema 11-2-2000
'Last modified 11-14-2000

On Error GoTo ErrorHandler

Set xlstSelected = Me![ResultMP3]
Set xlstAvailable = Me![List50]

'Check that at least one item has been selected
If xlstAvailable.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one item"
xlstAvailable.SetFocus
Exit Sub
End If

Set dbs = CurrentDb
Set rstSelected = dbs.OpenRecordset("TPlaylistSelect", dbOpenDynaset)
Set rstAvailable = dbs.OpenRecordset("TMP3", dbOpenDynaset)

For Each varItem In xlstAvailable.ItemsSelected
strItem = Nz(xlstAvailable.Column(0, varItem))

Debug.Print "Selected item: " & strItem

'Append selected item to Selected Items list
With rstSelected
strSearch = "[MP3] = " & Chr$(39) & strItem & Chr$(39)
.FindFirst strSearch
If .NoMatch = True Then
.AddNew
![MP3] = strItem
.Update

End If
End With

'Delete selected item from Available Items list
'With rstAvailable
'strSearch = "[MP3] = " & Chr$(39) & strItem & Chr$(39)
'.FindFirst strSearch
'If .NoMatch = False Then
' .Delete
'End If
'End With

Next varItem
rstSelected.Close
rstAvailable.Close
xlstAvailable.Requery
xlstSelected.Requery
Click to expand...
 
G

Guest

I wish it was that easy but its not. The form is not bound to any table or
querry. The listbox has only 1 field. However how is it written to the table.

Douglas J. Steele said:
Depending on which field of the listbox is the bound field, and assuming
that it's not a multi-select listbox, you'd simply refer to Me!list42 (or
Me.list42) to determine what value is currently selected in the listbox.

Note that if no name has been selected, Me!list42 will return Null.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BrianPaul said:
Below I took the example from Helen Feddema. What I accomplished was a
playlist to play MP3's and the below code works.

However, I wanted to store the records under someones name. So what I did
was added a field to the TPlaylistSelect table called personsname. I also
made another table called TNAMES to store the persons name who wants to
make
the playlist. So on my form I created a List box (list42) based on the
TNAMES table. Now when I open the form I select the name from the list
box
then I can select records and add them to the play list.

Here is my question: When the record is written to the table
TPlaylistSect
how can I also grab the valuse from List42 that is present on the form and
write it also to personsname field. I hope someone may have an answer.
Keep
in mind it doesnt have to be a listbox I could always create a combo box
to
do the same thing.

Thanks.




Option Compare Database
Option Explicit

Dim xlstAvailable As Access.ListBox
Dim xlstSelected As Access.ListBox
Dim strItem As String
Dim strSQL As String
Dim dbs As DAO.Database
Dim rstAvailable As DAO.Recordset
Dim rstSelected As DAO.Recordset
Dim rstPersonAvailable As DAO.Recordset
Dim strSearch As String
Dim varItem As Variant
Private Sub cmdAdd_Click()
'Written by Helen Feddema 11-2-2000
'Last modified 11-14-2000

On Error GoTo ErrorHandler

Set xlstSelected = Me![ResultMP3]
Set xlstAvailable = Me![List50]

'Check that at least one item has been selected
If xlstAvailable.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one item"
xlstAvailable.SetFocus
Exit Sub
End If

Set dbs = CurrentDb
Set rstSelected = dbs.OpenRecordset("TPlaylistSelect", dbOpenDynaset)
Set rstAvailable = dbs.OpenRecordset("TMP3", dbOpenDynaset)

For Each varItem In xlstAvailable.ItemsSelected
strItem = Nz(xlstAvailable.Column(0, varItem))

Debug.Print "Selected item: " & strItem

'Append selected item to Selected Items list
With rstSelected
strSearch = "[MP3] = " & Chr$(39) & strItem & Chr$(39)
.FindFirst strSearch
If .NoMatch = True Then
.AddNew
![MP3] = strItem
.Update

End If
End With

'Delete selected item from Available Items list
'With rstAvailable
'strSearch = "[MP3] = " & Chr$(39) & strItem & Chr$(39)
'.FindFirst strSearch
'If .NoMatch = False Then
' .Delete
'End If
'End With

Next varItem
rstSelected.Close
rstAvailable.Close
xlstAvailable.Requery
xlstSelected.Requery
Click to expand...
Click to expand...
 
D

Douglas J. Steele

I wasn't talking about whether the form was bound to a table or query. List
boxes (and combo boxes) have a "Bound Column" property which tells Microsoft
Access which column's values to use as the value of the control when you
make a selection from the list box or combo box. (If you want the list box
or combo box bound to a field in the form's underlying recordset, you'd
specify a field in the ControlSource property.)

To add that field to your TPlaylistSelect table, you'd need to change this
code:

With rstSelected
strSearch = "[MP3] = " & Chr$(39) & strItem & Chr$(39)
.FindFirst strSearch
If .NoMatch = True Then
.AddNew
![MP3] = strItem
.Update
End If
End With

to


With rstSelected
strSearch = "[MP3] = " & Chr$(39) & strItem & Chr$(39) & _
" And [personsname] = " & Chr$(34) & Me!List42 & Chr$(34)
.FindFirst strSearch
If .NoMatch = True Then
.AddNew
![MP3] = strItem
![personsname] = Me!list42
.Update
End If
End With



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BrianPaul said:
I wish it was that easy but its not. The form is not bound to any table or
querry. The listbox has only 1 field. However how is it written to the
table.

Douglas J. Steele said:
Depending on which field of the listbox is the bound field, and assuming
that it's not a multi-select listbox, you'd simply refer to Me!list42 (or
Me.list42) to determine what value is currently selected in the listbox.

Note that if no name has been selected, Me!list42 will return Null.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BrianPaul said:
Below I took the example from Helen Feddema. What I accomplished was a
playlist to play MP3's and the below code works.

However, I wanted to store the records under someones name. So what I
did
was added a field to the TPlaylistSelect table called personsname. I
also
made another table called TNAMES to store the persons name who wants to
make
the playlist. So on my form I created a List box (list42) based on the
TNAMES table. Now when I open the form I select the name from the list
box
then I can select records and add them to the play list.

Here is my question: When the record is written to the table
TPlaylistSect
how can I also grab the valuse from List42 that is present on the form
and
write it also to personsname field. I hope someone may have an answer.
Keep
in mind it doesnt have to be a listbox I could always create a combo
box
to
do the same thing.

Thanks.




Option Compare Database
Option Explicit

Dim xlstAvailable As Access.ListBox
Dim xlstSelected As Access.ListBox
Dim strItem As String
Dim strSQL As String
Dim dbs As DAO.Database
Dim rstAvailable As DAO.Recordset
Dim rstSelected As DAO.Recordset
Dim rstPersonAvailable As DAO.Recordset
Dim strSearch As String
Dim varItem As Variant
Private Sub cmdAdd_Click()
'Written by Helen Feddema 11-2-2000
'Last modified 11-14-2000

On Error GoTo ErrorHandler

Set xlstSelected = Me![ResultMP3]
Set xlstAvailable = Me![List50]

'Check that at least one item has been selected
If xlstAvailable.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one item"
xlstAvailable.SetFocus
Exit Sub
End If

Set dbs = CurrentDb
Set rstSelected = dbs.OpenRecordset("TPlaylistSelect", dbOpenDynaset)
Set rstAvailable = dbs.OpenRecordset("TMP3", dbOpenDynaset)

For Each varItem In xlstAvailable.ItemsSelected
strItem = Nz(xlstAvailable.Column(0, varItem))

Debug.Print "Selected item: " & strItem

'Append selected item to Selected Items list
With rstSelected
strSearch = "[MP3] = " & Chr$(39) & strItem & Chr$(39)
.FindFirst strSearch
If .NoMatch = True Then
.AddNew
![MP3] = strItem
.Update

End If
End With

'Delete selected item from Available Items list
'With rstAvailable
'strSearch = "[MP3] = " & Chr$(39) & strItem & Chr$(39)
'.FindFirst strSearch
'If .NoMatch = False Then
' .Delete
'End If
'End With

Next varItem
rstSelected.Close
rstAvailable.Close
xlstAvailable.Requery
xlstSelected.Requery
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Doug...worked perfectly.

Douglas J. Steele said:
I wasn't talking about whether the form was bound to a table or query. List
boxes (and combo boxes) have a "Bound Column" property which tells Microsoft
Access which column's values to use as the value of the control when you
make a selection from the list box or combo box. (If you want the list box
or combo box bound to a field in the form's underlying recordset, you'd
specify a field in the ControlSource property.)

To add that field to your TPlaylistSelect table, you'd need to change this
code:

With rstSelected
strSearch = "[MP3] = " & Chr$(39) & strItem & Chr$(39)
.FindFirst strSearch
If .NoMatch = True Then
.AddNew
![MP3] = strItem
.Update
End If
End With

to


With rstSelected
strSearch = "[MP3] = " & Chr$(39) & strItem & Chr$(39) & _
" And [personsname] = " & Chr$(34) & Me!List42 & Chr$(34)
.FindFirst strSearch
If .NoMatch = True Then
.AddNew
![MP3] = strItem
![personsname] = Me!list42
.Update
End If
End With



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BrianPaul said:
I wish it was that easy but its not. The form is not bound to any table or
querry. The listbox has only 1 field. However how is it written to the
table.

Douglas J. Steele said:
Depending on which field of the listbox is the bound field, and assuming
that it's not a multi-select listbox, you'd simply refer to Me!list42 (or
Me.list42) to determine what value is currently selected in the listbox.

Note that if no name has been selected, Me!list42 will return Null.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Below I took the example from Helen Feddema. What I accomplished was a
playlist to play MP3's and the below code works.

However, I wanted to store the records under someones name. So what I
did
was added a field to the TPlaylistSelect table called personsname. I
also
made another table called TNAMES to store the persons name who wants to
make
the playlist. So on my form I created a List box (list42) based on the
TNAMES table. Now when I open the form I select the name from the list
box
then I can select records and add them to the play list.

Here is my question: When the record is written to the table
TPlaylistSect
how can I also grab the valuse from List42 that is present on the form
and
write it also to personsname field. I hope someone may have an answer.
Keep
in mind it doesnt have to be a listbox I could always create a combo
box
to
do the same thing.

Thanks.




Option Compare Database
Option Explicit

Dim xlstAvailable As Access.ListBox
Dim xlstSelected As Access.ListBox
Dim strItem As String
Dim strSQL As String
Dim dbs As DAO.Database
Dim rstAvailable As DAO.Recordset
Dim rstSelected As DAO.Recordset
Dim rstPersonAvailable As DAO.Recordset
Dim strSearch As String
Dim varItem As Variant
Private Sub cmdAdd_Click()
'Written by Helen Feddema 11-2-2000
'Last modified 11-14-2000

On Error GoTo ErrorHandler

Set xlstSelected = Me![ResultMP3]
Set xlstAvailable = Me![List50]

'Check that at least one item has been selected
If xlstAvailable.ItemsSelected.Count = 0 Then
MsgBox "Please select at least one item"
xlstAvailable.SetFocus
Exit Sub
End If

Set dbs = CurrentDb
Set rstSelected = dbs.OpenRecordset("TPlaylistSelect", dbOpenDynaset)
Set rstAvailable = dbs.OpenRecordset("TMP3", dbOpenDynaset)

For Each varItem In xlstAvailable.ItemsSelected
strItem = Nz(xlstAvailable.Column(0, varItem))

Debug.Print "Selected item: " & strItem

'Append selected item to Selected Items list
With rstSelected
strSearch = "[MP3] = " & Chr$(39) & strItem & Chr$(39)
.FindFirst strSearch
If .NoMatch = True Then
.AddNew
![MP3] = strItem
.Update

End If
End With

'Delete selected item from Available Items list
'With rstAvailable
'strSearch = "[MP3] = " & Chr$(39) & strItem & Chr$(39)
'.FindFirst strSearch
'If .NoMatch = False Then
' .Delete
'End If
'End With

Next varItem
rstSelected.Close
rstAvailable.Close
xlstAvailable.Requery
xlstSelected.Requery
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can the below code be modified for select. Drive Letter, Dir. 2
List Box Multi Select problem 5
Two multi-select listboxes 5
Multi Select List Box 5
Form question 1
Array Question 3
Multi-select list box as query parameter 15
Help with browse button in Access 2010 0

Top