UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How Recall Previous Listbox Selections, Access 2016    
 
   
SteveL5231
post Jan 22 2020, 02:08 PM
Post#1



Posts: 16
Joined: 31-December 05



I'm trying to figure out how to recall previously selected (multi-select listbox) items on a form when the form is re-opened. Can some offer help?
Go to the top of the page
 
Larry Larsen
post Jan 22 2020, 02:16 PM
Post#2


UA Editor + Utterly Certified
Posts: 24,444
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi

Tell us a little bit more about the listbox and what's it source...
(query/table/etc..)

thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
MadPiet
post Jan 22 2020, 02:29 PM
Post#3



Posts: 3,472
Joined: 27-February 09



You'd have to store it somewhere... In MDB files you could stuff the value into one of the database's custom properties. Or you could put it in a table...
Go to the top of the page
 
SteveL5231
post Jan 22 2020, 04:11 PM
Post#4



Posts: 16
Joined: 31-December 05



The source is a table.
Go to the top of the page
 
SteveL5231
post Jan 22 2020, 04:12 PM
Post#5



Posts: 16
Joined: 31-December 05



Storing the selected values in a table may be the solution.
Go to the top of the page
 
SteveL5231
post Jan 22 2020, 04:25 PM
Post#6



Posts: 16
Joined: 31-December 05



I'm trying to do it with this query but it isn't working...

CODE
INSERT INTO tblListBoxSelections ( EmployeeID )
SELECT Employees.EmployeeID
FROM Employees
WHERE (((Employees.EmployeeID)=[Forms]![frmMainMenu]![NamesList].[ItemsSelected]));
Go to the top of the page
 
fkegley
post Jan 22 2020, 04:26 PM
Post#7


UtterAccess VIP
Posts: 23,779
Joined: 13-January 05
From: Mississippi


You'll need to store the bound column values of each selected row somewhere. Let's say that is in a cell in a table. The bound column values would need some kind of separator between them so you could tell them apart. You might also need a way to tie a particular row in this table to the table that contains the form which contains the list box.

Then when the form is opened, root through the table containing the bound column values for the particular row of interest, then compare each value in that row's bound column cell to a bound column value in the list box. When you find a match, set the selected value of that column in the list box to true. You would have to do this for the first row when the form is opened, and if navigating through a bunch of rows for each row to which the user navigates.


--------------------
Frank Kegley, Microsoft Access 2010 MVP
Go to the top of the page
 
fkegley
post Jan 22 2020, 04:30 PM
Post#8


UtterAccess VIP
Posts: 23,779
Joined: 13-January 05
From: Mississippi


That is not going to work. ItemsSelected is a collection. You must use iteration to get at its items. It will take code to do this.




--------------------
Frank Kegley, Microsoft Access 2010 MVP
Go to the top of the page
 
SteveL5231
post Jan 22 2020, 04:37 PM
Post#9



Posts: 16
Joined: 31-December 05



I'm lost and don't know where to begin. I even Googled to try to find examples and couldn't find any. Am I the only one that has tried to do this?
Go to the top of the page
 
fkegley
post Jan 22 2020, 04:44 PM
Post#10


UtterAccess VIP
Posts: 23,779
Joined: 13-January 05
From: Mississippi


Here's a pretty good example of what you have to do.

ListBoxIteration

--------------------
Frank Kegley, Microsoft Access 2010 MVP
Go to the top of the page
 
ADezii
post Jan 22 2020, 04:47 PM
Post#11



Posts: 2,760
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. I like the idea of saving previously selected Items from a ListBox to a Table for future Restore. Let's assume you have a ListBox named lstDepts with 2 Columns and its' MultiSelect Property set to either Simple or Extended. The following Code will write your ListBox Selections to a Table named tblSelected which consists of 2 Fields, namely: [ID] and [Selected]. Hope this helps.
    CODE
    Dim lst As Access.ListBox
    Dim varItem As Variant

    Set lst = Me![lstDepts]     'Set Reference to lstDepts

    'DELETE Records in tblSelected
    CurrentDb.Execute "DELETE * FROM tblSelected", dbFailOnError

    With lst
      If .ItemsSelected.Count = 0 Then Exit Sub     'Exit if nothing was selected
      
      For Each varItem In .ItemsSelected        'Iterate selections & Append to tblSelected
        CurrentDb.Execute "INSERT INTO tblSelected ([ID],[Selected]) VALUES (" & _
                          .Column(0, varItem) & ",'" & .Column(1, varItem) & "')", _
                           dbFailOnError
      Next varItem
    End With
  2. You can re-populate the ListBox from tblSelected at any time.
  3. Hope this helps.

P.S. - Sorry MadPiet, didn't mean to step on your toes! shrug.gif
This post has been edited by ADezii: Jan 22 2020, 04:51 PM
Go to the top of the page
 
MadPiet
post Jan 22 2020, 04:47 PM
Post#12



Posts: 3,472
Joined: 27-February 09



You can do it like this:

CODE
    Dim varItem As Variant
    Dim strValue As String
    Dim strInsertSQL As String
    
    
    For Each varItem In Me.List0.ItemsSelected
        strValue = Me.List0.ItemData(varItem)
        strInsertSQL = "INSERT INTO tblSelections(PutValueHere) VALUES ('" & strValue & "');"
        CurrentDb.Execute strInsertSQL, dbFailOnError
    Next varItem

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st February 2020 - 10:36 AM