Save new entry on form to other tables (1 Viewer)

statsman

Active member
Local time
Today, 10:54
Joined
Aug 22, 2004
Messages
2,088
I have an Access 03 database I am assisting a friend with.

The tables concerned are EmployeeID, Session1, Session2, Session3, Session4 and Exam. All are connected by the field EmployeeNo. It concerns mandatory training in the workplace. Each employee MUST attend all four sessions and write the exam at the end.

What I would like to do is that when I enter the EmployeeNo via a form in the table EmployeeID it will automatically be appended into the other 5 tables. I have created a query with all 6 tables with the relation set to EmpolyeeNo, but I can't get it to do the append.

All suggestions gratefully accepted.
 

Atomic Shrimp

Humanoid lifeform
Local time
Today, 14:54
Joined
Jun 16, 2000
Messages
1,954
Are you sure you want to do it that way? Creating empty records in readiness for population at some future date is often not a really great idea, and shouldn't really be necessary - because you can create the same view by means of a find unmatched query.

If you're sure you want to do it, isn't it just a case of running a bit of SQL on one of the update events for the employee form? - something like DoCmd.RunSQL("INSERT INTO Session1 ( EmployeeID ) SELECT " & Me.EmployeeIDField & " AS Expr1;"

But... perhaps a better question would be: Why are there all these different tables? Sounds like a normalization issue.
 

statsman

Active member
Local time
Today, 10:54
Joined
Aug 22, 2004
Messages
2,088
A.S.

Shortly after posting the above I gave the entire matter a re-think and have decided to advise my friend to go in a different direction.

Although I liked the concept of what he was attempting to do, the normalization and other issues make it more trouble than its worth.

Thanks for your input.
 

Users who are viewing this thread

Top Bottom