Solved

Populate values from previously selected list box values on another form

Posted on 2006-11-03
22
213 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
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 119

Expert Comment

by:Rey Obrero
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 500 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

18 Experts available now in Live!

Get 1:1 Help Now