Complicated Listbox question (1 Viewer)

[mumbles10]

Ok... I have two listboxes. I want to be able to select information from the left listbox and be able to move it to the right listbox. I created a table with option boxes to be able to choose yes/no to make it easier.

The rowsource for the left box is:

SELECT tblSampleEnt.EntitlementID, tblSampleEnt.Entitlement_Name AS Expr1
FROM tblSampleEnt
WHERE (((tblSampleEnt.Flag)=False))
ORDER BY tblSampleEnt.EntitlementID;

The rowsource for the right box is:

SELECT qrySysEnt.[Entitlement Name]
FROM qrySysEnt;

I can get each box to work independently of each other. There is a combo box that when chosen adjusts the information on the right box.

My obstacle is that when I choose the combo selection how do I get it to read the information from the dummy table so you can't choose one of the selections that is already on the right side. I know there needs to be some code written for the AfterUPdate of the combo just not sure what...

Any help or suggestions.

Thanks.

 

[jdraw]

There's an example here that may help.. video tutorial

http://www.datapigtechnologies.com/flashfiles/listboxtrick.html

 

[gemma-the-husky]

I can't get the demo to run

the way to do it is to have the left combo based on a query that includes items where the selectedfag is false, and the right combo, based on the a similar query, but with true items.

you then have a button to move items around, and the code needs to be, in the button click event

a) update item set selectedflag = whatever (ie true or false) for the selected item THEN
b) requery both combo boxes.

 

[mumbles10]

I can't get the demo to run

the way to do it is to have the left combo based on a query that includes items where the selectedfag is false, and the right combo, based on the a similar query, but with true items.

you then have a button to move items around, and the code needs to be, in the button click event

a) update item set selectedflag = whatever (ie true or false) for the selected item THEN
b) requery both combo boxes.

Agreed with everything you said. I understand the mechanics of it... however, I am not sure how to code it.

Just for clarification here are the tables associated with this:

tblSysEnt:
SysEntID
SystemID
EntID

tblENT:
EntID
EntName

tblSys:
SysID
SysName

tblSample
EntID
EntName
EntYesNo

Basically the left listbox is tblSample. Right list box is a query of SysEnt, Sys and Ent. When the combo selects a Sys the right list box filters the Sys to which Ent is associated with that.

 

[gemma-the-husky]

I am not following what you are doing - but you sohouldn't need filters - just two queries

the left box needs to be based on a query

select whatever from whatever where flag = false

the right box needs to be based on a query

select whatever from whatever where flag = true

and you need an update query to change the values for the selected item - which makes the item appear to jump from one box to the other

 

[mumbles10]

I am not following what you are doing - but you sohouldn't need filters - just two queries

the left box needs to be based on a query

select whatever from whatever where flag = false

the right box needs to be based on a query

select whatever from whatever where flag = true

and you need an update query to change the values for the selected item - which makes the item appear to jump from one box to the other

I agree with you... however, the combo box throws a wrinkle in it. I could get it to work if the combo box didn't need to be involved. Each Sys can have multiple Ents... There are already multiple Ents established with each Sys. For new Sys's its not a problem. For existing Sys's I need to make sure that a user can't select Ents that are already established.

For instance... I have Sys1, Sys2 and Sys3 in the combo box... Sys1 has Ent1, Ent2 and Ent3. Sys2 has Ent2, Ent3 and Ent4. When I choose Sys1 it shows Ent1, 2, 3 in the right list box. In the left list box there is Ent1 through Ent20. Ents are not mutually exclusive to each Sys. They can be used multiple times. I need to write a code in the afterupdate that marks the left list box with already established Ents so I can't choose them again and move to the right listbox.

 

[DCrake]

take a look at this Link

 

[jdraw]

Did you watch the video? Does it not apply to your situation.
Can you explain ( with a sample) what exactly should be happening under what condition?
Perhaps a jpg or 2 or some sample data.

 

[mumbles10]

take a look at this Link

Thanks DCrake... your example was the basis for everything that I have been trying to do. My form is just slightly different then yours and am not sure how to change it.

My right listbox is a query of the joiner table of tblSys and tblEnt. It shows which Ent is linked to each Sys. Now there are 40 different Sys' which can have a multiple of the 200+ Ents. So when I select the combo it runs the query based on the selection. All of your information with the flags is in one column. So if my table had 3 columns and Sys ID could have the same sys multiple times I am not sure how to write the VBA that would search for the Sys based on the selection then mark the Ent true or False based on that... I could attach an example if you think it could help.

 

[mumbles10]

Did you watch the video? Does it not apply to your situation.
Can you explain ( with a sample) what exactly should be happening under what condition?
Perhaps a jpg or 2 or some sample data.

The video is great... but it doesn't have the combo example. I need the user to not have the option of choosing the same Ent. Because once the user was finished and needed to save the info, I wouldn't want it to duplicate.

 

[jdraw]

Can you please describe exactly what you are doing? I am confused with your post. Also, you indicated 2 listboxes and now there's a combo somewhere.

I'd like to help but can not guess what the situation is.

And David's sample at his link seems similar to the video I suggested.

 

[mumbles10]

Can you please describe exactly what you are doing? I am confused with your post. Also, you indicated 2 listboxes and now there's a combo somewhere.

I'd like to help but can not guess what the situation is.

And David's sample at his link seems similar to the video I suggested.

JDraw...

I have attached the db.. I got rid of some the non-pertinent proprietary information that will be stored within... The combo box populates the listbox on the right... It is based on a query. The listbox on the left is populated by a dummy table that I was hoping would move the items back and forth between the listboxes. I need to figure out when I choose the combo box option, that the listbox on the left doesn't give the option of the ENT that is already populated on the right.