Listbox Updating to a table... (1 Viewer)

[mumbles10]

Okay... I have three tables...

tblSystem Entitlements
SE_ID
SystemID
EntitlmentID

tblSystems
SystemID
System Name

tblEntitlements
EntitlementID
EntitlementName

I have a form with a combobox (cboSystems) which lists all the System Names which is pulling from tblSystems.. I have one listbox (lboAllEntitlements) that list all entitlements available. This is pulling from tblEntitlements. Finally I have a 2nd listbox (lboCurrentEnt) which is run from a query showing all the entitlements available to the combo box selection.

My hope is that I can have a user choose entitlements from lboAll and move them when needed to lboCurrent and vice versa... My fear is that the lboCurrent will not update the tblSystemEntitlements... any thoughts on how I can do this?

 

[John Big Booty]

Sounds like you are looking for a Cascading Combo/Listbox.

 

[mumbles10]

Thanks John... but it doesn't really help for my situation. I need to find a solution for moving items between the listboxes then updating tblSystemEntitlements. I am confused as how to link lboCurrentEnt with the table in order to update it. Any suggestions?

 

[boblarson]

To move between them you will either need to have a table to place the items or you will have to use value lists which you create on the listboxes programatically. You can have a table which temporarily has the data you want and have a checkbox to denote it is in one listbox and then when unchecked it is in the other. Then you can have code which runs an update query based on the selections that are moved over to update the checkbox and then requery the listboxes so the appropriate values show up. For multiselect you would need code to iterate through so you can build your where clause for your query.

The same for storing the values. You would need a looping bit of code to find the selected items and insert into the tblSystem Entitlements table (or delete)

 

[mumbles10]

Thanks Bob... so basically, what you are saying is that I could have a table with all the entitlements (there are over 200) with a yes/no option. For the ones selected they would change depending the selection. My question to you is how would I uncheck the entitlements based on the other list box... so when I choose an option in my combobox it populates the other listbox with the current entitlements. I wouldn't want to have more then one of the same entitlement... it is making more sense to do it this way. I just need some fine tuning.

 

[boblarson]

You use the same table for both listboxes. The difference is that those that are checked would be included in the row source of the one where they are selected.

I'll upload a quick sample with a small table just to show how it can work. It may take me a few to get it uploaded.

 

[mumbles10]

You use the same table for both listboxes. The difference is that those that are checked would be included in the row source of the one where they are selected.

I'll upload a quick sample with a small table just to show how it can work. It may take me a few to get it uploaded.

Thanks John.

 

[boblarson]

Here you go.

 

[mumbles10]

Here you go.

Bob, that's awesome. One more question then I think I can figure the rest out... how would I link the combo selection to the one listbox? I have to be able to code to match up entitlements between the sample data and the actual table. It would have to look down the system id and then match up the entitlement ids between the two lists. Then make the sample data false for ones that are already selected. Any suggestions?

 

[boblarson]

I'm not sure I understand how the combo fits into this. I would need to see at least a screenshot showing me how this is supposed to work and then I might be able to give you more information.

 

[mumbles10]

I'm not sure I understand how the combo fits into this. I would need to see at least a screenshot showing me how this is supposed to work and then I might be able to give you more information.

See the screenshot... The combo box controls the one listbox. So you can see what entitlements are for what system. You answered a big question already... just trying to see how I can loop in the combo box.

 

[boblarson]

See the screenshot... The combo box controls the one listbox. So you can see what entitlements are for what system. You answered a big question already... just trying to see how I can loop in the combo box.

The screenshot didn't upload. Did you make sure to click the UPLOAD button after you selected the file? If it successfully uploads it will show a link just below the box where the filepath and name were when you selected the file.

 

[boblarson]

The screenshot didn't upload. Did you make sure to click the UPLOAD button after you selected the file? If it successfully uploads it will show a link just below the box where the filepath and name were when you selected the file.

Never mind, I see you managed to do it.

 

[mumbles10]

The screenshot didn't upload. Did you make sure to click the UPLOAD button after you selected the file? If it successfully uploads it will show a link just below the box where the filepath and name were when you selected the file.

It should be there now.

 

[boblarson]

Okay, so it is pretty much like my sample with the one exception that the row source for each of the listboxes would have the criteria of the SystemID set to the combo box as well.

 

[mumbles10]

Okay, so it is pretty much like my sample with the one exception that the row source for each of the listboxes would have the criteria of the SystemID set to the combo box as well.

Here is the sql statement for the Query that is the RowSource for the other listbox...

SELECT tblEntitlements.[Entitlement Name]
FROM (tblSystems RIGHT JOIN tblSystemEntitlements ON tblSystems.SystemID = tblSystemEntitlements.[System ID]) LEFT JOIN tblEntitlements ON tblSystemEntitlements.[Entitlement ID] = tblEntitlements.EntitlementID
WHERE (((tblSystems.[System Name])=[Forms]![frmSysEnt]![cboSysEnt]));

The AfterUpdate is now only Me!lboCurrentEnt.Requery. I would need something that would look at what is presently attached to the system selected then mark that particular Entitlement true or false on the other listbox... I think that is how I would have to do it... just don't know how to write the code to execute.

 

[mumbles10]

Okay, so it is pretty much like my sample with the one exception that the row source for each of the listboxes would have the criteria of the SystemID set to the combo box as well.

Bob - I know you are a busy guy... didn't know if you had any further thoughts on my last post... I feel I am so close and I think I have the rationale correct, just not sure how to get to the finish line.

thanks.