SQL Syntax help

G

Guest

Hello all,
I am using Access 2002. I am not familiar with SQL, but managed to piece
together some SQL with the assistance of the HELP FILE. In my database, I
have a hidden form that I use to store variables to record modifications that
are made to records. I am trying to pull values from this temp form and
throw them into an existing table. It is reduntant, but I use it to record
the activities/changes people make.

The fields I am trying to to insert into an existing table called
Activity_Log are...

Forms!Temp.SR (text) to Activity_Log.SR (text)
Forms!Temp.Modify_BY (text) to Activity_Log.Modify_By (text)
Forms!Temp.str_Email_txt (text) to Activity_Log.Comments (text)
current date to Activity_Log.Modify_Date (Date/Time)

I am trying to execute this code when I close a form that is bound to
another table.

ERROR at runtime: Syntax error INSERT INTO Statement
DEBUGS fine. Any help would be appreciated.

Public Sub DoSQL()
MsgBox "DOSQL"
Dim SQL As String

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, Modify_By,
Modify_Date ) " & _
"SET tbl_Activity_Log.SR = Forms![Temp].[SR], " & _
"tbl_Activity_Log.Comments = Forms![Temp].strEmail_txt, " & _
"tbl_Activity_Log.Modify_By = Forms![Temp].Modify_By, " & _
"tbl_Activity_Log.Modify_Date = Date() "


DoCmd.RunSQL SQL

End Sub
 
K

Ken Snell [MVP]

Concatenate the values into the string, not the names of the controls. And
be sure to delimit the values with ' for text:

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, Modify_By,
Modify_Date ) " & _
"SET tbl_Activity_Log.SR = '" & Forms![Temp].[SR] & "', " & _
"tbl_Activity_Log.Comments = '" & Forms![Temp].strEmail_txt & "',
" & _
"tbl_Activity_Log.Modify_By = '" & Forms![Temp].Modify_By & "', "
& _
"tbl_Activity_Log.Modify_Date = Date() "
 
G

Guest

Ken,
Thank you for your quick response.
Receiving same error message. The skewed lines make it hard to read or
copy/paste, so....lets start small. Just insert one field. Here is what I
have.

SQL = "INSERT INTO tbl_Activity_Log ( SR ) " & _
"SET tbl_Activity_Log.SR = '" & Forms![Temp].[SR] & "'"

What should it be?

Ken Snell said:
Concatenate the values into the string, not the names of the controls. And
be sure to delimit the values with ' for text:

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, Modify_By,
Modify_Date ) " & _
"SET tbl_Activity_Log.SR = '" & Forms![Temp].[SR] & "', " & _
"tbl_Activity_Log.Comments = '" & Forms![Temp].strEmail_txt & "',
" & _
"tbl_Activity_Log.Modify_By = '" & Forms![Temp].Modify_By & "', "
& _
"tbl_Activity_Log.Modify_Date = Date() "


--

Ken Snell
<MS ACCESS MVP>

David said:
Hello all,
I am using Access 2002. I am not familiar with SQL, but managed to piece
together some SQL with the assistance of the HELP FILE. In my database, I
have a hidden form that I use to store variables to record modifications
that
are made to records. I am trying to pull values from this temp form and
throw them into an existing table. It is reduntant, but I use it to
record
the activities/changes people make.

The fields I am trying to to insert into an existing table called
Activity_Log are...

Forms!Temp.SR (text) to Activity_Log.SR (text)
Forms!Temp.Modify_BY (text) to Activity_Log.Modify_By (text)
Forms!Temp.str_Email_txt (text) to Activity_Log.Comments (text)
current date to Activity_Log.Modify_Date (Date/Time)

I am trying to execute this code when I close a form that is bound to
another table.

ERROR at runtime: Syntax error INSERT INTO Statement
DEBUGS fine. Any help would be appreciated.

Public Sub DoSQL()
MsgBox "DOSQL"
Dim SQL As String

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, Modify_By,
Modify_Date ) " & _
"SET tbl_Activity_Log.SR = Forms![Temp].[SR], " & _
"tbl_Activity_Log.Comments = Forms![Temp].strEmail_txt, " & _
"tbl_Activity_Log.Modify_By = Forms![Temp].Modify_By, " & _
"tbl_Activity_Log.Modify_Date = Date() "


DoCmd.RunSQL SQL

End Sub
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Sorry... I think I see.. I left the Date function in the string:

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, " & _
"Modify_By, Modify_Date ) SET tbl_Activity_Log.SR = '" & _
Forms![Temp].[SR] & "', tbl_Activity_Log.Comments = '" & _
Forms![Temp].strEmail_txt & "', " & _
"tbl_Activity_Log.Modify_By = '" & _
Forms![Temp].Modify_By & "', " & _
"tbl_Activity_Log.Modify_Date = #" & _
Format(Date(), "mm/dd/yyyy") & "#;"


