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_Cli ck()
' 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.lstDeptsForA d
For intCurrentRow = 0 To ctlSource.ListC ount - 1
If ctlDest.ItemDat a(intCurrentRow ) <> "" Then
sSQL = "INSERT INTO tblAdDepts (AdNo, DeptName) Values (" & AdNo
& ", '" & ctlDest.ItemDat a(intCurrentRow ) & "')"
CurrentDb.Execu te sSQL
End If
Next intCurrentRow
End Sub
Sub lstAllDepts_Dbl Click(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_Cli ck()
CopySelected Me
End Sub
Function CopySelected(fr m As Form) As Integer
Dim ctlSource As Control
Dim ctlDest As Control
Dim intCurrentRow As Integer
Set ctlSource = frm!lstAllDepts
Set ctlDest = frm!lstDeptsFor Ad
strItems = ctlDest.RowSour ce
For intCurrentRow = 0 To ctlSource.ListC ount - 1
If ctlSource.Selec ted(intCurrentR ow) Then
'Add the item
ctlDest.AddItem ctlSource.Colum n(0, intCurrentRow) & ";"
& _
ctlSource.Colum n(1, intCurrentRow)
'Remove the item
ctlSource.Remov eItem (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!lstDeptsFor Ad
Set ctlDest = frm!lstAllDepts
' Just want to delete the selected row.
For intCurrentRow = 0 To ctlSource.ListC ount - 1
If ctlSource.Selec ted(intCurrentR ow) Then
ctlDest.AddItem ctlSource.Colum n(0, intCurrentRow) & ";"
& _
ctlSource.Colum n(1, intCurrentRow)
ctlSource.Remov eItem (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" <sa*******@yaho o.com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.com...
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_Cli ck()
' 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_Dbl Click(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_Cli ck()
CopySelected Me
End Sub
Function CopySelected(fr m 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!lstDeptsFor Ad
For intCurrentRow = 0 To ctlSource.ListC ount - 1
If ctlSource.Selec ted(intCurrentR ow) Then
strItems = strItems & ctlSource.Colum n(0,
intCurrentRow) & ";" _
& ctlSource.Colum n(1, intCurrentRow) & ";"
End If
Next intCurrentRow
' Reset destination control's RowSource property.
ctlDest.RowSour ce = ""
ctlDest.RowSour ce = 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!lstDeptsFor Ad
Set ctlDest = frm!lstAllDepts
' Just want to delete the selected row.
For intCurrentRow = 0 To ctlSource.ListC ount - 1
If ctlSource.Selec ted(intCurrentR ow) Then
strItems = strItems & ctlSource.Colum n(0,
intCurrentRow) & ";" _
& ctlSource.Colum n(1, intCurrentRow) & ";"
End If
Next intCurrentRow
' Requery the Ad list control's RowSource property. ??
Me.lstDeptsForA d.Requery
End Function
