Adding records to multiple tables

[access345]

I am trying to add a record to multiple tables at the same time. My current Code created multiple records in the same table.

I am trying two different methods with no results:

Public Sub txtSerialNumber_Enter()

Dim txtLotNumber As String
Dim txtSerialNumber As String
'Dim db As Database
'Set db = OpenDatabase("Modem.mdb")
'Dim Tbl As Recordset
'Set Tbl = Modem.OpenRecordset("TblLotNumber", dbOpenDynaset)


'txtSerialNumber = Me!txtSerialNumber
'Me!txtSerialNumber.
'txtLotNumber = Format(Now, "mm/dd/yyyy")
'Me!txtLotNumber.AddNew
'txtLotNumber = Me!txtLotNumber
'txtSerialNumber
'Me!txtLotNumber.Update

'For Each tbl In Array
'("TblLotNumber", "TblIncomingInspection", "TblFlashTest", "TblFunctionalTest")
'CurrentDb.Execute "INSERT INTO TblLotNumber (Me!txtSerialNumber,LotNumber)VALUES &" '"(Me!txtSerialNumber & txtLotNumber)&"'""
'CurrentDb.Execute "INSERT INTO TblIncomingInspection (" & Me!txtSerialNumber & ",'" & txtLotNumber & "') VALUES (" & Me!txtSerialNumber & ",'" & txtLotNumber & "')"
'CurrentDb.Execute "INSERT INTO TblFlashTest (SerialNumber,LotNumber) VALUES (" & Me!txtSerialNumber & ",'" & txtLotNumber & "')"
'CurrentDb.Execute "INSERT INTO TblFunctionalTest (SerialNumber,LotNumber) VALUES (" & Me!txtSerialNumber & ",'" & txtLotNumber & "')"
'CurrentDb.Updatable

End Sub

[MMcCarthy]

Assumptions made:

Each table has two fields called SerialNumber datatype text and LotNumber datatype date that defaults to todays date.

Try this code:

 
Public Sub txtSerialNumber_Enter()
 
  DoCmd.RunSQL "INSERT INTO TblLotNumber (SerialNumber, LotNumber) VALUES ('" & _
  Me!txtSerialNumber & "', " Format(Now, "mm/dd/yyyy" & ")"
  DoCmd.RunSQL "INSERT INTO TblIncomingInspection (SerialNumber, LotNumber) VALUES ('" & _
  Me!txtSerialNumber & "', " Format(Now, "mm/dd/yyyy" & ")"
  DoCmd.RunSQL "INSERT INTO TblFlashTest (SerialNumber, LotNumber) VALUES ('" & _
  Me!txtSerialNumber & "', " Format(Now, "mm/dd/yyyy" & ")"
  DoCmd.RunSQL "INSERT INTO TblFunctionalTest (SerialNumber, LotNumber) VALUES ('" & _
  Me!txtSerialNumber & "', " Format(Now, "mm/dd/yyyy" & ")"
 
End Sub