PeterDuthie
Programmer
Please excuse me if I'm being dense here. This is the problem:
Personnel database which is used by various departments each with a table and form recording relevant data. The Main "Workforce" table generates an autonumber which is then used as that person's "ID" and is the main key in the "Workforce" table. How do I get the ID from the "Workforce" table into the other departmental tables - e.g. the "EnvironmentalInduction" table?
At the moment my solution and an SQL query along the lines of:
Dim sqlStr As String
sqlStr = "INSERT INTO [Enviro data] SELECT Workforce.ID AS ID "
sqlStr = sqlStr + "FROM Workforce WHERE NOT EXISTS "
sqlStr = sqlStr + "(Select * from [Enviro data] WHERE "
sqlStr = sqlStr + "[Enviro data].ID = [Workforce].ID);"
DoCmd.RunSQL sqlStr
Which I have in the OnLoad event of the "Enviro data" form. I think I may have missed something. Any help gratefully appreciated.
Cheers
Peter
Personnel database which is used by various departments each with a table and form recording relevant data. The Main "Workforce" table generates an autonumber which is then used as that person's "ID" and is the main key in the "Workforce" table. How do I get the ID from the "Workforce" table into the other departmental tables - e.g. the "EnvironmentalInduction" table?
At the moment my solution and an SQL query along the lines of:
Dim sqlStr As String
sqlStr = "INSERT INTO [Enviro data] SELECT Workforce.ID AS ID "
sqlStr = sqlStr + "FROM Workforce WHERE NOT EXISTS "
sqlStr = sqlStr + "(Select * from [Enviro data] WHERE "
sqlStr = sqlStr + "[Enviro data].ID = [Workforce].ID);"
DoCmd.RunSQL sqlStr
Which I have in the OnLoad event of the "Enviro data" form. I think I may have missed something. Any help gratefully appreciated.
Cheers
Peter