Change the value of a field in an append query using SQL (1 Viewer)

MackMan

Registered User.
Local time
Today, 08:35
Joined
Nov 25, 2014
Messages
174
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

Registered User.
Local time
Today, 08:35
Joined
Feb 26, 2014
Messages
64
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
 

Users who are viewing this thread

Top Bottom