Re: Put Item from One List Box BACK into Source List Box and Using Field data

Sara:

Below is some revised code to add items to and remove items from the two
list boxes. I have also added a procedure tied to a button to show one
alternative for saving the data to a table. Obviously, you will need to
adjust this to suit your needs.

The list box control has an AddItem method and a RemoveItem method which can
be used to manipulate the list boxes. This is just another alternative way
of approaching this type of issue.

Without knowing more about your specific objectives, I have not tried to
optimize the code. As such, it just provides the basic functionality you
indicated. For example, if this code is behind the form, you could directly
reference the list boxes, rather than using generic control references.

Private Sub cmdAddDepts_Click()
' If Arrow was clicked
CopySelected Me
End Sub

Private Sub cmdRemoveDepts_Click()
' If arrow clicked to REMOVE dept from List for Ad
RemoveSelected Me
End Sub

Private Sub cmdSaveToTable_Click()
Dim sSQL As String
Dim intCurrentRow As Integer
Dim ctlSource As Control
Dim ctlDest As Control
Dim AdNo As Integer

'Just an example
AdNo = 5

Set ctlSource = Me.lstAllDepts
Set ctlDest = Me.lstDeptsForAd

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlDest.ItemData(intCurrentRow) <> "" Then
sSQL = "INSERT INTO tblAdDepts (AdNo, DeptName) Values (" & AdNo
& ", '" & ctlDest.ItemData(intCurrentRow) & "')"
CurrentDb.Execute sSQL
End If
Next intCurrentRow
End Sub

Sub lstAllDepts_DblClick(Cancel As Integer)
' Sub for Double Click or Using Arrow to Move
CopySelected Me
End Sub

' Sub for Double Click or Using Arrow to Move
Sub cmdCopyItem_Click()
CopySelected Me
End Sub

Function CopySelected(frm As Form) As Integer
Dim ctlSource As Control
Dim ctlDest As Control
Dim intCurrentRow As Integer

Set ctlSource = frm!lstAllDepts
Set ctlDest = frm!lstDeptsForAd

strItems = ctlDest.RowSource
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
'Add the item
ctlDest.AddItem ctlSource.Column(0, intCurrentRow) & ";"
& _
ctlSource.Column(1, intCurrentRow)
'Remove the item
ctlSource.RemoveItem (intCurrentRow)
End If
Next intCurrentRow
End Function


Function RemoveSelected(frm As Form) As Integer
Dim ctlSource As Control
Dim ctlDest As Control
Dim intCurrentRow As Integer

Set ctlSource = frm!lstDeptsForAd
Set ctlDest = frm!lstAllDepts

' Just want to delete the selected row.
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
ctlDest.AddItem ctlSource.Column(0, intCurrentRow) & ";"
& _
ctlSource.Column(1, intCurrentRow)
ctlSource.RemoveItem (intCurrentRow)
End If
Next intCurrentRow
End Function


--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


"sara" <saraqpost@xxxxxxxxx> wrote in message
news:1130637445.349569.46180@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am learning Access and programming. I wanted to have the user select
the departments for an ad from the list of all departments. Found code
(that I could understand) on this site, and it works. But I have 2
quesitons:

1. How can I REMOVE a selection from the Destination List box - and
keep the others there? My first code removes ALL from the Source list;
this code does nothing.

2. How can I now USE the data - I have to populate a table -
tblAdDepts with a record for each dept in the ad. No idea what to do
here.

Many Thanks - code below.
Sara

Private Sub cmdAddDepts_Click()
' If Arrow was clicked
CopySelected Me
End Sub

Private Sub cmdRemoveDepts_Click()
' If arrow clicked to REMOVE dept from List for Ad
RemoveSelected Me
End Sub

Sub lstAllDepts_DblClick(Cancel As Integer)
' Sub for Double Click or Using Arrow to Move
CopySelected Me
End Sub

' Sub for Double Click or Using Arrow to Move
Sub cmdCopyItem_Click()
CopySelected Me
End Sub

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!lstAllDepts
Set ctlDest = frm!lstDeptsForAd


For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0,
intCurrentRow) & ";" _
& ctlSource.Column(1, intCurrentRow) & ";"
End If
Next intCurrentRow


' Reset destination control's RowSource property.
ctlDest.RowSource = ""
ctlDest.RowSource = strItems
End Function


Function RemoveSelected(frm As Form) As Integer
' Not working - does nothing

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer


Set ctlSource = frm!lstDeptsForAd
Set ctlDest = frm!lstAllDepts

' Just want to delete the selected row.
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0,
intCurrentRow) & ";" _
& ctlSource.Column(1, intCurrentRow) & ";"
End If
Next intCurrentRow

' Requery the Ad list control's RowSource property. ??
Me.lstDeptsForAd.Requery

End Function


.

Quantcast