Solved

How to preserve values selected in a list box?

Posted on 2015-02-02
11
64 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
[X]
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
  • 5
  • 3
11 Comments
 
LVL 85
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 48

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 85

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 85
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

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.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

688 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