Automatically create record in related table

  • Thread starter Thread starter Jonathan Brown
  • Start date Start date
J

Jonathan Brown

I have a one to many relationship between an employees table and a
sitehistory table. What I'd like to do is, whenever I create a new record in
my employees table, automatically create an associated record in the
sitehistory table with site number 9 as the default site and Date() as the
default date.

EmployeesTable
Empnum <--Primary Key
FirstName
LastName
....

SiteHistoryTable
SHistoryNum <--auto-increment primary key
SiteNum <---foreign key to SitesTable
EmpNum <---Foreign key to EmployeesTable
StartDate

On my form my RecordSource is EmployeesTable. I've created the following
code for my add button:
----------------------------------------------------------------------------------------------------------

'Verify required fields
If IsNull(me.empnum) or isnull(me.firstname) or IsNull(me.lastname) or
IsNull(me.email) then
msgbox ("Please fill in all required fields")
exit sub
end if

'Add a default record to the SiteHistoryTable.
Dim db As Database
Dim strsql As String

strsql = "INSERT INTO SiteHistoryTable ( EmpNum, SiteNum, StartDate ) SELECT
" & me.empnum & " AS EmpNum, 9 as SiteNum, " & Date & " as StartDate;"
Set db = CurrentDb()

db.Execute strsql

Set db = Nothing

'Start a new record
DoCmd.GoToRecord , , acNext
 
Access does not have triggers. Consequently you will need to execute 2
queries to append records to 2 tables.
 
So what you're saying is, I need to turn my form into an unbound form and
then when the Add button is clicked, execute one query to insert a record in
my employeestable and then another query to insert a related record in my
sitehistorytable. Just making sure I understand.
 
No: if it's a bound form, you can use its AfterInsert event procedure to
execute the append query so the new record gets added to the other table.

(I misunderstood your example: thought you were adding records to the main
table via an Append query.)
 
Back
Top