Trying to run SQL ststement in an event procedure

  • Thread starter jonathansnyder via AccessMonster.com
  • 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
 
D

Douglas J. Steele

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)
 
J

John Spencer

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...
 
J

jonathansnyder via AccessMonster.com

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...
 
Top