Append query in code

[cwitc]

Anyone know how to write code for append query in VBA.
the append query can change the attributes based on the criteria. it looks something like that...

if condition is true then
append field 1, field 2
else
append field 2, field 3
end if

 

[John.Woody]

Create the query. This will give you the sql code you need, then look for help on Insert Into. You may need to adjust the sql code i.e. "...if condition = " & True & " then..." etc.

HTH
John

 

[cwitc]

oh! this is what I have tried but it was not work.
the code stop at qry.execute command

 

[Gremlin76]

You should be able to do this a couple of different ways.
My preference is with SQL and recordsets. I find it easier to manipulate (add variable information from forms) the information with SQL than with queries, but by what I have heard in this forum SQL will run slower and will increase database blot more than queries or query definitions.

To do this with a record set.
Open a record set, see help or this forum if unfamiliar with doing this in your version of Access, and use the following outline.
If condition Then
With recordsetname
.Addnew (or go to specified record in recordset)
!field1 = value1
!field2 = value2
.Update
End With
Else
With recordsetname
.Addnew
!field1 = value1
!field2 = value2
.Update
End With
End If

If going to a specified record in the recordset be sure to check that the recordset contains the record needed or else an error will be generated.

To do this with queries, I would create 2 separate queries, one for condition 1 (field 1 and 2) and the other for condition 2 (field 2 and 3). This should prevent the need for conditional statements in the queries. The code would then look something like this.

If condition Then
DoCmd.OpenQuery "condition1"
Else
DoCmd.OpenQuery "condition2"
End If

Either way you go be sure to save the record before using the code. If this information is from a form, the queries will not be able to see the changes or inserted records until the record is saved.

I hope this helps
sTeven

 

[John.Woody]

My best guess would be that the syntax is wrong in what you have written, without more specific info on the error message and the code written it is difficult to assess. If you want to post the info I'll help if I can.

John

 

[cwitc]

thx John, Steven. I solve the syntax problem eventually, the code should written this way

Dim dbs As Database

Set dbs = CurrentDb

dbs.Execute "INSERT INTO TempTblTransac SELECT * FROM [4TBLTRANSAC];"

dbs.Close