Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to preserve values selected in a list box?

Posted on 2015-02-02
11
62 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 4

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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
 
LVL 4

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 4

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 4

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 4

Author Closing Comment

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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
File.Search issue 8 32
Dinamic report to Crosstab query 9 33
set focus to tabbed sub form 10 39
How do I bind a table in Access 2013 to a form 5 32
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…

856 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