Solved

How to preserve values selected in a list box?

Posted on 2015-02-02
11
59 Views
Last Modified: 2016-07-10
Hi Experts,
I am having a subform that changes from visible to invisible and vise versa, now all unbounded controls that contained data are still having them, except for multi select list boxes, is there an easy way to make that happen?
0
Comment
Question by:bfuchs
  • 5
  • 3
11 Comments
 
LVL 84
ID: 40585272
Are you saying the DATA in those listboxes is not there, or the SELECTIONS?

If it's the Selections, then you'd have to store those values and then refresh the listbox.

If it's the DAta, then you've got troubles with your database, or with your install of Access or Windows.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40585290
Personally, I've gotten to the point where I prefer to use either a datasheet or a continuous subforms instead of listboxes.  You can configure these to look like a listbox, but get the advantage of being able to use a Yes/No field to indicate whether the record is selected.

If you use the listbox, as Scott mentioned, you will need to save the values associated with the selected records.  You can use code similar to the following to loop through the list of selected records.
    Dim varItem As Variant
    For Each varItem In Me.yourListboxName.ItemsSelected
        Debug.Print Me.yourListboxName.Column(0, varItem)
    Next

Open in new window

Instead of the debug.print, you would need to store the value of the primary key associated with each of these selected items.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40585384
Hi Experts,

I meant the selections.

So what you're saying is that for some reason the values disappear...and I must save it first before it gets invisible.

Would it make sense to have it done on the click event of the list box?

and how can I assign back all those selections when form gets visible again?

I am using this subform as a general filter screen, therefore would like to place the code to re-select those values originally selected within the module of the subform, instead of placing it in the code that makes the subform visible, where can it be done?

Thanks,
Ben
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40585934
Yes, that's what we're saying.

You could create a form-level variable, and store the selections in that. I'd do this just before you toggle the subform to be invisible. The code Dale showed could be used for that, and you could store them in a comma-delimited string (like 1,3, 15, 33)

To re-select them, you'd just loop through selections, and then loop through the listbox items and select them:

Dim i As Integer
For i = 1 to UBound(YourVariable)-1
  For j = 0 to YourListBox.ListCount-1
    If YourListbox.Columns(0) = YourVarable(i) Then
      YourListbox.Selected(i) = True
    End If
  Next j
Next i

Note that "YourVariable" is the comma-delimited variable you filled before you toggled the subform invisible. Also, you'd have to make sure you're looking at the correct column in the listbox, and remember that collection is zero-based, so the first column is 0, the second is 1, etc.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 3

Author Comment

by:bfuchs
ID: 40587454
@Scott,

My problem is as following, the list box is in a subform that is being toggled visible-invisible by a command button on the main form, therefore I am not sure where will the subform get to know that it got visible..?

Re your code above, just had a question, if I use the Comma-delimited string then I would need to use split in order to get it converted to an array, only then can I use Ubound, so actually I could stored it in an array at first place, correct?

Thanks,
Ben
0
 
LVL 84
ID: 40590926
Yes, you could store it in an array to start if you'd prefer.

I am not sure where will the subform get to know that it got visible.
If you have a command button to toggle the visible property, then you could include code in that event to manage the listbox.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 40592416
@Scott,

The reason I would like to code it in the subform is, that this subform is being used in various places on the app, and would require having to write/maintain code in multiple places, let me know if there is any possibility I can have this done in the sub?

Thanks,
Ben
0
 
LVL 3

Author Comment

by:bfuchs
ID: 41702133
Hi,

Perhaps I would rather accept Scott's answer (ID: 40585934) than have this question being deleted.

Thanks,
Ben
0
 
LVL 3

Author Closing Comment

by:bfuchs
ID: 41702267
Thanks to all participants!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now