I'm trying to... whenever a record is added to one table have it automatically add/append 3 fields from that record to another table. I tried making an apend query, but that copies all records from the source table. I just need the newly created record to be copied. I then tried VBA code from searching online, put it in the after update control of the form and came up with this, but it doesn't quite work.:

DoCmd.RunSQL "INSERT INTO EmployeeLog (EmployeeID, Work_Date, WorkCompleted) SELECT Me.EmployeeID, Me.Date_Off, Me.Reason"

I've tried variations of it like:

SELECT [Forms]![EmployeeDaysOffsubform]![Field1], [EmployeeID]![EmployeeDaysOffsubform]![Date_off],[Forms]![EmployeeDaysOffsubform]![Reason]

I dont know much about VBA... I can read most of it, but cant write it myself, and have no clue of the correct syntax when writing. Does anyone have suggestons on the correct code or other means of doing so? Also, is there a way to have it apend the records WITHOUT having the confirm dialog box appear before it runs?
Thanks for any help

BTW the table names are:
Source table: EmployeeDaysOff & EmployeeDaysOffsubform
Fields: EmployeeID, Date_Off, Reason
Destination table: Employee Log & EmployeeLogsubform
Fields: EmployeeID, Work_date, Workcompleted

You can do this simply using vba



Dim Rs As DAO.RecordSet
Set Rs = CurrentDb.OpenRecordset("EmployeeLog")

Rs.AddNew
Rs("EmployeeID") = Me.EmployeeID
Rs("Work_Date") = Me.Date_Off
Rs.("WorkCompleted") = Me.Reason
Rs.Update
Rs.Close
Set Rs = Nothing

Put this on the OnClick command of your button

BTW to turn off messages use

DoCmd.SetWarnings False/True

David

Thanks works great... I put that code in the After Update event of the form, and it works, but if I happen to just modify an existing record, it also re-appends that record too. What, or is there an event that I can place the code so it will only append NEW records and leave any modified ones alone? If possible, I'd prefer not have a button control and have it work automatically

Add a new field and flag it when new. Then use an IF statement to decide whether to update or not.

Another workaround is instead of automatically doing an AddNew you change the recordset to look for the PK then if it is not present continue with the add new otherwise change it to edit

Such as:

Set Rs = CurrentDb.OpenRecordset("Select * from Table Where Field=" PK)

If Not Rs.EOF And Not Rs.BOF Then
Rs.Edit
Else
Rs.AddNew
End If


David