--

Ken Snell
<MS ACCESS MVP>

David said:
Ken,
Thank you for your quick response.
Receiving same error message. The skewed lines make it hard to read or
copy/paste, so....lets start small. Just insert one field. Here is what
I
have.

SQL = "INSERT INTO tbl_Activity_Log ( SR ) " & _
"SET tbl_Activity_Log.SR = '" & Forms![Temp].[SR] & "'"

What should it be?

Ken Snell said:
Concatenate the values into the string, not the names of the controls.
And
be sure to delimit the values with ' for text:

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, Modify_By,
Modify_Date ) " & _
"SET tbl_Activity_Log.SR = '" & Forms![Temp].[SR] & "', " & _
"tbl_Activity_Log.Comments = '" & Forms![Temp].strEmail_txt &
"',
" & _
"tbl_Activity_Log.Modify_By = '" & Forms![Temp].Modify_By & "',
"
& _
"tbl_Activity_Log.Modify_Date = Date() "


--

Ken Snell
<MS ACCESS MVP>

David said:
Hello all,
I am using Access 2002. I am not familiar with SQL, but managed to
piece
together some SQL with the assistance of the HELP FILE. In my
database, I
have a hidden form that I use to store variables to record
modifications
that
are made to records. I am trying to pull values from this temp form
and
throw them into an existing table. It is reduntant, but I use it to
record
the activities/changes people make.

The fields I am trying to to insert into an existing table called
Activity_Log are...

Forms!Temp.SR (text) to Activity_Log.SR (text)
Forms!Temp.Modify_BY (text) to Activity_Log.Modify_By (text)
Forms!Temp.str_Email_txt (text) to Activity_Log.Comments (text)
current date to Activity_Log.Modify_Date (Date/Time)

I am trying to execute this code when I close a form that is bound to
another table.

ERROR at runtime: Syntax error INSERT INTO Statement
DEBUGS fine. Any help would be appreciated.

Public Sub DoSQL()
MsgBox "DOSQL"
Dim SQL As String

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, Modify_By,
Modify_Date ) " & _
"SET tbl_Activity_Log.SR = Forms![Temp].[SR], " & _
"tbl_Activity_Log.Comments = Forms![Temp].strEmail_txt, " & _
"tbl_Activity_Log.Modify_By = Forms![Temp].Modify_By, " & _
"tbl_Activity_Log.Modify_Date = Date() "


DoCmd.RunSQL SQL

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Ken,
I appreciate your help, but can we start small. I am getting the same error
on this code. I tried to modify it to just one field, but receiving that
message, syntax error in INSERT INTO

Can you help me modify this code to just insert one field, and I can get the
rest.

Dim SQL As String

' SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, " & _
' "Modify_By, Modify_Date ) SET tbl_Activity_Log.SR = '" & _
' Forms![Temp].[SR] & "', tbl_Activity_Log.Comments = '" & _
' Forms![Temp].strEmail_txt & "', " & _
' "tbl_Activity_Log.Modify_By = '" & _
' Forms![Temp].Modify_By & "', " & _
' "tbl_Activity_Log.Modify_Date = #" & _
' Format(Date, "mm/dd/yyyy") & "#;"

SQL = "INSERT INTO tbl_Activity_Log (SR)" & _
"Set tbl_Activity_Log.SR = 'Forms![Temp].SR';"

DoCmd.RunSQL SQL


Ken Snell said:
Sorry... I think I see.. I left the Date function in the string:

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, " & _
"Modify_By, Modify_Date ) SET tbl_Activity_Log.SR = '" & _
Forms![Temp].[SR] & "', tbl_Activity_Log.Comments = '" & _
Forms![Temp].strEmail_txt & "', " & _
"tbl_Activity_Log.Modify_By = '" & _
Forms![Temp].Modify_By & "', " & _
"tbl_Activity_Log.Modify_Date = #" & _
Format(Date(), "mm/dd/yyyy") & "#;"


--

Ken Snell
<MS ACCESS MVP>

David said:
Ken,
Thank you for your quick response.
Receiving same error message. The skewed lines make it hard to read or
copy/paste, so....lets start small. Just insert one field. Here is what
I
have.

SQL = "INSERT INTO tbl_Activity_Log ( SR ) " & _
"SET tbl_Activity_Log.SR = '" & Forms![Temp].[SR] & "'"

What should it be?

Ken Snell said:
Concatenate the values into the string, not the names of the controls.
And
be sure to delimit the values with ' for text:

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, Modify_By,
Modify_Date ) " & _
"SET tbl_Activity_Log.SR = '" & Forms![Temp].[SR] & "', " & _
"tbl_Activity_Log.Comments = '" & Forms![Temp].strEmail_txt &
"',
" & _
"tbl_Activity_Log.Modify_By = '" & Forms![Temp].Modify_By & "',
"
& _
"tbl_Activity_Log.Modify_Date = Date() "


