Append Query with VBA (Access 2000)

  • Thread starter Thread starter Guest
  • Start date Start date
Hi,I believe this is the general syntax:

**CODE BEGINS***

Dim strSQL as string

strSQL = "INSERT INTO YOURTABLE (YOUR FIELDS) VALUES(" & _
yourformfield & ");"

CurrentDb.Execute strSQL

***CODE ENDS***

Post back if you have any problems

Cheers

John Webb
 
John

Thanks I tried your suggestion but did not work, I get syntax error code 3144.

Here's my code:-

Dim StrSQL As String

StrSQL = "INSERT INTO tblPjt" & Forms![frmPjtPmt]![txtSiteCode] & "PmtLog (
PmtVoucherDateSupplier, SumOfValueNet, SumOfValueVAT, SumOfValueGross )SELECT
tblPmtRequest.PmtVoucherDate, Sum(tblPmtRequest.ValueNet) AS SumOfValueNet,
Sum(tblPmtRequest.ValueVAT) AS SumOfValueVAT, Sum(tblPmtRequest.ValueGross)
AS SumOfValueGross FROM tblPmtRequest GROUP BY tblPmtRequest.PmtVoucherDate;"
CurrentDb.Execute StrSQL

Regards
Chase
 
Ok, looking at that hurts my eyes :-)

I notice there is no space between the first closing bracket and the SELECT
statement, I wonder if that may be something to do with the error?

Other possibilities could be the table doesn't exist, or the code is not
picking up the textbox value correctly.

First thing I would do is put the following just before the code:
debug.print strSQL, and check the SQL to see if would work. Then, if you
are happy you think it should, paste the immediate window into a blank
query (the SQL pane of course), and try it, see if it does run.

Also, for some reason I have noticed that occaisionally Access doesn't like
using the CurrentDB.Execute for certain SQL strings, but using DoCmd.RunSQL
strSQL works fine; so perhaps try that.

I must admit that I have only just returned back to Access after working on
some C++ projects, and I might be a little rusty; so if you still can't get
it to work, post back with the text from the immediate pane, so I can see
the SQL it generates.
 
Back
Top