Insert in VBA

  • Thread starter Thread starter B
  • Start date Start date
B

B

Anyone out there can tell me what you need to change when you copy a query
in sql view and copy that into a VBA module... I tried and this one does not
want to work not sure what I am missing

DoCmd.RunSQL ("INSERT INTO [tblData](OPENYEAR, ACTINDX, ACTNUMBR_1,
ACTNUMBR_2, ACTNUMBR_3, ACTDESCR, CRDTAMNT," _
& "DEBITAMT, NetChange, TRXDATE)" _
& "(SELECT GL20000.OPENYEAR, GL00100.ACTINDX, GL00100.ACTNUMBR_1,
GL00100.ACTNUMBR_2, GL00100.ACTNUMBR_3," _
& "GL00100.ACTDESCR, GL20000.CRDTAMNT, GL20000.DEBITAMT,
[DEBITAMT]-[CRDTAMNT] AS NetChange, GL20000.TRXDATE" _
& "FROM GL00100 INNER JOIN GL20000 ON GL00100.ACTINDX = GL20000.ACTINDX" _
& "WHERE (GL20000.TRXDATE) Between [Forms]![frmData]![BegDate] And
[Forms]![frmData]![EndDate]")
 
B said:
Anyone out there can tell me what you need to change when you copy a query
in sql view and copy that into a VBA module... I tried and this one does
not
want to work not sure what I am missing

DoCmd.RunSQL ("INSERT INTO [tblData](OPENYEAR, ACTINDX, ACTNUMBR_1,
ACTNUMBR_2, ACTNUMBR_3, ACTDESCR, CRDTAMNT," _
& "DEBITAMT, NetChange, TRXDATE)" _
& "(SELECT GL20000.OPENYEAR, GL00100.ACTINDX, GL00100.ACTNUMBR_1,
GL00100.ACTNUMBR_2, GL00100.ACTNUMBR_3," _
& "GL00100.ACTDESCR, GL20000.CRDTAMNT, GL20000.DEBITAMT,
[DEBITAMT]-[CRDTAMNT] AS NetChange, GL20000.TRXDATE" _
& "FROM GL00100 INNER JOIN GL20000 ON GL00100.ACTINDX = GL20000.ACTINDX" _
& "WHERE (GL20000.TRXDATE) Between [Forms]![frmData]![BegDate] And
[Forms]![frmData]![EndDate]")
Click to expand...

Spaces between the MEANINGFUL WORDS in the SQL statement. You need a space
in front of the WHERE, in front of the FROM, etc. Build a string from your
statement and then use Debug.Print to print it to the Immediate window. That
is an easy way to find such SQL syntax problems.
 
You need to make sure that you have a space between words.
[DEBITAMT]-[CRDTAMNT] AS NetChange, GL20000.TRXDATE" _
& "FROM GL00100 INNER JOIN GL20000 ON GL00100.ACTINDX = GL20000.ACTINDX" _
Click to expand...

There is no space between TRXDATE and FROM, resulting in TRXDATEFROM. Put a
space between the E and the " or between the " and the F. The same problem
exists between TRXDATE and SELECT and between ACTINDX and WHERE. No space
after a comma shouldn't cause a problem, but it's ok if you add it.

The forms reference may need to be concatenated in as values. Try the
changes above first. If you still have problems, then change the last line
to:

"WHERE (GL20000.TRXDATE) Between " & [Forms]![frmData]![BegDate] & " And " &
[Forms]![frmData]![EndDate])

This will have VBA get the values from the form frmData and concatenate in
the values so that the query doesn't have to go get them.

Also, I would recommend a different function.

CurrentDb.Execute strSQL, dbFailOnError

Either assign your string to strSQL first or replace strSQL with the string.
The advantage of this syntax is that it won't prompt you prior to running
the SQL and you don't have to remember to SetWarnings False/True to handle
the prompts. It also will return an error if the SQL fails.

--
Wayne Morgan
MS Access MVP


B said:
Anyone out there can tell me what you need to change when you copy a query
in sql view and copy that into a VBA module... I tried and this one does
not
want to work not sure what I am missing

DoCmd.RunSQL ("INSERT INTO [tblData](OPENYEAR, ACTINDX, ACTNUMBR_1,
ACTNUMBR_2, ACTNUMBR_3, ACTDESCR, CRDTAMNT," _
& "DEBITAMT, NetChange, TRXDATE)" _
& "(SELECT GL20000.OPENYEAR, GL00100.ACTINDX, GL00100.ACTNUMBR_1,
GL00100.ACTNUMBR_2, GL00100.ACTNUMBR_3," _
& "GL00100.ACTDESCR, GL20000.CRDTAMNT, GL20000.DEBITAMT,
[DEBITAMT]-[CRDTAMNT] AS NetChange, GL20000.TRXDATE" _
& "FROM GL00100 INNER JOIN GL20000 ON GL00100.ACTINDX = GL20000.ACTINDX" _
& "WHERE (GL20000.TRXDATE) Between [Forms]![frmData]![BegDate] And
[Forms]![frmData]![EndDate]")
Click to expand...
 
Back
Top