Syntax error on insert

[Guest]

can you tell me what is wrong with this. i get a message which just says Sql
syntax error

CurrentDb.Execute "INSERT INTO Ingredients ( Ingredient, " _
& "Note ) Values (" _
& "'Ingr2', " _
& "'note2')", dbFailOnError

 

[Brian Bastl]

Assuming you have Ingredient and Note in Ingredients table, and you have
controls on your form named Ingr2 and note2 (and they are text values) then
the following should work:

CurrentDb.Execute "INSERT INTO Ingredients ( Ingredient, " _
"Note ) Values (" _
"'" & Ingr2 & "', " _
"'" & note2 & "')", dbFailOnError

otherwise, if Ingr2 and note2 are numeric values, then

CurrentDb.Execute "INSERT INTO Ingredients ( Ingredient, " _
"Note ) Values (" _
"" & Ingr2 & ", " _
"" & note2 & ")", dbFailOnError

HTH,
Brian

 

[Guest]

thanks, but i still have the same problem. in my last example i use just
hard coding string values in to get the syntax right. here is a more
acturate ex:

Tbl:
Ingredients
IngredientId autoNumber primary key
Ingredient text
Note text

in a tab on my form i have 2 text boxes
AddTabIngredient
AddTabNote
(Both of these text boxes are on the tab named AddTab.

The tab also has a button which when clicked executes:


Private Sub AddTabAddIngredientButton_Click()
On Error GoTo Err_AddTabAddIngredientButton_Click

CurrentDb.Execute "INSERT INTO Ingredients ( Ingredient, " _
& "Note ) Values (" _
& "'" & Me!AddTabIngredient & "', " _
& "'" & Me!AddTabNote & "')", dbFailOnError

Me!AddTabIngredient = ""


Rem Refreshes form data
DoCmd.RunCommand acCmdRefresh

Exit_AddTabAddIngredientButton_Click:
Exit Sub

Err_AddTabAddIngredientButton_Click:
MsgBox Err.Description
Resume Exit_AddTabAddIngredientButton_Click

End Sub


when i click the button i get a message:

Syntax error in Insert INTO statement
--
thanks
brian

Assuming you have Ingredient and Note in Ingredients table, and you have
controls on your form named Ingr2 and note2 (and they are text values) then
the following should work:

CurrentDb.Execute "INSERT INTO Ingredients ( Ingredient, " _
"Note ) Values (" _
"'" & Ingr2 & "', " _
"'" & note2 & "')", dbFailOnError

otherwise, if Ingr2 and note2 are numeric values, then

CurrentDb.Execute "INSERT INTO Ingredients ( Ingredient, " _
"Note ) Values (" _
"" & Ingr2 & ", " _
"" & note2 & ")", dbFailOnError

HTH,
Brian

 

[Brian Bastl]

brianv,

The ampersand ( should you choose to use it ) would preceed the underscore
on the previous line.

CurrentDb.Execute "INSERT INTO Ingredients ( Ingredient, " & _
"Note ) Values (" & _
"'" & Me!AddTabIngredient & "', " & _
"'" & Me!AddTabNote & "')", dbFailOnError

By the way, why are you using SQL to append the data to the table? Is the
form unbound?

Brian

thanks, but i still have the same problem. in my last example i use just
hard coding string values in to get the syntax right. here is a more
acturate ex:

Tbl:
Ingredients
IngredientId autoNumber primary key
Ingredient text
Note text

in a tab on my form i have 2 text boxes
AddTabIngredient
AddTabNote
(Both of these text boxes are on the tab named AddTab.

The tab also has a button which when clicked executes:


Private Sub AddTabAddIngredientButton_Click()
On Error GoTo Err_AddTabAddIngredientButton_Click

CurrentDb.Execute "INSERT INTO Ingredients ( Ingredient, " _
& "Note ) Values (" _
& "'" & Me!AddTabIngredient & "', " _
& "'" & Me!AddTabNote & "')", dbFailOnError

Me!AddTabIngredient = ""


Rem Refreshes form data
DoCmd.RunCommand acCmdRefresh

Exit_AddTabAddIngredientButton_Click:
Exit Sub

Err_AddTabAddIngredientButton_Click:
MsgBox Err.Description
Resume Exit_AddTabAddIngredientButton_Click

End Sub


when i click the button i get a message:

Syntax error in Insert INTO statement

 

[Guest]

let me start by saying I really do not know what i am doing. i have never
used Access or Visual Basic before. i am a java developer and my daughter is
trying to use this in school. she has no book so i am trying to pick it up a
little. i have run several test and committed out the code for each stmt
before going to the next. here is what i see:


Add a rcd with an idgredient but no note.
Rem this works
CurrentDb.Execute "INSERT INTO Ingredients ( Ingredient ) Values (" & _
"'" & Me!AddTabIngredient & "')", dbFailOnError


use the note field on the tab to add the rcd into Ingredient. this
tells me that i can access and use the data in the note field on the tab.
the insert rcd has a valkue for Ingredient, even if it is what was on the
note field in the tab, and the Note field in the rcd is blank
Rem this works
CurrentDb.Execute "INSERT INTO Ingredients ( Ingredient ) Values (" & _
"'" & Me!AddTabNote & "')", dbFailOnError


This fails with the syntax error. For some reason it does note like the
Note field in the tbl.
Rem this fails. something is wrong with Note
CurrentDb.Execute "INSERT INTO Ingredients ( Note ) Values (" & _
"'" & Me!AddTabNote & "')", dbFailOnError


Tbl
Ingredients
IngredientId autoNumber
Ingredient text 50
Note text 80

any ideas? not sure how to use bound but my fields are UNBOUND. after this
i will have a tab for change and another for delete. my intent is to click
the data in the List box on the FORM and when i select a tab, Change or
Delete, the data from the list box will transfer in. i can then change the
data, except for the key, and click a button to do the Update or Delete.

 

[Brian Bastl]

brianv,

Unless it's a homework project with a requirement to use an Append query,
I'd honestly suggest that you do away with the SQL Insert Statement, and
instead (using the Wizard) create a form whose record source is either your
Ingredients table or a query based on your Ingredients table. The form is
then "bound" to your Ingredients table. And each control on your form
(assuming you've created it with the Wizard) will be bound to a
corresponding field in your table. When you type something into your text
boxes on your form, these values are written to the underlying table when
the record is saved, by either moving to a new or different record, or when
a command is executed to save the record. To edit a record, you'd direct
your "bound" form to display it. Any changes you make to the data (assuming
your inputs and underlying fields' data-types are the same) will be updated
when you move off the current record, or issue a command via code.

As to the reason why "Note" fails, I couldn't really say, off hand. Perhaps
your data-type in the table is actually set to something other than text
(?).

HTH,
Brian

 

[Douglas J Steele]

Is Ingredient perhaps a numeric field? If so, remove the quotes around the
value you're trying to write for it.

 

[Guest]

thanks for the help.

the fields are unbound. i will look into making them bound. i do believe i
understand the problem i was having. it appears that the name Note must be
reserved. if i rename the field it works.
--
thanks
brian

Is Ingredient perhaps a numeric field? If so, remove the quotes around the
value you're trying to write for it.