--

Ken Snell
<MS ACCESS MVP>

Hello all,
I am using Access 2002. I am not familiar with SQL, but managed to
piece
together some SQL with the assistance of the HELP FILE. In my
database, I
have a hidden form that I use to store variables to record
modifications
that
are made to records. I am trying to pull values from this temp form
and
throw them into an existing table. It is reduntant, but I use it to
record
the activities/changes people make.

The fields I am trying to to insert into an existing table called
Activity_Log are...

Forms!Temp.SR (text) to Activity_Log.SR (text)
Forms!Temp.Modify_BY (text) to Activity_Log.Modify_By (text)
Forms!Temp.str_Email_txt (text) to Activity_Log.Comments (text)
current date to Activity_Log.Modify_Date (Date/Time)

I am trying to execute this code when I close a form that is bound to
another table.

ERROR at runtime: Syntax error INSERT INTO Statement
DEBUGS fine. Any help would be appreciated.

Public Sub DoSQL()
MsgBox "DOSQL"
Dim SQL As String

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, Modify_By,
Modify_Date ) " & _
"SET tbl_Activity_Log.SR = Forms![Temp].[SR], " & _
"tbl_Activity_Log.Comments = Forms![Temp].strEmail_txt, " & _
"tbl_Activity_Log.Modify_By = Forms![Temp].Modify_By, " & _
"tbl_Activity_Log.Modify_Date = Date() "


DoCmd.RunSQL SQL

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Ken,
I created this SQL statement from the query object window. I just set the
default value of the Modify_Date field to Date(). Not sure what was wrong
with the SET statements, but this seems to work fine.

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, Modify_By ) " & _
"SELECT [Forms]![Temp].[SR] AS SR, [Forms]![Temp].[strEmail_txt]AS
Comments, [Forms]![Temp].[Modify_By] AS Modify_By;"

Thanks for taking a stab at it.



Ken Snell said:
Sorry... I think I see.. I left the Date function in the string:

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, " & _
"Modify_By, Modify_Date ) SET tbl_Activity_Log.SR = '" & _
Forms![Temp].[SR] & "', tbl_Activity_Log.Comments = '" & _
Forms![Temp].strEmail_txt & "', " & _
"tbl_Activity_Log.Modify_By = '" & _
Forms![Temp].Modify_By & "', " & _
"tbl_Activity_Log.Modify_Date = #" & _
Format(Date(), "mm/dd/yyyy") & "#;"


--

Ken Snell
<MS ACCESS MVP>

David said:
Ken,
Thank you for your quick response.
Receiving same error message. The skewed lines make it hard to read or
copy/paste, so....lets start small. Just insert one field. Here is what
I
have.

SQL = "INSERT INTO tbl_Activity_Log ( SR ) " & _
"SET tbl_Activity_Log.SR = '" & Forms![Temp].[SR] & "'"

What should it be?

Ken Snell said:
Concatenate the values into the string, not the names of the controls.
And
be sure to delimit the values with ' for text:

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, Modify_By,
Modify_Date ) " & _
"SET tbl_Activity_Log.SR = '" & Forms![Temp].[SR] & "', " & _
"tbl_Activity_Log.Comments = '" & Forms![Temp].strEmail_txt &
"',
" & _
"tbl_Activity_Log.Modify_By = '" & Forms![Temp].Modify_By & "',
"
& _
"tbl_Activity_Log.Modify_Date = Date() "


--

Ken Snell
<MS ACCESS MVP>

Hello all,
I am using Access 2002. I am not familiar with SQL, but managed to
piece
together some SQL with the assistance of the HELP FILE. In my
database, I
have a hidden form that I use to store variables to record
modifications
that
are made to records. I am trying to pull values from this temp form
and
throw them into an existing table. It is reduntant, but I use it to
record
the activities/changes people make.

The fields I am trying to to insert into an existing table called
Activity_Log are...

Forms!Temp.SR (text) to Activity_Log.SR (text)
Forms!Temp.Modify_BY (text) to Activity_Log.Modify_By (text)
Forms!Temp.str_Email_txt (text) to Activity_Log.Comments (text)
current date to Activity_Log.Modify_Date (Date/Time)

I am trying to execute this code when I close a form that is bound to
another table.

ERROR at runtime: Syntax error INSERT INTO Statement
DEBUGS fine. Any help would be appreciated.

Public Sub DoSQL()
MsgBox "DOSQL"
Dim SQL As String

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, Modify_By,
Modify_Date ) " & _
"SET tbl_Activity_Log.SR = Forms![Temp].[SR], " & _
"tbl_Activity_Log.Comments = Forms![Temp].strEmail_txt, " & _
"tbl_Activity_Log.Modify_By = Forms![Temp].Modify_By, " & _
"tbl_Activity_Log.Modify_Date = Date() "


