Table was designed wrong

[rhernand]

I have this Access application. The Incidents table was created incorrectly. apparently the design was not thought over. The Incidents table was created to store employee accidents. However, since the employee data is based in nicely normalized data, all Incident reports will have the employee's PRESENT data, instead of the Department, Cost Center, Location, etc., that the employee was at the time of the incident. I want to de-normalize the Incidents table and add the Department, Cost Center, Location, etc., that belong to the employee at the time of the incident, I have added the columns to the table, but I am having problems getting the data from the normalized tables to the Incidents table in the Click event of the AddIncident command button. I have tried to create queries, but can not get them to work. I was unable to send the mdb, but attach is the AddIncident event. In the Fields statement, I want to get a field data from another table. For example, the present Employees table, which always has the employees present info, contains the AutoNumber Key to the rest of the normalized tables, Department, Cost Center, Location, etc; I want to get the actual Department name, Cost Center number, etc, and put it in the Incidents table. The Employees table has the data I want for the time of the incident. But the employee may change Department, Cost Center, Location, etc, before his next incident.

 

[neileg]

I would:
1) Create a combo based on the Employee table to select the relevant employee. In the query used to drive the combo, I would include all the data you want to store, Department, Cost Center, Location, etc. Include these as hidden columns in the combo.
2) Crete controls on your form, text boxes probably, that are bound to the fields in the Incident table.
3) In the AfterUpdate event of the combo, set the values of your controls to the relevant column values of the combo. E.g. Me.TxtDepartment=Me.cboEmployee.Column(4)

 

[rhernand]

Still having problems

The (1) combo based on the Employee table to select the relevant employee is already there. In the query used to drive the combo, I have included all the data I want to store, EmployeeNumber, Department, Cost Center, Location, etc. and made these as hidden columns in the combo.
I (2) have created controls on the form, text boxes, that are bound to the fields in the Incident table. In the (3) AfterUpdate event of the combo, I set the values of the controls to the relevant column values of the combo. E.g. Me.TxtEmpNum=Me.cboEmployee.Column(3)

However, I get a Compile error: Method or data member not found on Me.TxtEmpNum=Me.cboEmployee.Column(3). When I run the query, the EmployeeNumber is on Column(3).

 

[rhernand]

Why are the text boxes bound to the fields in the Incident table? The insert of the Employee table data to the Incidents table is done with a Command button with a .AddNew on the Click Event. Can I just add a
.Fields ("EmployeeNumber") = cboxEmployee.Column(3) to the event. (did not work) If I could only populate the text boxes, I could add a
.Fields ("EmployeeNumber") = txtEmpNum. But I get a Compile error

 

[neileg]

However, I get a Compile error: Method or data member not found on Me.TxtEmpNum=Me.cboEmployee.Column(3). When I run the query, the EmployeeNumber is on Column(3).

I would have expected the combo to be bound to the EmployeeNumber field anyway, so I'm confused. Column numbering starts from 0, so the third column is actually .Column(2)

Why are the text boxes bound to the fields in the Incident table?

If these are bound, then populating the text box also populated the field so your code is not needed.