Set combobox value to other combobox

[bigsich]

So here's the situation. I've got a drop-down combobox in on split form where the employees whom a project is assigned to are selected (multiple employees can be selected). Upon completion of a project, an autohotkey script is triggered which creates and populates some post-project word doc. However, there needs to be some input from the employees who worked on the project. So I also have the script create a new project in a separate split form denoting this. (i.e. the pojects are in the projects form, wrap-up paper in the wrap-up form) One of the fields in the wrap-up form is employees, which i would like to match the employees field in the projects form for the record that was just selected as completed. While i can easily put the employees names into a textbox, this severely limits the sorting effectiveness and creates no meaningful data relationships.

I feel like there has to be a set the default value of the employees combobox in the wrap-up form to the value of the "parent" combobox, which is located in the "active" record in the projects form.

Any guidance is appreciated.

[June7]

The wrap-up form is an independent split form? What code opens the wrap-up form?

Perhaps in the wrap-up form Current event:
If IsNull(Me.EmployeeID) Then Me.EmployeeID = Forms!projectform!EmployeeID

Are you saving employee name or employee ID?

[bigsich]

The rap-up form is an independent split form. I've hidden a button on the projects form that the autohotkey script selects to open the wrap-up form. In the projects form, the employees field is using a look-up to pull employee names from the employees table. I feel as though i did a poor job explaining the situation, let me try again.

Ive got the wrap-up form, and projects form. In the projects form, when a user selects the project as completed, it triggers an external script to run. At the end of the script, the filenames of the documents that were just created by the script are copied into the wrap-up form as a new record. I want to "copy" the contents of the employees field for the current record from the projects form to the wrap-up form employees field. However, since it's a look-up field combobox in the projects from, I am not sure how to do this. Hopefully that clears up any confusion.

[June7]

I am not sure you need to save the EmployeeID. Need to know more about data structure. You have tables: Projects, Employees, Assignments, WrapUp ? Will there be multiple WrapUp records for each Assignment? Assignments has the EmployeeID? Save the AssignmentID to WrapUp or include the wrapup info in the Assignments table.

Is the combobox multi-column? Show the RowSource sql statement.

If you want to provide db for analysis, follow instructions at bottom of my post.

[bigsich]

I've stripped down the database to the minimum needed to demonstrate what I'm trying to do. In the projects splitform there is the drop-down combobox to select which employees are working on it. When the user selects completed in the classification drop-down, i have a macro set to run the external autohotkey script. The script creates the paperwork from the information currently in the fields for the project that was just selected as completed. At the end of the script, it opens the wrap-up split form, creates a new record, and enters in the document name. What I want to be able to do is have a drop-down commobox for the employees that sets itself to the same employees that the project had. The project i want the data to match to will still be the current record in the projects splitform.

Hopefully that makes more sense now that you can see the db. Please let me know if there is anything else i can do. I appreciate the help so far.
db_betav2.ZIP

[June7]

Never had a table trigger a parameter prompt. You have the wrong field name for Customer Abbr in the Lookup for Customer field in Projects table.

Advise no spaces in names, nor special characters and punctuation (underscore is exception).

Is WrapUp Doc ID field in WrapUp table supposed to be foreign key for the ProjectID value? WrapUp Doc ID has to be a number type so the tables could be joined in query. Otherwise, what is that field for and where do you want to save the ProjectID in the WrapUp record?

You are using multi-value fields. I never use multi-value fields. Employees field in projects is a multi-value. Do you want records in WrapUp for each employee associated with the project record? If you don't want individual records for each employee then there is no reason to have the Employees field in WrapUp. Just save the ProjectID. Review: http://office.microsoft.com/en-us/ac...001233722.aspx

I use only VBA:
CurrentDb.Execute "INSERT INTO Wrapup([WrapUp Doc ID], Employees) SELECT [Project ID], Employees.Value FROM Projects WHERE [Project ID]=" & Me.[Project ID];"

[bigsich]

Never had a table trigger a parameter prompt. You have the wrong field name for Customer Abbr in the Lookup for Customer field in Projects table.

Thanks for finding that, it has been corrected.

Advise no spaces in names, nor special characters and punctuation (underscore is exception).

Thanks for the advice, this is my first time programming a database. I'm self taught, so I'm constantly learning.

Is WrapUp Doc ID field in WrapUp table supposed to be foreign key for the ProjectID value? WrapUp Doc ID has to be a number type so the tables could be joined in query. Otherwise, what is that field for and where do you want to save the ProjectID in the WrapUp record?

WrapUp Doc ID is a pseudo foreign key in that it is not linked as a relationship, only in that the document name corresponds to the project. When the external script creates the wrap-up document, it pulls the primary key from the project (Project ID) and sets the wrap-up document name to WU-%Project ID%. So document WU-3 corresponds to project that has primary key 3. WrapUp ID is the primary key (auto assign number). I have no need to link the two tables with a database relationship; the WrapUp form is there as a standalone so that employees can check which paperwork they need to complete. As they complete it, they will un-select themselves from the combobox. Additionally, while I could set the field as a text type and copy and paste the contents from the projects employees field into the employees field for wrapup, the employees would not be able to use the sort feature to have db just show which documents they need to complete (as they are in the projects splitform by selecting their initials only). Hence, I'm trying to get the employees field in the wrap-up form to set itself to that of the corresponding project. Since I have a script creating the wrap-up document and record in the wrapup form, I know that the value I want in the wrapup form combobox is the value of the employees field for the currently "active" record in the projects table.

Do you want records in WrapUp for each employee associated with the project record?

No, I want one record with the wrap-up document name, and all the employees assigned to that document.

If you don't want individual records for each employee then there is no reason to have the Employees field in WrapUp. Just save the ProjectID.

The reason I want one record, with multiple employees is that it makes no sense to have the same wrap-up document listed and one employee assigned to it, as opposed one record with all employees assigned to it.

Is there some way for the source of the employees field in wrapup to be set such that when a new record is created it uses the value from the active record in the projects form, while keeping it as a multiple value combobox so that employees can un-select themselves as they complete the paperwork?

[June7]

Do you fully understand what a multi-value field is and how the data is stored? Programmatically copying the data from a multi-value field to another multi-value field is not simple.

Google: access vba copy multi-value field data to another table
Review
http://answers.microsoft.com/en-us/o...0-64413513c815
http://stackoverflow.com/questions/1...rd-access-2007
http://www.access-programmers.co.uk/...d.php?t=204915

Can actually copy/paste the field but apparently SQL INSERT and UPDATE actions don't work with multi-value fields, hence the above code examples that open recordsets.