By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,611 Members | 1,464 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,611 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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" <sa*******@yahoo.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.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_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
Nov 13 '05 #2

P: n/a
David -
Been working on this and the box won't populate. I'm getting
"438: Object Doesn't support this property or Method"

on

ctlDest.AddItem ctlSource.Column(0, intCurrentRow) & ";" _
& ctlSource.Column(1, intCurrentRow)

I use Intellisense and AddItem and RemoveItem are NOT in the list when
I typed in the code.

I am on Access 2000, and I have references DAO 3.6
VB for Applications
Access 9.0 Library
OLE Automation
Microsoft ActiveX data Objects 2.1 (still failed when I removed 2.8 and
put in 2.8).

Can you help on this? Code compiles fine, so I didn't think it was a
missing reference, but I can't find the answer on other posts. VBA Help
shows the methods so I'm not sure what to do.
Thanks-
Sara

Nov 13 '05 #3

P: n/a
Sara:

Unfortunately I am not running Access 2000, so I am limited in my ability to
test any recommendation I might give you. You did not say whether all of
your code is behind a form (i.e. in a Form module), however, assuming that
it is, you can use direct references to the two list boxes instead of
assigning them to generic control objects. So, if this is the case, then I
would recommend changing the ctlDest and ctlSource references with the
appropriate list box names: lstAllDepts and lstDeptsForAd. This is one
potential source of the error you are seeing.

If that does not do the trick, let me know and look at other resolutions.

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

This response is supplied "as is" without any representations or warranties.
"sara" <sa*******@yahoo.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
David -
Been working on this and the box won't populate. I'm getting
"438: Object Doesn't support this property or Method"

on

ctlDest.AddItem ctlSource.Column(0, intCurrentRow) & ";" _
& ctlSource.Column(1, intCurrentRow)

I use Intellisense and AddItem and RemoveItem are NOT in the list when
I typed in the code.

I am on Access 2000, and I have references DAO 3.6
VB for Applications
Access 9.0 Library
OLE Automation
Microsoft ActiveX data Objects 2.1 (still failed when I removed 2.8 and
put in 2.8).

Can you help on this? Code compiles fine, so I didn't think it was a
missing reference, but I can't find the answer on other posts. VBA Help
shows the methods so I'm not sure what to do.
Thanks-
Sara
Nov 13 '05 #4

P: n/a
David -
Thanks, but that did not work. I am thinking it's stopping on .AddItem
as this is different from my original code that was working (but I
couldn't delete the items! - .RemoveItem)

I am running this from a form.

Other ideas? I am wondering if the problem is AddItem is there a
substitute in Access 2000 or something?

thanks for continuing to help -
Sara

Nov 13 '05 #5

P: n/a
Sara:

I did confirm that the AddItem method was not available in Access 2000. I
will have to look at alternative solutions for you.

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

This response is supplied "as is" without any representations or warranties.
"sara" <sa*******@yahoo.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
David -
Thanks, but that did not work. I am thinking it's stopping on .AddItem
as this is different from my original code that was working (but I
couldn't delete the items! - .RemoveItem)

I am running this from a form.

Other ideas? I am wondering if the problem is AddItem is there a
substitute in Access 2000 or something?

thanks for continuing to help -
Sara
Nov 13 '05 #6

P: n/a
Thank you. I look forward to your ideas!
Sara

Nov 13 '05 #7

P: n/a
Sara:

The code below adjusts the code you originally posted. Again, since I don't
have Access 2000 installed, I cannot guarantee its operation on that
version, however, it does not use the AddItem or RemoveItem methods. I took
a slightly different approach from the original code in that I remove the
selected department from the AllDepts list box when it is added to the
DeptsForAd listbox. This prevents a department from being selected more
than once. Give it test drive and let me know how it handles.

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 strItemsDest As String
Dim strItemsSource As String
Dim strItemToAdd As String
Dim intCurrentRow As Integer

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

strItemsSource = ctlSource.RowSource
strItemsDest = ctlDest.RowSource

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) And
ctlSource.ItemData(intCurrentRow) <> "" Then
strItemToAdd = ctlSource.Column(0, intCurrentRow) & ";" _
& ctlSource.Column(1, intCurrentRow) & ";"
strItemsDest = strItemsDest & strItemToAdd
strItemsSource = Replace(strItemsSource, strItemToAdd, "")
End If
Next intCurrentRow

ctlSource.RowSource = strItemsSource
ctlDest.RowSource = strItemsDest

Set ctlSource = Nothing
Set ctlDest = Nothing
End Function

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

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItemsDest As String
Dim strItemsSource As String
Dim strItemToAdd As String
Dim intCurrentRow As Integer

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

strItemsSource = ctlSource.RowSource
strItemsDest = ctlDest.RowSource
If Right(strItemsDest, 1) <> ";" Then strItemsDest = strItemsDest &
";"

' Just want to delete the selected row.
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) And
ctlSource.ItemData(intCurrentRow) <> "" Then
strItemToAdd = ctlSource.Column(0, intCurrentRow) & ";" _
& ctlSource.Column(1, intCurrentRow) & ";"
strItemsDest = strItemsDest & strItemToAdd
strItemsSource = Replace(strItemsSource, strItemToAdd, "")
End If
Next intCurrentRow

ctlSource.RowSource = strItemsSource
ctlDest.RowSource = strItemsDest

Set ctlSource = Nothing
Set ctlDest = Nothing

End Function

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

This response is supplied "as is" without any representations or warranties.
"sara" <sa*******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Thank you. I look forward to your ideas!
Sara
Nov 13 '05 #8

P: n/a
David -
I tried the code you wrote - thank you very much - and made 2 changes
so far.

1. I inserted "End If" in the Remove Selected Function
Set ctlSource = frm!lstDeptsForAd
Set ctlDest = frm!lstAllDepts
strItemsSource = ctlSource.RowSource
strItemsDest = ctlDest.RowSource
If Right(strItemsDest, 1) <> ";" Then strItemsDest =
strItemsDest &
";"

' INSERTED BY SARA
End If

' Just want to delete the selected row.
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) And

2. I commented out one line (see below) as i stepped through the code
and it seemed that this line was causing ALL the source departments to
be removd, not just the ones I selected. Is there any way to fix that?
It is not critical, but would be good to avoid duplicates.

Next intCurrentRow
ctlSource.RowSource = strItemsSource
' COMMENTED OUT BY Sara
' ctlDest.RowSource = strItemsDest
Set ctlSource = Nothing
Set ctlDest = Nothing

End Function

I tried the code to write to the table, and it seems to work - thanks!
I'm now going to have to put some error handling in that code - we'll
see how I do.

Thanks again - I am checking the posts, but not daily as I'm actually
on a cruise and have somewhat limited access to the internet. But I
need to keep this going so I keep learning and don't backslide on my
progress.

Sara

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.