473,846 Members | 1,911 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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

Nov 13 '05 #1
8 2747
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
Nov 13 '05 #2
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.Colum n(0, intCurrentRow) & ";" _
& ctlSource.Colum n(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
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*******@yaho o.com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.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.Colum n(0, intCurrentRow) & ";" _
& ctlSource.Colum n(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
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
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*******@yaho o.com> wrote in message
news:11******** **************@ g43g2000cwa.goo glegroups.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
Thank you. I look forward to your ideas!
Sara

Nov 13 '05 #7
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_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 strItemsDest As String
Dim strItemsSource As String
Dim strItemToAdd As String
Dim intCurrentRow As Integer

Set ctlSource = frm!lstAllDepts
Set ctlDest = frm!lstDeptsFor Ad

strItemsSource = ctlSource.RowSo urce
strItemsDest = ctlDest.RowSour ce

For intCurrentRow = 0 To ctlSource.ListC ount - 1
If ctlSource.Selec ted(intCurrentR ow) And
ctlSource.ItemD ata(intCurrentR ow) <> "" Then
strItemToAdd = ctlSource.Colum n(0, intCurrentRow) & ";" _
& ctlSource.Colum n(1, intCurrentRow) & ";"
strItemsDest = strItemsDest & strItemToAdd
strItemsSource = Replace(strItem sSource, strItemToAdd, "")
End If
Next intCurrentRow

ctlSource.RowSo urce = strItemsSource
ctlDest.RowSour ce = 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!lstDeptsFor Ad
Set ctlDest = frm!lstAllDepts

strItemsSource = ctlSource.RowSo urce
strItemsDest = ctlDest.RowSour ce
If Right(strItemsD est, 1) <> ";" Then strItemsDest = strItemsDest &
";"

' Just want to delete the selected row.
For intCurrentRow = 0 To ctlSource.ListC ount - 1
If ctlSource.Selec ted(intCurrentR ow) And
ctlSource.ItemD ata(intCurrentR ow) <> "" Then
strItemToAdd = ctlSource.Colum n(0, intCurrentRow) & ";" _
& ctlSource.Colum n(1, intCurrentRow) & ";"
strItemsDest = strItemsDest & strItemToAdd
strItemsSource = Replace(strItem sSource, strItemToAdd, "")
End If
Next intCurrentRow

ctlSource.RowSo urce = strItemsSource
ctlDest.RowSour ce = 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*******@yaho o.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .
Thank you. I look forward to your ideas!
Sara
Nov 13 '05 #8
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!lstDeptsFor Ad
Set ctlDest = frm!lstAllDepts
strItemsSource = ctlSource.RowSo urce
strItemsDest = ctlDest.RowSour ce
If Right(strItemsD est, 1) <> ";" Then strItemsDest =
strItemsDest &
";"

' INSERTED BY SARA
End If

' Just want to delete the selected row.
For intCurrentRow = 0 To ctlSource.ListC ount - 1
If ctlSource.Selec ted(intCurrentR ow) 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.RowSo urce = strItemsSource
' COMMENTED OUT BY Sara
' ctlDest.RowSour ce = 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
3060
by: lawrence | last post by:
I've been bad about documentation so far but I'm going to try to be better. I've mostly worked alone so I'm the only one, so far, who's suffered from my bad habits. But I'd like other programmers to have an easier time understanding what I do. Therefore this weekend I'm going to spend 3 days just writing comments. Before I do it, I thought I'd ask other programmers what information they find useful. Below is a typical class I've...
15
2162
by: C White | last post by:
I've got another drop list problem I am using the following code where users select a name, but it should pass a name and email into the table <select name="user"> <option value="<% Response.Write (rsUser("Name")) %>"> <% Response.Write (rsUser("Name")) %> <input type="hidden" name="Email" value="<% Response.Write (rsUser("Email")) %>">
3
3213
by: mal | last post by:
Sorry for repost - system added to another subject for some reason Have tried numerous ideas from the group to solve this one. It is such a simple example that it should be straightforward ! I just want to add a new item to a combo that has data from a file, by typing in the new value , adding to the file and the requerying to get the new valus in the list. i.e. a data entry and data display combo box. I select an item from cmb1 and...
9
19487
by: Bob Alston | last post by:
I have a drop down combo box that gives the user to enter an item not in the list by adding it to the list. The list is a table. It works fine on Access2003 but fails on Access2002/XP. ON XP, it seems to work, shows the message that the entry has been added, then I get an Access error saying the item is not in the list. Here is my code: Private Sub Combo10_NotInList(NewData As String, Response As Integer) intAnswer = MsgBox("The...
3
2998
by: John Walker | last post by:
Hi, On an ASP.NET page I have a drop down list control. When the user pulls down the list and makes a selection, I perform validation, and if the validation fails I want the selected item in the drop down box to go back to what the value was before the user tried to change it, but at that point I will not know what the original value was. Or is there a drop down control "revert" method, or is there any way of knowing what the original...
4
2500
by: techiepundit | last post by:
I'm a Python newbie who just started learning the language a few weeks ago. So these are beginner questions. I have a list of sockets that I use for select.select calls like this: ReadList,WriteList,EventList = select.select(self.SocketList,,,3) In parallel with that list of sockets I want something that is like a list of socket,PacketFragment pairs. I need this because I could do recv and get part of a sent packet. I want to loop...
3
94288
by: sklett | last post by:
I'm changing from a DataGrid to a DataGridView and have run across a problem. The items that are bound to the DataGrid have an int Property that represents a primary key of a lookup table in my database. For example: table JobTypes 1 | Manager 2 | Controller 3 | Supervisor table Employee
6
5862
by: yasodhai | last post by:
Hi, I used a dropdown control which is binded to a datagrid control. I passed the values to the dropdownlist from the database using a function as follows in the aspx itself. <asp:DropDownList ID="FldType_add" Runat="server" DataSource='< %#GetFieldType()%>' DataValueField="Type" DataTextField="Type" /> Oce the page is loaded all the values are added to the dropdown list. But when I thought of getting the selected value from the...
4
2115
by: darrel | last post by:
I have a DDL list along these lines: item value="1" text="a" item value="2" text="b" item value="3" text="c" item value="2" text="d" item value="2" text="e" item value="1" text="f" item value="1" text="g"
0
9879
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9727
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10643
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10705
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10330
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5716
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5907
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4521
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3158
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.