Enter or Update Child Record?

[mkling]

I have a Parent Record "Events" which has data already entered into it. I now want to assign workers to work the "event". The child table is "Assigned Officer". They have a one (event) to many(employees) relationship. Both the parent and the child have the key "Event ID". Of course Event ID is a subkey in the employee's table.



In a nut shell, Everytime I enter an employee it creates a new blank event.

my query looks like:

UPDATE Event INNER JOIN [Assigned Officer] ON Event.[Event ID] = [Assigned Officer].[Event ID] SET [Assigned Officer].[Name Assigned] = [Assigned Officer].[forms]![Assigned Officer Subform].[name assigned], [Assigned Officer].[Employee ID] = [Assigned Officer].[forms]![Assigned Officer Subform].[Employee ID];

Greatly appreciate any suggestions. Thank you.

[John_G]

Hi -

Can one employee be assigned to many events (at the same time) , and is there another "Employees" table?

John

[mkling]

Hi -

Can one employee be assigned to many events (at the same time) , and is there another "Employees" table?

John

No. They can only be assigned to one event at a time. Maybe I should rethink that? The employees come from an excel spreadsheet where the salary, etc.. is kept and updated by a secretary so that is constantly changing. I use the spreadsheet to see all of the available employees via combo box then I assign the employee name and the employee number to two fields on the form.

What I was also hoping to do is that once an employee is selected, if another is needed thast selected employee would not show up. I need to set a flag for that but didn't want to get into it just yet. That will be my next thread. LOL

[John_G]

Hi -

When you say you assign the employee name and number to two fields on the form - what table are these bound to, if any?

It seems to me that when you select an employee to work on a project, you should not be doing an UPDATE on existing data, because there shouldn't be any to update yet; rather you should be doing an INSERT into the [Assigned Officer] table, something like:

Insert into [Assigned Officer] fields (field list) values (value list). Field list will have Event_ID, [Name assigned] , [employee ID], and any other data you need.

I don't think you want to start updating the Excel spreadsheet; so in order to find out which employees are available (the combo box list), you would have to look at the Excel list, but exclude any employees already assigned, which you can find by looking at the [Assigned Officer] table.

Written more-or-less in air-code, the select statement for your combo box might look something like this:

Select field list from Excel table where Excel Table.Employee_ID not in (select distinct Employee_ID from [Assigned Officer]

It is a bit more complicated than that, and I can give you more info later if you need it. I have to go now or I'll miss the last bus, and it's a L-O-N-G walk!

John

[mkling]

The two fields on the form are: [assigned officer].[employee id] and [assigned officer].[name assigned]. both get populated from the unbound combo box which draws from the spreadsheet.

When I select an event at the top of the form and select an employee from the subform and try to enter it(using the "new record * at the bottom of the subform), I get the error message:

"The Microsoft Database Access Engine cannot find a record in the table 'Event' with key matching field(s) 'Event ID'.

However, when I look at the table 'Event', I clearly see a child table with the matching Event ID number.

[John_G]

I think your probelm is with the recource of the form and subform - it should not be the multi-table query you described earlier. Set the source for the main form to [Events], and the source for the Sub-form to [Assigned officer]. Link the Main form and Sub-form using Event_ID.

That should work for you.

John

[mkling]

I think your probelm is with the recource of the form and subform - it should not be the multi-table query you described earlier. Set the source for the main form to [Events], and the source for the Sub-form to [Assigned officer]. Link the Main form and Sub-form using Event_ID.

That should work for you.

John

Thank you for your help. I basically unbound the fields in the subform, removed the child-parent link and just have a separate form for assigning the employees based upon an existing event. With the form unbound, the error message is gone and I use a variable for each fieled and insert the variable into the table. Probably not the best way to accomplish this but it works.

[mkling]

John_G,

Would you please take a look at the following thread?
I tried using your suggested code and it failed.

Thread: If Employee Selected Do Not Allow Employee to be Shown in Drop Down Again