Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Populate values from previously selected list box values on another form

Posted on 2006-11-03
22
Medium Priority
?
223 Views
Last Modified: 2009-12-16
I have multiple list boxes where people can select one to many fields that correspond to a record.  

These selections in the list boxes are moved to another table where they are referenced with the record.

The problem I have is when I go back into that record either on the same form or another the list box is cleared.

I have had to create subforms within my forms to display the data and allow it to be updated.  

Is there a way to have the selections on my list boxes populated when a user opens a particular record without creating subforms?  

Below is an example of one of my list boxes code to send it to a table.


Private Sub cmdOK_Click()
Dim rsTrain As DAO.Recordset
Dim varTrain As Variant
Set rsTrain = CurrentDb.OpenRecordset("tblTrainingEmployee", dbOpenDynaset)
With rsTrain
For Each varTrain In Me.lstTraining.ItemsSelected
    .AddNew
    !TrainingType = Me.lstTraining.ItemData(varTrain)
    !EMP_ID = Me.EMP_ID
    .Update
Next
End With
End Sub
0
Comment
Question by:gfortuna
  • 8
  • 4
  • 2
  • +4
22 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 17869393
After you have populated the listboxes you need to save the form.  Try before Dim lines and insert:

Docmd.Save acForm "myFormName"

replace myFormName with the actual form name.

0
 

Author Comment

by:gfortuna
ID: 17869718
That didn't work.  I select the items then run the command. After I close the form and open it back up the selections are gone.

Thanks.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17870262
How do you populate your list boxes now.  How many forms are they on?
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.

 

Author Comment

by:gfortuna
ID: 17870606
The list boxes are populated from various queries.  All but one is on one form.  There are a total of five list boxes on one of my forms.  They are all multiselect boxes.  

Right now if someone wants to go back and edit the data that they selected, I had to create another form with subforms for all the list boxes.  I want them to be able to go back into the same form and have the selections that were previously chosen to be there.  

Do I need to use something else besides list boxes? Maybe check boxes?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17870639
Strange.  I opened a list box albeit with a populated control, and with multiselect set to simple or extended, I can make row selections at will, change forms, do other things, and the form does not lose the records selected.  I was thinking if you saved the form after it was populated, you would save the contents of the listbox(es).  Can you show us one of the queries and how you use it to populate a box?
0
 

Author Comment

by:gfortuna
ID: 17870677
I'm obviously doing something wrong.  When I close the form after selecting items from my list box the selections are lost when I go back in.

SELECT tblSMELead.ID, tblSMELead.Name
FROM tblSMELead
ORDER BY tblSMELead.Name;


This is attached to the Ok cmd on my form to transfer the data to another table.

Set rsSME = CurrentDb.OpenRecordset("tblSMEList", dbOpenDynaset)
With rsSME
For Each varSME In Me.lstSME.ItemsSelected
    .AddNew
    !SMElead = Me.lstSME.ItemData(varSME)
    !DocumentNumber = Me.DocumentNumber
    .Update
Next
End With
0
 
LVL 4

Expert Comment

by:GordonPrince
ID: 17872828
To go back to basics, are the multiple users running the same copy of the Access code? Could the last person closing it be saving it and therefore you're losing your values? Or are you the only one running the code in a development environment?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17874112
Going back to what I said at the beginning, if the list box is empty on loading, then you fill it with code and queries, and then close it without saving, you will lose your entries.
0
 

Author Comment

by:gfortuna
ID: 17882672
I tried all the above.  It's not working.  Obsiouly I'm doing something wrong.  It drops the data every time.  Does it matter that these list boxes are from other tables?  Maybe it has something to do with my relationships.  

I don't understand why it's doing what it's doing.  It looks like I've designed something wrong on my end.  I'll try to figure it out.  I'll keep this question open for a bit to see if anyone might have ran into this before.  

Thanks for your help.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17882762
unfortunately, you can only make changes to the form and save the changes in design view.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 18263504
I believe the question was answered.   I just used one of my forms with a list box and cleared the row source, set the rowsource type to Values and saved the form.  When I opened the form the listbox was empty as expected. I set the no of columns to 2 bound on 1, widths, 0.3";1.0". Then I went to the Immediate Pane and entered the line:

Forms!Form!lbxAny.RowSource="1;""GRayL"""

I went back to the list box and all was as it should be - populated as I has coded.  I then saved the form, closed it, re-opened it, and there was my listbox - populated.  I think capricorn1 was wrong when he said you can only make changes in design view.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 18264399
Ray,

>I want them to be able to go back into the same form and have the selections that were previously chosen to be there.  
>When I close the form after selecting items from my list box the selections are lost when I go back in.

I don't think the author is concerned with the rowsource that populates the listbox, but with retaining the selections a user has made in a multiselect list box, taking the data that has been saved through the OK button and showing those as selected items in the listbox.  
0
 
LVL 44

Expert Comment

by:GRayL
ID: 18270212
Thanks, Miriam, now I understand.  I'm running A2K, I made several selections of a listbox, (having preset the listbox property multiselect to Extended), run the same code, open another form, do several things, go back to the form with the listbox, and the selections are still all there.  I cannot duplicate the problem.

I suggest a Compact and Repair.  If that does not resolve anything, open a new mdb, and copy all the old objects into the new mdb using Files - Get External Data - Import - point to the old mdb, and import everthing.  See if the problem exists in the new mdb.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 18270248
gfortuna:  Can you take a moment to re-describe the problem?
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 2000 total points
ID: 18271728
Hi all.
Just passing by...
I may be missing something (and as Ray says - perhaps gfortuna could do with re-stating the issue step by step so as to be sure...)

It seems to me we're dealing with unbound listboxes (bound multiselect listboxes are essentially meaningless).
The user selects the multiple entries - those values are persisted to a table.
The user leaves that record - or closes that form.  (<---The pivotal point here and the core of my supposition).
Returning to that form the listboxes show nothing.  (Naturally)
(They're always going to behave this way between records or sessions of the form - the data from them was only persisted to another table by code - they can only display those selections again by running more code to reset the values).

The reverse equivalent would be something like...

Private Sub cmdLoad_Click()

Dim rsTrain As DAO.Recordset
Dim varTrain As Variant
Dim intRows As Integer

Set rsTrain = CurrentDb.OpenRecordset("SELECT * FROM tblTrainingEmployee WHERE EMP_ID = " & Me.EMP_ID, dbOpenSnapshot)
With rsTrain
    Do Until .EOF
        For intRows = 0 To lstTraining.ListCount - 1
            If Me.lstTraining.ItemData(intRows) = !TrainingType Then
                Me.lstTraining.Selected(intRows) = True
                Exit For
            End If
        Next
        .MoveNext
    Loop
End With

End Sub
0
 

Expert Comment

by:42na
ID: 18272749
Hi LPurvis,

I changed user names from gfortuna to 42na.  

I think you are on to something.  I gave up on this question thinking I was doing something so wrong that no one could explain.  

My multiselection boxes are transferred to another table when the user selects OK.  When you go back to the initial form the entries are gone.  I'll try that code when I back to work tomorrow.  

I have a question in to support to see how I can still assign points.

Thanks for you help!
0
 
LVL 44

Expert Comment

by:GRayL
ID: 18278658
42na:  >My multiselection boxes are transferred to another table when the user selects OK<  You mean the data in the bound field of the listbox is stored in another table?  So long as you do not close the form or 'deselect' the row of the listbox programatically, you can move from form to form without losing the listbox selections.  Maybe I still don't understand the problem.  Please clarify.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 18283325
Changed recommendation
Please do not delete
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

963 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