Trying to run SQL ststement in an event procedure

  • Thread starter Thread starter jonathansnyder via AccessMonster.com
  • Start date Start date
J

jonathansnyder via AccessMonster.com

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
 
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)
 
VERY small quibble. Documentation (97 Help) says 10. In Access 2000 I can
have 24 line continuations

--
John Spencer

Douglas J. Steele said:
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)
Click to expand...
 
Thanks
Douglas. Its always the little thing that trip me up!

Jonathan
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)
HI folks,
Click to expand...
[quoted text clipped - 53 lines]
Jonathan
Click to expand...
Click to expand...
 
Back
Top