?
Solved

recall listbox selections on a form

Posted on 2005-04-11
23
Medium Priority
?
356 Views
Last Modified: 2012-06-27
I have a listbox on a form which has many items populated from a query. Listbox property is set to mulitselect. As I make multiple selections in the listbox I would like to retain those selections so they are highlighted when I go back to that/those records? Please help - samples if possible since I am new to Access and have been struggling with this.
Thanks,
Steven
0
Comment
Question by:Sherkanowski
23 Comments
 
LVL 2

Expert Comment

by:ssilvi84
ID: 13753873
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
0
 

Author Comment

by:Sherkanowski
ID: 13754003
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.
0
 
LVL 2

Expert Comment

by:ssilvi84
ID: 13754049
Actually I think I can make it even easier for you.. give me 5 minutes to work on it..
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Expert Comment

by:ssilvi84
ID: 13754244
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...
0
 
LVL 2

Expert Comment

by:ssilvi84
ID: 13754486
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...
0
 
LVL 58

Expert Comment

by:harfang
ID: 13754742
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
0
 
LVL 2

Expert Comment

by:ssilvi84
ID: 13755258
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..
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 13755408
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
0
 
LVL 2

Expert Comment

by:ssilvi84
ID: 13756276
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
0
 

Author Comment

by:Sherkanowski
ID: 13756637
ssilvi84 - could you email your sample code?
0
 
LVL 2

Expert Comment

by:ssilvi84
ID: 13756732
yeah will do
0
 
LVL 2

Expert Comment

by:ssilvi84
ID: 13768442
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?
0
 

Author Comment

by:Sherkanowski
ID: 13768497
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.
0
 
LVL 2

Expert Comment

by:ssilvi84
ID: 13768894
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.
0
 

Author Comment

by:Sherkanowski
ID: 13773079
That is exactly what I need, and then to retrieve and highlight those selections when I go back to that record. Thanks!
0
 
LVL 2

Expert Comment

by:ssilvi84
ID: 13773316
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...
0
 
LVL 2

Expert Comment

by:ssilvi84
ID: 13773326
In addition.. are your buttons on your subform or your main form?
0
 

Author Comment

by:Sherkanowski
ID: 13774080
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.
0
 
LVL 2

Expert Comment

by:ssilvi84
ID: 13774282
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..
0
 
LVL 2

Accepted Solution

by:
ssilvi84 earned 2000 total points
ID: 13774365
Okay I'm done :) I'll re-email you Dbase so you can take a look at it... i'm pretty happy with it!
0
 
LVL 2

Expert Comment

by:ssilvi84
ID: 13774433
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.
0
 

Author Comment

by:Sherkanowski
ID: 13774653
Got your sample, thanks. I will try to modify to get my db working using this as a baseline sample.
0
 
LVL 2

Expert Comment

by:ssilvi84
ID: 13775331
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:

http://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...
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question