DoCmd.RunSQL SQL

End Sub
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

< dumb guy - me that is >

Sometimes, I should just take the afternoon off. :)
I apologize that I overlooked the incorrect use of SET in your SQL
statement. When you do an append query, the general syntax is this:

INSERT INTO TableName
VALUES ( Field1, Field2, Field3 )
SELECT FieldOne, Field2, Field3
FROM AnotherTable;

What you're doing is using a query to select records that then are to be
inserted into a table.

When you have just a single record to insert and you have "constant" values
for that record (such as you're doing), you don't need AnotherTable:

INSERT INTO TableName
VALUES ( Field1, Field2, Field3 )
SELECT 1, 2, 3;

Sorry for delaying your correct answer.

Now I note that you're concatenating the form control names into the query.
While that may work for some situations, it can lead to a "parameters
missing" error in other situations. As such, I would still recommend that
you concatenate the values from those controls into the SQL string.

Good luck.

--

Ken Snell
<MS ACCESS MVP>

David said:
Ken,
I created this SQL statement from the query object window. I just set the
default value of the Modify_Date field to Date(). Not sure what was wrong
with the SET statements, but this seems to work fine.

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, Modify_By ) " & _
"SELECT [Forms]![Temp].[SR] AS SR, [Forms]![Temp].[strEmail_txt]AS
Comments, [Forms]![Temp].[Modify_By] AS Modify_By;"

Thanks for taking a stab at it.



Ken Snell said:
Sorry... I think I see.. I left the Date function in the string:

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, " & _
"Modify_By, Modify_Date ) SET tbl_Activity_Log.SR = '" & _
Forms![Temp].[SR] & "', tbl_Activity_Log.Comments = '" & _
Forms![Temp].strEmail_txt & "', " & _
"tbl_Activity_Log.Modify_By = '" & _
Forms![Temp].Modify_By & "', " & _
"tbl_Activity_Log.Modify_Date = #" & _
Format(Date(), "mm/dd/yyyy") & "#;"


--

Ken Snell
<MS ACCESS MVP>

David said:
Ken,
Thank you for your quick response.
Receiving same error message. The skewed lines make it hard to read or
copy/paste, so....lets start small. Just insert one field. Here is
what
I
have.

SQL = "INSERT INTO tbl_Activity_Log ( SR ) " & _
"SET tbl_Activity_Log.SR = '" & Forms![Temp].[SR] & "'"

What should it be?

:

Concatenate the values into the string, not the names of the controls.
And
be sure to delimit the values with ' for text:

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, Modify_By,
Modify_Date ) " & _
"SET tbl_Activity_Log.SR = '" & Forms![Temp].[SR] & "', " &
_
"tbl_Activity_Log.Comments = '" & Forms![Temp].strEmail_txt
&
"',
" & _
"tbl_Activity_Log.Modify_By = '" & Forms![Temp].Modify_By &
"',
"
& _
"tbl_Activity_Log.Modify_Date = Date() "


--

Ken Snell
<MS ACCESS MVP>

Hello all,
I am using Access 2002. I am not familiar with SQL, but managed to
piece
together some SQL with the assistance of the HELP FILE. In my
database, I
have a hidden form that I use to store variables to record
modifications
that
are made to records. I am trying to pull values from this temp form
and
throw them into an existing table. It is reduntant, but I use it to
record
the activities/changes people make.

The fields I am trying to to insert into an existing table called
Activity_Log are...

Forms!Temp.SR (text) to Activity_Log.SR (text)
Forms!Temp.Modify_BY (text) to Activity_Log.Modify_By (text)
Forms!Temp.str_Email_txt (text) to Activity_Log.Comments (text)
current date to Activity_Log.Modify_Date (Date/Time)

I am trying to execute this code when I close a form that is bound
to
another table.

ERROR at runtime: Syntax error INSERT INTO Statement
DEBUGS fine. Any help would be appreciated.

Public Sub DoSQL()
MsgBox "DOSQL"
Dim SQL As String

SQL = "INSERT INTO tbl_Activity_Log ( SR, Comments, Modify_By,
Modify_Date ) " & _
"SET tbl_Activity_Log.SR = Forms![Temp].[SR], " & _
"tbl_Activity_Log.Comments = Forms![Temp].strEmail_txt, " &
_
"tbl_Activity_Log.Modify_By = Forms![Temp].Modify_By, " & _
"tbl_Activity_Log.Modify_Date = Date() "


DoCmd.RunSQL SQL

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please Help with syntax 6
SQL syntax for INSERT 5
passing SQL commands as text to variable 6
Help with VBA sql code 2
SQL INSERT INTO Syntax 2
sql syntax- Help! 1
SQL where clause in Access VB 3
SQL syntax help please 6

Top