Paired List Boxes

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.
darrylmaisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

darrylmaisAuthor Commented:
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
dan_vellaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
darrylmaisAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dan_vellaCommented:
Nearly I think! You have the structure dead on.

You are effectively transferring the selected value from one table to another
0
Julian HansenCommented:
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
darrylmaisAuthor Commented:
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
Julian HansenCommented:
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
darrylmaisAuthor Commented:
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
Julian HansenCommented:
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
dan_vellaCommented:
Still need help with this?
0
darrylmaisAuthor Commented:
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
Julian HansenCommented:
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
darrylmaisAuthor Commented:
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
Julian HansenCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.