Simple Insert statement

[brianv]

i am trying to write an express which will insert a single row into a table.
the table, Ingredients, has 2 fields.

Ingredients:
IngredientId autoNumber
Ingredient text

my expression looks like:
DoCmd.RunSQL "Insert INTO Ingredients(Ingredient) Values ('" &
Me.Ingredient & "')"

when i run it it says it is going to append 1 row. i click ok then i get an
error which says

1 row was not added due to a key violation. can you tell me what is wrong.
i assumed that i would not insert the IngredientId because it was an
autoNumber type.


thanks
brian

 

[Douglas J. Steele]

Do you have an index on Ingredient perhaps?

You might try using

CurrentDb.Execute "Insert INTO Ingredients(Ingredient) Values ('" &
Me.Ingredient & "')", dbFailOnError

That should raise a trappable error that might give you more details.

As well, check exactly what the SQL that's being generated is

Dim strSQL As String

strSQL = "Insert INTO Ingredients(Ingredient) Values ('" & Me.Ingredient &
"')"
Debug.Print strSQL

and see whether anything looks wrong with the SQL. Try running it manually
as a query and see what happens.

 

[Jerry Whittle]

Your code worked like a champ for me UNTIL I set the Ingredient field to
Indexed: Yes (No Duplicates). Then I got your error message. Check the
properties for the Ingredient field.

 

[Jerry Whittle]

I Brian,

You can have the index, but just make it duplicates allowed.

Try putting a , -1 or , True at the end of your SQL statement. This will
cause it to use a transaction and commit the changes. Actually you shouldn't
have to as True is assumed when you leave that arguement blank.

It things still don't work, consider changing it to the Execute Method to
run the SQL. If you do, make sure that you nest the Execute method inside a
transaction. Use the BeginTrans method on the current Workspace object, then
use the Execute method, and complete the transaction by using the CommitTrans
method on the Workspace. This saves changes on disk and frees any locks
placed while the query is running.

Also instead of using DoMenuItem, which can be flakey, do a
DoCmd.RunCommand acCmdRefresh instead.

Actually a refresh might not be right. You may need to do a Requery to get
the latest records.

Me.Requery

 

[brianv]

Jerry

thanks for the info. i had just figured that out from someone else posting.
i fixed that by removing the index on Ingredient but the problem got very
confusing then. i posted athe new question but here is what it is.


i am having trouble insert a rcd into a tbl using an expression on dbl click

Tbl: Ingredients
IngredientId autoNumber Primary Key
Ingredient text

These is the starting data in the tbl:
IngredientId Ingredient
10 salt
14 butter
15 milk


on a form i have a Text Box Named IngredientTB
i type data into the text box, onion, and then dbl click it.

event dbl click starts an express which looks like this:

Private Sub IngredientTB_DblClick(Cancel As Integer)
DoCmd.RunSQL "Insert INTO Ingredients(Ingredient) Values ('" &
Me.IngredientTB & "')"

Rem Refreshes form data
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

End Sub


when i do this a rcd gets added but the Ingredient value is blank.

here is the data now:
IngredientId Ingredient
10 salt
14 butter
15 milk
24



then i add another Ingredient like flour. after dbl clicking the row gets
added. now if i look at the tbl i still have the blank Ingredient and now i
have onion also.

here is the data now:
IngredientId Ingredient
10 salt
14 butter
15 milk
24
25 onion


it is like 1 value behind. i have no idea what could be going on.


actually i want the index and will readd it once i fix this problem.

 

[brianv]

thanks, i got it to work. the data in the text box did not get set until i
hit enter. therefore when i typed into the empty text box and then dbl
click, the variable was still set to blank. so a blank got inserted. if i
typed, hit enter then dlb click it worked. i decieded to just type and then
click a button instead of dbl click the field. this worked better.
--
thanks
brian

Do you have an index on Ingredient perhaps?

You might try using

CurrentDb.Execute "Insert INTO Ingredients(Ingredient) Values ('" &
Me.Ingredient & "')", dbFailOnError

That should raise a trappable error that might give you more details.

As well, check exactly what the SQL that's being generated is

Dim strSQL As String

strSQL = "Insert INTO Ingredients(Ingredient) Values ('" & Me.Ingredient &
"')"
Debug.Print strSQL

and see whether anything looks wrong with the SQL. Try running it manually
as a query and see what happens.