' Try this
Private Sub ButtonName_Click()
Dim MaxDateID As Double
' To add the records to the main table, add all fields except of the key, it
will be assign automaticaly
DoCmd.RunSQL "INSERT INTO tblDateName ( [Field1 Name], [Field2 Name]
,[Field3 Name] ,[Field4 Name] ) SELECT tblDateName.[Field Name] ,
tblDateName.[Field2 Name] ,tblDateName.[Field3 Name] ,tblDateName.[Field4
Name] FROM tblDateName WHERE [DateID] = " & Me.DateID
' Look for the new key
MaxDateID = DMax("DateID", "tblDateName")
' Insert the new records in the second table based on the old key, but with
the new key above
DoCmd.RunSQL "INSERT INTO tblTimeJob ( [Field1 Name], [Field2 Name] ,[Field3
Name] ,[Field4 Name],[DateID] ) SELECT tblTimeJob.[Field Name] ,
tblTimeJob.[Field2 Name] ,tblTimeJob.[Field3 Name] ,tblTimeJob.[Field4 Name]
, " & MaxDateID & " FROM tblTimeJob WHERE [DateID] = " & Me.DateID
' refresh the form, so the new record will be added
Me.Requery
' Look for the new record
With Me.RecordsetClone
.FindFirst "[DateID] = " & MaxDateID
If .NoMatch Then
MsgBox "Timeheet not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub
--
I hope that helped
Good luck
Melinda said:
OK, the table that my master form (frmTimeCard) comes from is tblDateName.
The table my sub form (frmSub) comes from is tblTimeJob. The forms are
linked by DateID which is an AutoNumber in tblDateName and a Number in
tblTimeJob.
Thanks for the help! I've never even heard of Insert Queries! If you need
any more info let me know.