listbox value/data

[Guest]

I am using the below function to select multiple values in one listbox and
place them into another listbox on the same form. When I close the form, I am
trying to take the multiple values and save them into an applicable field in
the associated table. I can get all the other textbox fields to save, but not
this part.

Thanks in advance

Function CopySelected(frm As Form) As Integer

Dim ctlsource As Control
Dim ctldest As Control
Dim stritems As String
Dim intCurrentRow As Integer
Set ctlsource = frm!List20
Set ctldest = frm!lstDestination
For intCurrentRow = 0 To ctlsource.ListCount - 1
If ctlsource.Selected(intCurrentRow) Then
stritems = stritems & ctlsource.Column(1, _
intCurrentRow) & ";"

End If
Next intCurrentRow
' Reset destination control's RowSource property.
ctldest.RowSource = ""
ctldest.RowSource = stritems

End Function

 

[Sandra Daigle]

First, do you REALLY want to store multiple values in a single field? This
is a departure from normalized form and is best done only for temporary
purposed (ie reporting). The individual values would be better off being
stored in a related table. You could easily adapt the following code to
create related records rather than stringing values into a single field.

Regardless, to do what you want there is an easier way using the
ItemsSelected property of the listbox:

Dim varItem As Variant
Dim stritems As String
For Each varItem In frm!List20.ItemsSelected
stritems = stritems & frm!List20.Column(1,varItem) & "; "
Next varItem

 

[Guest]

Sandra,
If I used your code, how do I save these values in a fld of a table? This is
still the problem I can't figure out?

Thanks

First, do you REALLY want to store multiple values in a single field? This
is a departure from normalized form and is best done only for temporary
purposed (ie reporting). The individual values would be better off being
stored in a related table. You could easily adapt the following code to
create related records rather than stringing values into a single field.

Regardless, to do what you want there is an easier way using the
ItemsSelected property of the listbox:

Dim varItem As Variant
Dim stritems As String
For Each varItem In frm!List20.ItemsSelected
stritems = stritems & frm!List20.Column(1,varItem) & "; "
Next varItem

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I am using the below function to select multiple values in one
listbox and place them into another listbox on the same form. When I
close the form, I am trying to take the multiple values and save them
into an applicable field in the associated table. I can get all the
other textbox fields to save, but not this part.

Thanks in advance

Function CopySelected(frm As Form) As Integer

Dim ctlsource As Control
Dim ctldest As Control
Dim stritems As String
Dim intCurrentRow As Integer
Set ctlsource = frm!List20
Set ctldest = frm!lstDestination
For intCurrentRow = 0 To ctlsource.ListCount - 1
If ctlsource.Selected(intCurrentRow) Then
stritems = stritems & ctlsource.Column(1, _
intCurrentRow) & ";"

End If
Next intCurrentRow
' Reset destination control's RowSource property.
ctldest.RowSource = ""
ctldest.RowSource = stritems

End Function

 

[Sandra Daigle]

Is the table bound to the form or is it a different table?

If it's bound (and a single multivalue field) then just assign the value
from stritems to a bound control on your form:

me.MyItems=stritems

Otherwise you can use DAO to open a recordset on a related table and add the
related records. Here is some code that I have used to add records to a
table based on the selected items in a listbox:

Dim varItem As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tblPersonClasses where
PersonNbr=-1")
For Each varItem In Me.lstAvailable.ItemsSelected
With rst
.AddNew
' All added records have the same value in "classid"
.Fields("Classid") = Me.ClassId
.Fields("PersonNbr") = Me.lstAvailable.ItemData(varItem)
.Update
End With
Next varItem
rst.Close
Set rst = Nothing
Set db = Nothing
Me.lstAvailable.Requery
Me.lstSelected.Requery



--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Sandra,
If I used your code, how do I save these values in a fld of a table?
This is still the problem I can't figure out?

Thanks

