gfortuna
asked on
Populate values from previously selected list box values on another form
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("t
With rsTrain
For Each varTrain In Me.lstTraining.ItemsSelect
.AddNew
!TrainingType = Me.lstTraining.ItemData(va
!EMP_ID = Me.EMP_ID
.Update
Next
End With
End Sub
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("t
With rsTrain
For Each varTrain In Me.lstTraining.ItemsSelect
.AddNew
!TrainingType = Me.lstTraining.ItemData(va
!EMP_ID = Me.EMP_ID
.Update
Next
End With
End Sub
ASKER
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.
Thanks.
How do you populate your list boxes now. How many forms are they on?
ASKER
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?
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?
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?
ASKER
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("t
With rsSME
For Each varSME In Me.lstSME.ItemsSelected
.AddNew
!SMElead = Me.lstSME.ItemData(varSME)
!DocumentNumber = Me.DocumentNumber
.Update
Next
End With
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("t
With rsSME
For Each varSME In Me.lstSME.ItemsSelected
.AddNew
!SMElead = Me.lstSME.ItemData(varSME)
!DocumentNumber = Me.DocumentNumber
.Update
Next
End With
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?
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.
ASKER
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.
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.
unfortunately, you can only make changes to the form and save the changes in design view.
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.RowSourc
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.
Forms!Form!lbxAny.RowSourc
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.
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.
>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.
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.
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.
gfortuna: Can you take a moment to re-describe the problem?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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!
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.
Changed recommendation
Please do not delete
Please do not delete
Docmd.Save acForm "myFormName"
replace myFormName with the actual form name.