dbs.Execute being "ignored" by access

[Design by Sue]

I have created an insert statement in code that is to run on a button click. The code is to add the information from the form to a table. Simple enough - except no matter how I write the code either using dbs.execute or DoCmd.RunSQL the code I write will compile but will not be run. I have put a stop in the code and stepped through it and I see that it does run through the code, but with no results. I have entered a typo in the code and the complier tells me there is an error. BUT if the error is in the name of the table or field or form, when I click the button, access runs through the code with no compliant but does nothing. I know it is running through it completely because the last line is to "go to next record" on the form and it does that. I have also put a message box in right after the insert code and that does produce the message box.

I am pulling my hair out on this because I just don't understand why this could be happening.

Werd question I know but anyone have any idea what is going on??

Sue

 

[vbaInet]

Perhaps you've got some error handling that's absorbing the error.

Can we see the full code?

 

[Rx_]

Here is a great discussion on your question:
http://www.access-programmers.co.uk/forums/showthread.php?t=200670

docmd.setwarnings true
This is also something to look at (part of the link above).

 

[Design by Sue]

There are no warnings given, the code is simply ignored.

Code below. I am trying to enter the letters OTO in the OTO column, the ID is a number and the Description is text.

Dim dbs As DAO.Database, strSQL As String
Set dbs = CurrentDb
strSQL = "INSERT INTO OTOTBL (ID, Description, OTO) VALUES (Me.ID, Me.Description, 'OTO')"
dbs.Execute strSQL, dbFailOnError

 

[vbaInet]

Here:

strSQL = "INSERT INTO OTOTBL (ID, Description, OTO) VALUES (" & Me.ID & ",'" & Me.Description & "', 'OTO')"

I'm guessing that ID is a Number.

However, you didn't show us the full code though. I wanted to see the entire sub. Test the above first anyway.

 

[Design by Sue]

Thought you might mean the entire button code so here that is. The only additional code in this is to go to the next record on the form.


Private Sub OTO_Click()

On Error GoTo OTO_Click_Err

On Error Resume Next


Dim dbs As DAO.Database, strSQL As String
Set dbs = CurrentDb
strSQL = "INSERT INTO OTOTBL (ID, Description, OTO) VALUES (Me.ID, Me.Description, 'OTO')"
dbs.Execute strSQL, dbFailOnError

DoCmd.GoToRecord , , acNext





OTO_Click_Exit:
Exit Sub

OTO_Click_Err:
MsgBox Error$
Resume OTO_Click_Exit

End Sub

 

[vbaInet]

As mentioned in my first post, you may have error handling that's "absorbing" the error message. Remove the On Error Resume Next completely.

 

[vbaInet]

And also change that line of code to what I gave you.

 

[ino_mart]

Your SQL-command is incorrect. Assuming ID is defined as a numeric field and Description as text, you must use

 strSQL = "INSERT INTO OTOTBL(ID, Description, OTO) VALUES(" & Me.Id & ",'" & Me.Description & "', 'OTO')"

This results in e.g.
INSERT INTO OTOTBL(ID, Description, OTO) VALUES(6,'test', 'OTO')

Note: I don't know your full code, but if the ID is the Primary Key and it already exists in your database, your INSERT-command fails. Furthermore, if ID is an autonumeric field it must not be in the insert-command

 strSQL = "INSERT INTO OTOTBL(Description, OTO) VALUES(Me.Description & "', 'OTO')"

Last but not least, it is advised to put field-names between paratheses to avoid they are interpret as system commands

 strSQL = "INSERT INTO OTOTBL([ID], [Description], [OTO]) VALUES(" & Me.Id & ",'" & Me.Description & "', 'OTO')"

 

[Design by Sue]

Thank you for all of your responses - You have solved the problem - The apostrophes were the problem (and I removed the error line also)

FYI: The ID is not an auto number in the table I am saving to (it is an autonumber from a different table that identifies the package that the item described in being put into. So in this instance it is just a number.

You are all so fantastic - thank you over and over!

 

[vbaInet]

Good to hear Sue.

Last but not least, it is advised to put field-names between paratheses to avoid they are interpret as system commands

 strSQL = "INSERT INTO OTOTBL([ID], [Description], [OTO]) VALUES(" & Me.Id & ",'" & Me.Description & "', 'OTO')"

Square brackets are required for only two cases, if your field name contains spaces (or special characters) and if you have used a reserved keyword to name a field (i.e. [Date]). Obviously both cases which one should avoid.