Copying newly entered record back into form?

[eskybel]

Hello all,

This might be a real simple fix, and I'm just missing it, but here's what I'm looking to do:

I have a form, where all necessary data is filled out for a specific project. This form is tied to a table that holds all the information, with a couple controls on the form pointing to a couple different tables, such as tblEmployeeList and tblCustomerList. At the end, the user can either go to the next record, enter data on the form again, or close the form. The part I'm stuck on is in regards to the employee field on the form, which lists the employee involved in the "project" for which the form is filled out.

Right now, the form gets filled out for this specific project, and only one employee can be entered at a time. The problem is a single project can be worked on by up to 10 employees. Right now, they fill the form, enter one employee's name, and the form clears, and they have to retype it. This works, but it is inefficient.

I also have another version of the main table and form has 10 combo boxes where they could select the different employee's names in each, and these would go into their own fields in the single record for the project. The problem with this came when trying to run a query to ask "which projects has [this] employee worked on?" and it would be done with a "like [Employee name?]" in the criteria for each field, and asks for the name 10 times. Obviously this is also a problem.

It seems it might be easiest to go with the database that has the one employee entry per form, then have a way to duplicate the info on the form, change the employee name, and have it create another record, exactly the same, except the employee name, but this creates a lot of records. If there is a good way to query 10 fields (emp1-10) in each record, and have it only ask for the employee's name once, then spit out the report based on your entry for the query, but I can't seem to get that working either (always getting a blank report).

Again, I'm probably missing something easy.

If I haven't explained it well, or am missing some key information, please let me know.

Thank you

[Missinglinq]

I got kind of dizzy, trying to follow the ins and outs of your explanation, but if you want to enter Multiple Records, one following the other, where the only difference in each Record is the Employee's name, you can use the AfterUpdate event of the Control holding your data to set the DefaultValue for the Field. From that time forward, until you either manually change the data or close your form, the data will be entered automatically in each New Record.

Private Sub YourControlName_AfterUpdate()
   Me.YourControlName.DefaultValue = """" & Me.YourControlName.Value & """"
End Sub

You’ll need to do this for each Control that you want to ‘carry forward.’

Note that this code/syntax can be used for any Datatype.

Another approach for this kind of thing would be to have a separate ProjectEmployees Table, related to the Projects Table by a ProjectID Field, that’s present in the Projects Table as the Primary Key and in the ProjectEmployees Table as a Foreign Key. You’d then create a Form for each Table, with all the project info in the Projects Table/Form, and have the ProjectEmployees Form displayed as a Subform on the Main, i.e. Projects Form.

Linq ;0)>

[eskybel]

Another approach for this kind of thing would be to have a separate ProjectEmployees Table, related to the Projects Table by a ProjectID Field, that’s present in the Projects Table as the Primary Key and in the ProjectEmployees Table as a Foreign Key. You’d then create a Form for each Table, with all the project info in the Projects Table/Form, and have the ProjectEmployees Form displayed as a Subform on the Main, i.e. Projects Form.

Linq ;0)>

Sorry for the spaghetti plate of a description.
This sounds like a good idea. I'll run with this for a bit and see if I can get it working properly, and report back when (I'm sure) I have another question.

Thanks for the help!

[eskybel]

I found an action for a command button that duplicates the record. They have the form, they fill it out, they enter the first employee name, then they hit the button "add employee to project" and it duplicates the record, and they change the name in the drop down, then they close when they're done. It works well enough for me.
Thanks!