Before Update insert new record of what changes have made in master form

[waqas]

Dear experts,

i have master detail form and i want to insert a new record in detail form which user make change in master form.

like an employee has designation Driver from 01-01-07 to 31-01-09 in detail form now if user change his designation in master form it should insert a new record in detail form with new position.



how can i do?

[June7]

Why duplicate data? User can just enter record in subform. There is no reason to have this data in the 'master'. Use query to retrieve the latest detail records for employees.

[waqas]

how can i know which records are latest.

[June7]

One way:

SELECT * FROM detailtablename WHERE datefield=DMax("datefield","tablename","EmployeeID =" & [EmployeeID]);

I can't be more specific without knowing your data structure.

[waqas]

thanks june7.
now i tell u in details i have two tables one master tables in which i have full details of employees change able values like Designation, grade, section, joining date, last date and other non change able info like date of birth, name, nationality etc.. from master table i get total head count information in (designation, grade, section ) vise as required. in detail table i insert records as when an employee got promotion, grade change, section change and demotion each entry is consist on date period and this date period is effect his daily attendance in his daily attendance we need also (designation, grade, section ) information on daily bases. therefore i have to change in master list and insert a new record of these changes in detail table.

i think now you will get my point.

[June7]

Oh rats! Time&Attendance database is a hard one. Never built one but seen enough threads about them.

You have an 'appointments' table that holds history of employee promotion, grade change, section change and demotion? Then why not save the ID of the latest action in the daily attendance records? I still don't see need for the duplication into 'master' table.

If you want to add a new 'appointment' record on-the-fly while entering daily attendance, that is a different issue.

[waqas]

yes your are right i have all tables for attendance, overtime calculation, clocking data, duty timings. i am not inserting duplicate record in master table i just update current record like change designation Driver to Clerk i change it in master table now he will appear with new designation i can't know what his previous designation for that i insert a new record manually in detail table like " Driver - 01-01-12 to 31-12-12 " now " Clerk - 01-01-13 to 31-12-14 " then these designation and date i update in attendance table on daily bases. now i want if i change any entry in master form it should insert as new record in detail table. thanks

[June7]

I know you are not duplicating record in master table but you are duplicating data. There is no need to continually modify master record to reflect the latest designation. The detail table has that info so there is no need for those fields in the master table.