Change the value of a field in an append query using SQL

[MackMan]

Hi guys..

I have a main form which does nothing except filter subforms through a cbo.

On the main form are two subforms. One shows top line data, the other shows a breakdown of the top line data, and are linked by an unbound textbox (it's how it works, and does so perfectly)

I'm running an append query to duplicate a record in the second form using vba/sql BUT... need to have one of the fields values changed based on a field on the parent table.

If Forms!frmmain!frmPost.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblposts] ( TopLineID, AccountID, TransDate, Cat, SubCat, Debit, Credit ) " & _
"SELECT " & lngID & " As NewID, AccountID, TransDate, Cat, SubCat, Credit, Debit " & _
"FROM [tblposts] WHERE TopLineID = " & Me.TopLineID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

Credit and Debits are reversed as I want one to zero out the other.

In regards to the AccountID, I've tried allsorts and it's just not working... to the point I'm almost giving up and finding an alternative.
Whats the "Correct" syntax to attach a "WHERE" statement to the highlighted [AccountID]'s (which needs to be the value on AccountID on the Parent table)

 

[mh123]

So you want the value of accountID to be appended into tblPosts?

You could always grab the value and assign it to a variable in a similar fashion you have NewID