Trying to run SQL ststement in an event procedure

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jonathansnyder - 12 Jan 2007 01:21 GMT

HI folks,

Im trying to run a make-table query via docmd.runsql within en event
procedure. I get a compile error on the SQL statement with the word "SELECT"
highlited. I copied the SQL statement directly from the query. I suspect
this is the problem.

Here is my code: Explanation of what code is supposed to do follows:

Private Sub cmdclose_Click()
On Error GoTo Err_cmdclose_Click

Dim strSQL As String

strSQL = SELECT TblMTRPVillage.Village, QryPBtaggerTotalByVillage.
CountOfTagger_ID AS PB_Total, QrySeotTaggerTotalByVillage.CountOfTagger_ID AS
SEOT_Total, QryWalrTaggerTotalByVillage.CountOfTagger_ID AS Walr_Total INTO
tblMTRPTaggersByVillage IN 'M:\MMM_SHAR\DATA\Alaska.mdb'FROM ((TblMTRPVillage
LEFT JOIN QrySeotTaggerTotalByVillage ON TblMTRPVillage.Village =
QrySeotTaggerTotalByVillage.Village) LEFT JOIN QryWalrTaggerTotalByVillage ON
TblMTRPVillage.Village = QryWalrTaggerTotalByVillage.Village) LEFT JOIN
QryPBtaggerTotalByVillage ON TblMTRPVillage.Village =
QryPBtaggerTotalByVillage.Village;

If Upd = 1 Then
DoCmd.RunSQL (strSQL)
DoCmd.Close

Else: DoCmd.Close

End If

Exit_cmdclose_Click:
Exit Sub

Err_cmdclose_Click:
MsgBox Err.Description
Resume Exit_cmdclose_Click

End Sub

UPD is a public variable declared in a module, on form open UPD is set to 0,
if a record is updated UPD is set to 1

I want to run a make-table querey when the user closes the form if any record
has been updated. The table created is then used in an ARC GIS project.

Thanks in Advance

Jonathan

Reply to this Message

Douglas J. Steele - 12 Jan 2007 01:58 GMT

You're missing the quotes around the SQL statement.

strSQL = "SELECT TblMTRPVillage.Village, QryPBtaggerTotalByVillage.
CountOfTagger_ID AS PB_Total, QrySeotTaggerTotalByVillage.CountOfTagger_ID
AS
SEOT_Total, QryWalrTaggerTotalByVillage.CountOfTagger_ID AS Walr_Total INTO
tblMTRPTaggersByVillage IN 'M:\MMM_SHAR\DATA\Alaska.mdb'FROM
((TblMTRPVillage
LEFT JOIN QrySeotTaggerTotalByVillage ON TblMTRPVillage.Village =
QrySeotTaggerTotalByVillage.Village) LEFT JOIN QryWalrTaggerTotalByVillage
ON
TblMTRPVillage.Village = QryWalrTaggerTotalByVillage.Village) LEFT JOIN
QryPBtaggerTotalByVillage ON TblMTRPVillage.Village =
QryPBtaggerTotalByVillage.Village;"

That has to be all on one line. If you'd prefer shorter lines, you can use
the line continuation character, _, as:

strSQL = "SELECT TblMTRPVillage.Village, " & _
"QryPBtaggerTotalByVillage.CountOfTagger_ID AS PB_Total, " & _
"QrySeotTaggerTotalByVillage.CountOfTagger_ID AS SEOT_Total, " & _
"QryWalrTaggerTotalByVillage.CountOfTagger_ID AS Walr_Total INTO " & _
"tblMTRPTaggersByVillage IN 'M:\MMM_SHAR\DATA\Alaska.mdb' " & _
"FROM ((TblMTRPVillage LEFT JOIN QrySeotTaggerTotalByVillage " & _
"ON TblMTRPVillage.Village = QrySeotTaggerTotalByVillage.Village) " & _
"LEFT JOIN QryWalrTaggerTotalByVillage ON TblMTRPVillage.Village = " & _
"QryWalrTaggerTotalByVillage.Village) LEFT JOIN QryPBtaggerTotalByVillage
" & _
"ON TblMTRPVillage.Village = QryPBtaggerTotalByVillage.Village;"

(Note: you can have a maximum of 10 continuation characters in a single
statement)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> HI folks,
>
[quoted text clipped - 53 lines]
>
> Jonathan

Reply to this Message

John Spencer - 12 Jan 2007 13:14 GMT

VERY small quibble. Documentation (97 Help) says 10. In Access 2000 I can
have 24 line continuations

Signature

John Spencer

> You're missing the quotes around the SQL statement.
>
[quoted text clipped - 88 lines]
>>
>> Jonathan

Reply to this Message

Douglas J. Steele - 12 Jan 2007 14:19 GMT

Thanks, John. I never realized they'd changed that limitation.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> VERY small quibble. Documentation (97 Help) says 10. In Access 2000 I
> can have 24 line continuations

Reply to this Message

jonathansnyder - 12 Jan 2007 17:39 GMT

Thanks
Douglas. Its always the little thing that trip me up!

Jonathan

>You're missing the quotes around the SQL statement.
>
[quoted text clipped - 34 lines]
>>
>> Jonathan

Reply to this Message