not sure how to approach this - preselecting items in multi select listbox (1 Viewer)

[madEG]

Hello,

I'm grinding my brain on how to approach this application challenge.

(Please see attached image)

The form works like this... #1, select a parent record (strategic priority). #2, select second parent record (committee/department), step #3 select appropriate additional properties (emphasis areas), #4 add title, description, then press add/insert record...

The way the insert happens is a record is inserted into a bridge table, with autoincrement pkeyID and the two Fkeys of the parents in steps 1 and 2.

Then, a routine runs that regets the pkeyID just inserted (backend is sql server using identity) and then uses that pkeyID to insert more child records in a separate bridge table using the new pkeyID (as a fkey) along with the IDs of the selected items in step #3. This part works just fine.

I run into a snag when I wish to update this record...

Currently when the new Objective is selected it shows in another listbox the child records that are associated with it in step #3. (the grey list box to the right of the Objective Title, description etc.)

What I want to do? If I could wave a magic wand...?

I want to be able to pre-select the items in the multi select in step #3 when I double-click on the Objective records.

Generally the way that folks update records in this app is to double click on the record they wish to update, and from the values in the listbox, the text entry areas are filled with the values of the record. Upon update button those text entry areas are resent to the record to update the record's columns.

But, I don't have a way to insert more (or less) of the selected items in #3.

Question1: How do I process the items that are shown in #3, pre-selecting the ones that are are currently associated with the (circled) Objective.

Question2: Any thoughts on how to go about adding or removing the Emphasis Areas that are currently associated with the Objective? e.g., I double click the Objective1 record to load up the text entry boxes (already hapenning), which magically (my problem) auto selects the sub set of items from the grey listbox in the listbox in #3.

If the user were to deselect an item, or select an additional item, how can I teach the program to "know this" so I can remove or add another child record?

I hope this is clear.

Thanks for any ideas/help on any parts of this.

Thanks!

-Matt G.

 

[The_Doc_Man]

To pre-select items in a multi-select list box, you need a source of the preselection keys and a loop.

In essence, you have to do a loop through listbox.Columns(n, index) to find the records to select and then set listbox.Selected(index) = True ... or something like that.

 

[jdraw]

I think you are focused on How and you really haven't told us about WHAT you are working with.

I see terms like Strategy, Objective, Mandate, Emphasis Area(s) Supporting Objective, Department.

You need a model of the relationship among these within your organization/company. If you have such a data model, please post it. If you don't have such, then I recommend you build one --you can start with a description of each of these things in context.

 

[The_Doc_Man]

jdraw is correct. While we can tell you how (technically) to do something, it might be possible that with the information he requests, we could tell you a different way to get to the same result.

For instance, what I told you was true, but the key question is this: Once the preselection is made, can you really choose to deselect what was selected and/or can you choose to select other items than were preselected? I.e. augment or reduce the selection set? If the answer is NO then why bother with the list box? If the answer is YES, then maybe we need to know more to give you the most efficient method for your real goal.

 

[madEG]

Hello,

Thank you all for your responses. I gave this some more thought based on your comments...

Yes, I do have a ER diagram/data model. I won't start anything without it. (attached)

Yes, with my previous thinking, I would have to not only manage to reselect the previously selected items (now stored in the bridge table), but also track them to compare if more were added/removed from the selection, and manage that (inserting new, remove no-longer-selected) in the update routine... Absolutely.

It hit me today that since these child records in the bridge table won't have any children of their own, if, on update of the Objectives parent record, I deleted all the bridge children, and re-inserted again with whatever was *now* selected - then I would not have to store before/after state of records selected, or manage that.

So, I think what I'm going to go with is, on update button event, count if any items selected - if none, then msgbox 'do you wish to add areas of emphasis?' or something to explain they have none selected now - making the user re-select these items for update. Sorta' annoying, yes.

There is never more than seven (at least through year 2021!), so it's not that bad that I don't pre-re-select them for the users.

...as an aside, the "project manager" started changing the architecture while waiting in line to get a sandwich at lunch. "You know what I just thought about...?" I wanted to strangle him.

Thanks folks!
-Matt G.

 

[The_Doc_Man]

It hit me today that since these child records in the bridge table won't have any children of their own, if, on update of the Objectives parent record, I deleted all the bridge children, and re-inserted again with whatever was *now* selected - then I would not have to store before/after state of records selected, or manage that.

Actually, there IS another way that MIGHT be possible. If all of this is in a table and if you have design-level control on the table, do your pre-selection more or less like this:

1. Be sure that your bridging table has what you want in it beforehand.

2. When you make that selection from the prior control that tells you what rows you want selected, have an extra flag in your table that says "Preselected", type Yes/No. Before you load up the multiselect box in question, pre-set the Preselected field to True for all things condoned by the prior control.

3. Now display the contents of the bridge table with all eligible records. (Not all pre-selected records, all the ELIGIBLE records so that you can add or remove records ... bear with me a moment longer). In list- and combo-box controls, you can retrieve all the fields you want but make their displayed width as 0 if you don't want to see them. Be sure that the Preselected field is retrieved but with displayed width zero.

4. Now loop through the rows of the box. You can use the column(n,row) as a way to see what is in each field of each row (remember that in this context, the row number is ZERO-based, not one-based). When the invisible flag says "True" then set the box.Selected(row) flag true. The loop will be for 0 to box.ListRows.Count -1 (or something like that).

5. Now refresh the control or form. Don't requery. Just refresh.

6. As you click on an entry to deselect it, have a click event scan for cases where the box.Selected(row) flag is different than the Preselected flag. Based on other data in the row visible through the box.Columns(n,row) facility, do an update query to clear or set the Preselected flag to match the box.Selected(row) flag. This probably means that if you had a PK, it has to be in the box even if not visible. If you had a compound PK, then all members of the PK have to be in the box even if not visible. You might also be able to use a Change event, though I'm not sure offhand which events work for each box type.

When you are finished, your working query can drive off the state of the Preselected flag rather than trying to work off the state of each selected row in the multiselect box.

 

[madEG]

Mr. The Doc Man, thanks again! Your last post gave me a few ideas. I really appreciate your push in the right direction!

Thank you,
-Matt G.