recall listbox selections on a form

Steven,

I would suggest creating a table to store the values you selected.  For example:

In lstSelect (your selection list box) you click on, say, 4 different items.  Once you move to the next record (or click a button.. whatever it is that you go on to), store the selected values in a table.  Once you return to that record,  you just re-set the ItemsSelected property to what you saved in the past.  Hope that'll help you get started

Do you have a sample I could get? not sure how to structure the table, how to store the selected values in the table or how to re-set the itemsSelected propert to what is saved.
Sorry, I just need to get a bit more step by step instructions since I am not familiar how to do all this.

Actually I think I can make it even easier for you.. give me 5 minutes to work on it..

I'm running into some probs & the boss is calling for some work.. any experts around to finish this question, I'm sure sherkanowski and I would appreciate it...

Okay this is what I've been able to work on thus far:

here is the code for creating a message box that shows you what item you've selected:

Private Sub [Insert Command Button Name Here()
'Call SaveSelected
SaveSelected Me

End Sub

Public Sub SaveSelected(ByRef frm As Form)

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItem As String
Dim intCurrentRow As Integer

Set ctlSource = frm![insert Selection List Box]

For intCurrentRow = 0 To ctlSource.ListCount - 1
    If ctlSource.Selected(intCurrentRow) Then
        strItem = ctlSource.Column(0, intCurrentRow)
    End If
Next intCurrentRow

MsgBox strItem

End Sub

strItem stores the data in a string, and the MsgBox strItem line displays the selected item in a message box.  Now what you'll need to do is put that data in a table.  In addition, since you had a multiselect feature on your list box, you will need to cycle through and put in everything selected for that record in your query.  Then, when you reopen the query, specifically that record, you will need to reconstruct the string to select particular items.  That is what I'm working on now, but this is a start...

Let me expand on ssilvia84's suggestions...

A) Store selection in a text field

This idea implies the creation of a large text field or memo field, that will contain the *row numbers* of the currently selected items for each record in the form. The content of the field at a given time could be "0,4,8", to indicate that these rows of the listbox are currently selected.

You will then need to trap to _events_ on the form, Form_Current and lstTheListBox_Change. The first will be used to *select* the appropriate rows in the listbox whenever the user surfs to a new record and the second will be used to *store* that selection after each change.

This has several drawbacks, however. The most obvious is that the storing of *row numbers* is not efficient if the content of the list box is likely to change. And trust me, it will change. You could of course use some form of key to identify the rows in the list box ("Y" for yellow, "B" for blue, etc.). Again, the coding will change at some point and you will have a new problem...

B) Store selection in a link table

The rational solution is to store the link information in another table. Let's imagine Items and Colors. You want to store (i.e. "select") a certain number of colors for each item. In other words, you want to store the information that item X has colors G, I, and Y.

The "normal" way to do this is to create a link table contain pairs like "X, G", "X, I", "X, Y", describing this relation (called a "many-to-many" relationship, btw). In the interface, you show a list box with the currently selected colors and either a combo box or another list box to let the user "add" colors. The user can also select on the "selected" colors and "delete" it.

This type of interface is now very comon and easy to understand for the user. It is also relatively easy to program (a full example is available on microsoft's site).

C) Display links as check boxes

Another easy interface is to display the list of all colors together with a check box where the user can simply tick those that correspond. Easy for the user, but surprisingly complex for the programmer... I went this route in some instances, but i do not recommend it in this case.

D) Reprogram the "multi select" list box using a link table

This is a combination of A) and B). In the Form_Current event, you run a query on the link table to determine which rows should be selected in the list box. In the lstTheListBox_Change event, you run two update queries: one to delete all known links for the current Item and a second to store all selected colors from the list box to the link table.

Actually, a little more efficient way to to this is to simply edit any field in the lstTheListBox_Change event and then to use the Form_AfterUpdate event to store the current selection once and for all. But this is again a little more involved technically.


I know this is a little theoretical, but it's sometimes best to step back to look at the whole picture.

Hope this helps

thanks harfang... I suppose I could have explained that in depth like you did =)  I just kinda attacked the code.  I took a lunch and am back now, but if you want to take it from here (specifically the code aspect) by all means feel free.  I'm working on an open question of my own, something similar in some ways, so you're welcome to take over.  Thanks again..

Questions ...

1.) What action on the form causes the items to become de-selected???  Is the running of the Query??
2.) What is the content of the ListBox???  Is each item in the list box unique by some sort of description or item number??

