Insert Into Select Where Issues

[VFWMain]

I have been trying for hours to get this code to work, I have looked at countless websites and gained tidbits of info here and there.
I have two tables, One is the main entry table (Safety Audits) for auditing and the other is to close an open audit (Closed Audits). What I need to do is to insert the data from Closed Audits into the Safety Audits.
Why not use the UPDATE statement you may ask? There is no data to update in the two fields [DateClosed],[CAR]. So i have to use an insert statement.

Below is the code:

Dim CAD As Date ' Date Audit is to be closed
Dim MainSql As String
Dim CARE As String ' User entered filed to describe why audit is being closed
Dim ADID As Long ' Entered Audit ID
Dim AudSt As String ' Status of the Audit is not closed
AudSt = "Closed"
ADID = Me.txtID
CAD = Format(Me.txtDateC, "mm/dd/yyyy")
CARE = Me.txtRC


MainSql = "INSERT INTO [tblAudits](DateClosed, CAR, Status) VALUES(#" & CAD & "# ,'" & CARE & "', '" & AudSt & "') SELECT([Audit ID], DateClosed, CAR) FROM [Closed Audits ]WHERE [tblAudits].[Finding ID] =" & Me.txtID & ");"


I am now getting a error saying there is a missing ";" at the end of the statement.

This is what I see in the immediate window.


INSERT INTO [tblAudits](DateClosed, CAR, Status) VALUES(#3/11/2014# ,'Test', 'Closed') SELECT([Audit ID], DateClosed, CAR) FROM [Closed Audits ]WHERE [tblAudits].[Finding ID] =1505);

Any and all help is greatly appreciated.

[orange]

Not sure I'm following but see what this gives in the immediate window

MainSql = "INSERT INTO [tblAudits](DateClosed, CAR, Status)   SELECT([Audit ID], DateClosed, CAR) FROM [Closed Audits ]WHERE  [tblAudits].[Finding ID] =" & Me.txtID & ");"