Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Paired List Boxes

Posted on 2004-11-09
14
Medium Priority
?
510 Views
Last Modified: 2011-10-03
I can't be the first person to ask this, though I can't seem to find a clear answer.  I'm trying to create a pair of list boxes that work similar to the windows wizards.  I have a list of items on the left that the user can click the add button and move to the list box on the right.  I'd also need the ability to click a remove button and take items out of the list box on the right.  I've seen code to do this with single selections at a time, but does anyone happen to have code examples for a list box with multi-select set to extended?  TIA.
0
Comment
Question by:darrylmais
  • 6
  • 5
  • 3
14 Comments
 

Author Comment

by:darrylmais
ID: 12537596
Almost forgot - I'll need to take the list on the right and create table entries for a table that joins clients with customers.  Don't know if that helps explain what I'm trying to do better, but thought I'd throw it in.
0
 
LVL 4

Accepted Solution

by:
dan_vella earned 1000 total points
ID: 12538232
I would set up a TO table to sit behind the second list box. This is how I have done this before.

You select the value in list1 then click an arrow button to perform and append to the table behind list2 and removed from list1 if you require.

the lists are then requeried. let me know if you want guidence with coding this as don't want to do it then loose the points for not getting in quickly!
0
 

Author Comment

by:darrylmais
ID: 12538339
Dan Vella,

Thanks for the response.  I assume you mean that I'd create a temp table that was an exact dupe of the source table for list box 2?  I'd then update my master table with the data contained in the temp table?  Just want to make sure I'm following you.  Thanks.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 4

Expert Comment

by:dan_vella
ID: 12538455
Nearly I think! You have the structure dead on.

You are effectively transferring the selected value from one table to another
0
 
LVL 61

Expert Comment

by:Julian Hansen
ID: 12538611
I wouldn't use a temporary table. I would use a flag in the database to say whether the record was selected or not.

What you would do is this

+-----------------+                   +-----------------+
|                      |                    |                      |
|                      |       <<        |                      |
|                      |       >>        |                      |
|                      |                    |                      |
|                      |                    |                      |
+-----------------+                   +-----------------+

When you initialise the boxes you fill the one on the left with

select [field list] from [source table] where selected_flag = 1

and the one on the right with

select [field list] from [source table] where selected_flag = 0

Whenever you pick up the click on the button [>>] you run through all the selected items on the left  and ...

    set selected_flag = 0
    remove from left hand box
    add to right hand box

and with the [<<] the opposite
   set selected_flag = 1 etc

I did this recently with an application - it worked quite well.

If this is what you are after let me know and I can post some code.

0
 

Author Comment

by:darrylmais
ID: 12566241
Thanks for both the responses.  I still think I'll need to use a temporary table, as I want to populate the right-hand box with the existing "attached" selections before I allow them to edit (hope this makes sense).  I guess I could try it with the checkbox thing - I might be making it more complicated than I need to.  JulianH - if you could post some code, that'd be appreciated.  
0
 
LVL 61

Expert Comment

by:Julian Hansen
ID: 12566769
I scaled down some code from an Access database I wrote a few weeks ago.

You can download this from

http://www.marcorpsa.com/downloads/Q_21200567.zip (14Kb)

The MDB has a form that demonstrates the concept.

Good luck
0
 

Author Comment

by:darrylmais
ID: 12566936
julianH,

Thanks for the code.  I'm kind of new to Access, so this may be a stupid question: if I'm changing the selection criteria in the table itself, wouldn't this immediately impact all users in a multi-user enviro?  For example, I select "Dog" from the master list and it marks it as a selected item - wouldn't that take it out of the list for another user looking at the master list in another part of the app?  Just want to make sure I'm thinking through implementing this method.  Thanks again.
0
 
LVL 61

Expert Comment

by:Julian Hansen
ID: 12567756
Yes it would. The table would be updated immediately.

You could use a temporary table for this - however, you need to be sure that no-one else is updating the table at the same time or you will have a conflict.

>>For example, I select "Dog" from the master list and it marks it as a selected item - wouldn't that take it out of the list for another user looking at the master list in another part of the app?

Probably - but this is how you would want it surely?

Just in case you want to do it this way I modified the original code and posted an update. This version allows you to move items between the list boxes without affecting the underlying table until you press Ok at which point the table is updated. The code is not pretty but it works - best I could do in the time available ;)

Hopefully with the two versions of the database you can solve your problem

Second version is at

http://www.marcorpsa.com/downloads/Q_21200567_01.zip 

0
 
LVL 4

Expert Comment

by:dan_vella
ID: 12840319
Still need help with this?
0
 

Author Comment

by:darrylmais
ID: 12844551
Thanks, Dan.  I did get the user table thing taken care of (using a temp table) but now I seem to be having an issue getting the SQL properly implemented.  Can't see to get the user IDs added to the Co table for some reason.  Syntax looks right to me, but it keeps erroring out.  Any suggestions?

CurrentDb.Execute "INSERT INTO tblCompanyToUser ( companyNo, id ) VALUES ( [Forms]![frmCompany]![companyNo], ( SELECT tblUsers_temp.id FROM tblUsers_temp ))"

No worries if you don't, you're still getting the points for the original portion of this thread :)
0
 
LVL 61

Expert Comment

by:Julian Hansen
ID: 12844622
darrly,

I think you need to close this question and open a new one.

BTW did you take a look at the latest code I posted (refer my previous post)?
0
 

Author Comment

by:darrylmais
ID: 12844648
Thanks, Julian.  I did read your last post and I don't actually want it to take it out of the list, since the company could be associated with multiple details records.  I'm going to go ahead and close this one and open a new one for the SQL code.  
0
 
LVL 61

Expert Comment

by:Julian Hansen
ID: 12844723
Well, that was why I originally recommended a flag approach. That way it stays in the list it is just not listed as selected.

Creating temporary tables just adds an extra level of admin that is not really necessary.

However, if it works for you then great - go for it.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question