Trouble with INSERT INTO

[csprop]

I am trying to use a SQL string to INSERT INTO a table. My sub reads as follows:

Private Sub makeTrans_Click()
Dim SQL As String

SQL = "INSERT INTO Transactions (TenantID, Date, Amount, Category, Notes) " & _
"VALUES ([TenantID].Value, [Date].Value, [Amount].Value, [Category].Value, [Notes].Value)"

DoCmd.RunSQL SQL

End Sub

When I call the sub, I get a run-time error '3134' saying I have invalid syntax in my INSERT INTO statement. I don't see anything wrong with the syntax. Is there something I'm missing?

Thanks.

 

[BryanT]

Hello csprop

It looks like you are currently trying to insert the values "[TenantID].Value" etc. Not the Value contained in those items. You need to concatenate thos items into the string with quotes as appropriate
Try

SQL = "INSERT INTO Transactions (TenantID, Date, Amount, Category, Notes) " & _
"VALUES ('" & [TenantID].Value & "','" & [Date].Value & "'," [Amount].Value & ","' & [Category].Value & "','" & [Notes].Value)"'"


I hope this helps
Bryan

 

[csprop]

That was definately a step in the right direction. I changed that and did a Debug.Print on the SQL string and the intermediate window showed:

INSERT INTO Transactions (TenantID, Date, Amount, Category, Notes) VALUES ('1','11/5/2004','10','Late Fee','note')

.. which looks ok to me. But I still get the "Syntax error in INSERT INTO statement" on the DoCmd.RunSQL SQL line. I'm very confused.

 

[RuralGuy]

Date is a reserved word in Access. Rename that field or it will continue to give you grief.

 

[csprop]

That did the trick, I just changed the name of the field to DateOf instead. Thanks a million.

 

[RuralGuy]

You're welcome and thanks for posting back with your success.

 

[isnogud]

totally confused

strSQL = "insert into position (dd,shift,kact,pact,ktar,ptar,refhead) " & _
"values (" & dd & "," & shift & "," & kact & "," & pact & "," & ktar & "," & ptar & "," & idhead & ");"
ale_db.Execute strSQL


Hi!

I have almost the same problem with my insert into code.

Either I get the Error message 3061 with text "Too few parameters. Expected 1."

or I get the error message 3134 with text "Syntax error in INSERT INTO statement."

Can anybody see my mistake?

I don't know what to do any more.

Thanks for your answers.

 

[RuralGuy]

What do you change to change the error?

 

[pa3329]

I was just about to post a question about how to insert into a table when I saw this thread. I have a similar problem. I need to insert information from a for into a table, if that information isn't already in the table. Right now I have it opening the table so I can manually insert the information in the table but I want it to automate that. My question is: How can I get it to automatically insert an autonumber? In my table I have two fields, LocationID (autonumber) and Location (text).

 

[RuralGuy]

When you add a new record to your table with [Location], the AutoNumber field will take care of itself and increment.

 

[pa3329]

When you add a new record to your table with [Location], the AutoNumber field will take care of itself and increment.

I thought it might but I wanted to make sure, thanks!

 

[pa3329]

Here is what I have so far:
Dim SQL As String
SQL = "INSERT INTO tblContainers (Container) " _
"VALUES ('" & [Container].Value )"'"
DoCmd.RunSQL SQL
but it gives me an error "Expected: End of statement" at "VALUES ('". Does anybody have any idea why? As you can tell I have never programmed any code into Access.

 

[RuralGuy]

SQL = "INSERT INTO tblContainers (Container) " _
"VALUES ('" & [Container] & "')"

 

[pa3329]

Same error. Here is the code in the entire Sub:

Private Sub CboContainer_NotInList(NewData As String, Response As Integer)
If Len(NewData & "") > 0 Then

If MsgBox("[" & NewData & "] is not in the Containers list." & vbCr & vbCr & "Do you want to add it?", vbQuestion + vbYesNo) = vbYes Then



'DoCmd.OpenForm "frmContainers", acNormal, , , acFormAdd, acDialog, NewData
Dim SQL As String
SQL = "INSERT INTO tblContainers (Container) " _
"VALUES ('" & [CboContainer] "')"
DoCmd.RunSQL SQL


Response = acDataErrAdded '-- Causes the ComboBox to requery

Else

Response = acDataErrContinue

End If

Else

Response = acDataErrContinue

End If
End Sub

 

[FireStrike]

SQL = "INSERT INTO tblContainers (Container) " _
"VALUES ('" & [Container] & "')"

In your example you do not have the second & after container. If this is not a typo, then that is your problem.

 

[pa3329]

I still get the error. I guess instead of [Container] I need [NewData] since I am passing NewData into the Sub? either way I still get the error.

 

[RuralGuy]

Try with a semicolon at the end:
SQL = "INSERT INTO tblContainers (Container) " _
"VALUES ('" & [NewData] & "');"

 

[jkl0]

If the line is broken as your example try

SQL = "INSERT INTO tblContainers (Container) " & _
"VALUES ('" & [NewData] & "');"

hope this helps?

 

[pa3329]

If the line is broken as your example try

SQL = "INSERT INTO tblContainers (Container) " & _
"VALUES ('" & [NewData] & "');"

hope this helps?

That did the trick! Thank you so much!

If you were here I'd give you a kiss!

 

[mtairhead]

I'm sure these problems have been long since solved, but since the thread helped me ID a problem, I thought I'd post an alternative solution:

Although "date" is reserved by Access, you can always get around it by using [brackets].

My SQL statement wasn't going anywhere:

sql = "INSERT INTO UPDATE_Orders (Date) VALUES (date()) "
DoCmd.RunSQL (sql)

I changed it to...

sql = "INSERT INTO UPDATE_Orders ([Date]) VALUES (date()) "
DoCmd.RunSQL (sql)

Now it works.

~Andrew