First, do you REALLY want to store multiple values in a single
field? This is a departure from normalized form and is best done
only for temporary purposed (ie reporting). The individual values
would be better off being stored in a related table. You could
easily adapt the following code to create related records rather
than stringing values into a single field.

Regardless, to do what you want there is an easier way using the
ItemsSelected property of the listbox:

Dim varItem As Variant
Dim stritems As String
For Each varItem In frm!List20.ItemsSelected
stritems = stritems & frm!List20.Column(1,varItem) & "; "
Next varItem

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I am using the below function to select multiple values in one
listbox and place them into another listbox on the same form. When I
close the form, I am trying to take the multiple values and save
them into an applicable field in the associated table. I can get
all the other textbox fields to save, but not this part.

Thanks in advance

Function CopySelected(frm As Form) As Integer

Dim ctlsource As Control
Dim ctldest As Control
Dim stritems As String
Dim intCurrentRow As Integer
Set ctlsource = frm!List20
Set ctldest = frm!lstDestination
For intCurrentRow = 0 To ctlsource.ListCount - 1
If ctlsource.Selected(intCurrentRow) Then
stritems = stritems & ctlsource.Column(1, _
intCurrentRow) & ";"

End If
Next intCurrentRow
' Reset destination control's RowSource property.
ctldest.RowSource = ""
ctldest.RowSource = stritems

End Function

 

[Guest]

Thanks Sandra,

This is what I did;

Dim varItem As Variant
Dim stritems As String
Set ctldest = frm!lstDestination
For Each varItem In frm!List20.ItemsSelected
stritems = stritems & frm!List20.Column(0, varItem) & "; "
Next varItem

ctldest.RowSource = stritems
Me.[Applicable SWABs] = stritems

and it saves it to the table.
Thank you very much.

Is the table bound to the form or is it a different table?

If it's bound (and a single multivalue field) then just assign the value
from stritems to a bound control on your form:

me.MyItems=stritems

Otherwise you can use DAO to open a recordset on a related table and add the
related records. Here is some code that I have used to add records to a
table based on the selected items in a listbox:

Dim varItem As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tblPersonClasses where
PersonNbr=-1")
For Each varItem In Me.lstAvailable.ItemsSelected
With rst
.AddNew
' All added records have the same value in "classid"
.Fields("Classid") = Me.ClassId
.Fields("PersonNbr") = Me.lstAvailable.ItemData(varItem)
.Update
End With
Next varItem
rst.Close
Set rst = Nothing
Set db = Nothing
Me.lstAvailable.Requery
Me.lstSelected.Requery



--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Sandra,
If I used your code, how do I save these values in a fld of a table?
This is still the problem I can't figure out?

Thanks

First, do you REALLY want to store multiple values in a single
field? This is a departure from normalized form and is best done
only for temporary purposed (ie reporting). The individual values
would be better off being stored in a related table. You could
easily adapt the following code to create related records rather
than stringing values into a single field.

Regardless, to do what you want there is an easier way using the
ItemsSelected property of the listbox:

Dim varItem As Variant
Dim stritems As String
For Each varItem In frm!List20.ItemsSelected
stritems = stritems & frm!List20.Column(1,varItem) & "; "
Next varItem

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Cuz wrote:
I am using the below function to select multiple values in one
listbox and place them into another listbox on the same form. When I
close the form, I am trying to take the multiple values and save
them into an applicable field in the associated table. I can get
all the other textbox fields to save, but not this part.

Thanks in advance

Function CopySelected(frm As Form) As Integer

Dim ctlsource As Control
Dim ctldest As Control
Dim stritems As String
Dim intCurrentRow As Integer
Set ctlsource = frm!List20
Set ctldest = frm!lstDestination
For intCurrentRow = 0 To ctlsource.ListCount - 1
If ctlsource.Selected(intCurrentRow) Then
stritems = stritems & ctlsource.Column(1, _
intCurrentRow) & ";"

End If
Next intCurrentRow
' Reset destination control's RowSource property.
ctldest.RowSource = ""
ctldest.RowSource = stritems

End Function