Auto Entry Error

[Nylex]

I am having a problem with making a new entry
The following trial run worked:
strSQL = "Insert Into OrderDetails (OrderID, ProductID, UnitPrice)
values (123456, 2, 99.00)"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
This doesn’t
strSQL = "Insert Into OrderDetails (OrderID, ProductID, UnitPrice)
values (Me![OrderNo], Me![CleanDesc] , Me![InvAmount)"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
I even took out Me! – still doesn’t work

 

[Tom Wickerath]

Hi Nylex,

You can always insert a debug.print statement to help troubleshoot. Then
examine the result by opening the Immediate Window (Ctrl G).

strSQL = "Insert Into OrderDetails (OrderID, ProductID, UnitPrice)
values (Me![OrderNo], Me![CleanDesc] , Me![InvAmount)"

Debug.Print strSQL

I haven't tested a similar insert statement, but I suspect that you will
need to read the field values into variables, and use those instead. Use the
appropriate variable types (I guessed at Long, String and Currency).
Something like this:

Dim lngOrderNo As Long
Dim strCleanDesc As String
Dim curUnitPrice As Currency

lngOrderNo = Me.[OrderNo]
strCleanDesc = Me.[CleanDesc]
curUnitPrice = Me.[InvAmount]

strSQL = "INSERT INTO OrderDetails (OrderID, ProductID, UnitPrice) " _
& "VALUES (lngOrderNo, strCleanDesc, curUnitPrice)"

CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded


Additional notes:
1.) I recommend using the dot (period) notation instead of the bang (!)
notation. This way, the compiler will catch a naming error when you click on
Debug > Compile. Otherwise, with the bang notation, any errors in control or
field references will only be detected at run-time.

2.) I like to capitolize the SQL keywords such as INSERT INTO. Your choice.

3.) While creating this reply, I think I just noticed the problem. Your
original SQL statement does not include a closing square bracket after the
last term:

Me![InvAmount)"

should be:

Me![InvAmount])"

or, better yet:

Me.[InvAmount])"


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tony Toews [MVP]]

strSQL = "Insert Into OrderDetails (OrderID, ProductID, UnitPrice)
values (Me![OrderNo], Me![CleanDesc] , Me![InvAmount)"

should be

values (" & Me![OrderNo] & ", " & Me![CleanDesc] & ", " & Me![InvAmount] & ")"

I use this technique lots.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tom Wickerath]

PS.
Looks like Tony got the correct form, by concatenating the pieces together.
Had you tried my form, and included the Debug.Print strSQL statement, it
should have been obvious what was going on.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html