You could store the items selected down to a temp table then re-select them (based on the unique identifer other than the
row number) after the action that causes them to become de-selected.

ET

Sherankowski... Try this...

I had a combo box called cbo_Sel on my form, and a list box called lst_reports.  Let me know if it works...

I also created a function called IsItemFlagSet to determine if an item was flagged as selected... the code is at the bottom..

Private Sub cbo_Sel_AfterUpdate()
    Dim selItem As Integer
    Dim lstRpt As Integer

    If Me!cbo_user.Column(2) <> "" Then
        selItem = Me!cbo_user.Column(2)
        For lstRpt = 0 To (Me!lst_Reports.ListCount - 1)
            If IsItemFlagSet(Me!lst_Reports.Column(0, lstRpt), selItem) = True Then
                Me!lst_Reports.Selected(lstRpt) = True
            Else
                Me!lst_Reports.Selected(lstRpt) = False
            End If
        Next lstRpt
    Else
        For lstRpt = 0 To (Me!lst_Reports.ListCount - 1)
            Me!lst_Reports.Selected(lstRpt) = False
        Next lstRpt
    End If

End Sub

Public Function IsBitFlagSet(Flag As Integer, Pos As Integer) As Boolean
    If (Flag And Pos) = 0 Then
        IsBitFlagSet = False
    Else
        IsBitFlagSet = True
    End If

End Function

ssilvi84 - could you email your sample code?

yeah will do

I was able to get Sherk to successfully use my cbo/list box combination...

Now I'll convert to your list box.. could you please explain some more about moving between records with your cmd buttons?

The form which my list boxes are on is actually a subform. I created custom navigation buttons which work similar to the built in navigation to advance to the next record. records on the subform have a relationship with the records selected on the main form. as I navigate through the records on the subform using the custom navigation buttons I would like the highlighted listbox selections to be recalled.

Sorry I've been having pc/inet problems all night.. rather irritating... I use various online resources if I need a reference or something, and haven't been able to access 2 webpages in a row without Page cannot be displayed... very frustrating.  

What you're asking then is to convert my code into something that will store the selected values when you move to another record using your custom nav buttons... I'll get on that.

That is exactly what I need, and then to retrieve and highlight those selections when I go back to that record. Thanks!

Sherk... a little help for me please.. just for context

When you say that your list box is populated by a query, and you want to save the selected items when you go back.... What is it that you're saving? This is what I think you're doing..

Lets say you've got a form that you use to place a purchase order for more products for your store.  On your form, you select an item.  In your subform, you populate a query with say, colors for that item.  You then click the color(s) that you'd like to purchase, to restock your shelves.  

What I'm curious about is: How in my example would you utilize custom nav buttons?  When you move to the next record, what would you be moving to?  Is your listbox more then 1 column?

Please let me know...

In addition.. are your buttons on your subform or your main form?

The list box is populated from a query of a table - listbox/query updates based on other checkbox selections on the subform.

Main form is customer -
Subform is jobs for that particular customer

the list box selections represent items used for that particular job. As they select from the items in the listbox there is another field which calculates price based on those selections. when I go to another record and come back the correct price is listed in the price field (since I have that bound to a table) however there is no way to see the items that were selected.

I have created custom navigation buttons on both the main form and sub form to navigate through customers and jobs.

okay well what I'm working on right now has a list box in a subform with the nav buttons on the main form.  I'm hoping to be done in about 10 minutes with this.. i've got one bug to fix and i'm done..

Quick note... the next record button in the subform doesn't work, so don't bother trying to click it :)  What I hope you get out of this is the code you'll need to incorporate into YOUR application.... this example shows you how to store selections from a list box of a subform, as per your request.

Got your sample, thanks. I will try to modify to get my db working using this as a baseline sample.

Sherk...

I recently found out I am not able to send anything via e-mail (regardless of what may be seen in other threads).  It's simply <not> allowed.  Because of that, we will have to feed everything through here.  In addition, in here:

https://www.experts-exchange.com/help.jsp#hs8

The help topic talks about how we're not supposed to ask questions within questions (Something we're all guilty of from time to time).  I believe your initial question has been answered -- about how to store selections in a list box.  If you want to close this topic out and start a new one, i'll gladly help you with modifying it to your dbase if you need it.  I'm glad I could help, thanks for bearing with me through some tech probs...