Save items from listbox selections to display on another form and save in table

[taya621]

Hello all,
I'm pretty new at Access and I cannot find a solution to my problem.


I'm building a project management database in which I have a form for entry of a new project. A user will enter their new project into this form and then come to a button called "assign members" which I have currently made to open another form called "assign members". Assign Members form has two listboxes in which they can double click on employees from the left to put into the right listbox to indicate that they want these people assigned to this project. The object is for them to click a save button so that the items in the right listbox will save to the record they were creating in the new project form and display to them who they chose once they return to the original form for entering a new project. Then, those members need to be saved to the projects table in a column called "assigned members". I can't even figure out how to save the items from the right list box SOMEWHERE, let alone display them on the new project form. Please help!!

[NoellaG]

Hi,

I suppose you have a list with the multiselect property set to true.
In this case you can use the ItemsSelected property to catch the selected values. The following code snip shows how you can print the selected values in the immediate pane:

    Dim varItem As Variant
    
    For Each varItem In Me.MyList.ItemsSelected
        Debug.Print Me.MyList.ItemData(varItem)
    Next varItem

Once you can catch the values you can write them to a table using a recordset object.

Kind regards
Noëlla

[taya621]

Hello Noella - thanks for your help!
I'm not sure ItemsSelect will work with the way I have my listboxes set up because the multiselect option is set to none. If it does work, then I'm not sure how to write the code. Can I attach my form and let me know how to do it?

I want the employees selected in "main" to be saved in T_Employees "Assigned members" column and also show on the "F_Projects" form in the "your assigned employees" section. What do you think?

Basically, it's more complicated because the items in the right listbox are not "selected" - they have been moved from the left listbox so I need something that will tell it that I want all the items in the right hand listbox to be saved with the creation of the project we were making in the first form. thanks!!

[NoellaG]

Hi,

I added a PK to every table and added a table to store who is assigned to which project, and then added the code to register this.
You'll find the database with the changes in the attached zip file.

Success
Noëlla

[taya621]

Noella,
Thanks so much for taking the time to help me out and I don't want to be a nuisance but I'm having some issues. When I click on the Assign Employees button and it pulls up fsformsprojectemployees, I choose my employees, click ok and I get the error message attached as png. This only happens when I am trying to create a new project entry. Also, the assigned members do not get saved to the Assigned Members column in the Projects Table. I would need it there because I am planning on creating reports that would show the project creator a summary of their projects, including the people they assigned to those projects. Please let me know if I can be more clear or if you need more information.
Thanks again1!!

[NoellaG]

Hi,

that's because I added referential integrity in the relationship between T_employees and T_projectemployees. make sure the new project is saved to the disc (push shift + enter or so) before adding employees.

greetings
NG

[taya621]

NoellaG,
Thanks again for your help!
Is there a way that if the user changed their mind, they could click again on to "Assign Employees" and remove some or all of the people they assigned? Right now, if you click on that again, it only shows the employees that weren't assigned at all and there is nothign in the right hand box.

Thanks!!

[taya621]

NoellaG,
Thanks again for your help!
Is there a way that if the user changed their mind, they could click again on to "Assign Employees" and remove some or all of the people they assigned? Right now, if you click on that again, it only shows the employees that weren't assigned at all and there is nothign in the right hand box.

Thanks!

[NoellaG]

Hi,

as the selected persons are added to the table T_ProjectsEmployees, they can be deleted from that table through the subform ProjectEmployees on the form Projects

[taya621]

Ok great thanks.
If your up for it I have a different problem in the same database:
I am trying to create a report that will list my projects + description, deadline, creator, etc. and also the list of assigned employees. I know I have to do some type of query in conjunction with a report but I can't figure out how. I keep getting #error in all the fields on my report...
Thank you in advance!!!