Insert does not work in VBA and SQL (1 Viewer)

[DemonDNF]

This is from a SAVE RECORD button:

Private Sub cmdSaveNewValue_Click()
10  Dim db As Database
20  Dim strSql As String
 
'       Add new tblValues entry for New Value textbox.
30  Set db = CurrentDb

40  strSql = "INSERT INTO tblValues (SpecID, Value) VALUES (" & _
             txtKey & ", '" & txtNewValue & "');"

50  db.Execute (strSql), dbFailOnError
60  db.Close
70  Me.Requery
80  cmdCancelNewValue_Click
End Sub

I printed strSql using debug.print:

INSERT INTO tblValues (SpecID, Value) VALUES (31, 'b');

It does not work in VBA, it does not work in SQL in a query design view; INSERT SYNTAX ERROR.

But I can insert 31 and b manually in the database and I can insert 31 and b using a continuous form.

SpecID is long (links to autonumber in another table), Value is text.

I'm stumped. I copied that insert statement from another routine that works without problems.

Robert

 

[DemonDNF]

Here is the manual insert:

 

[spikepl]

Before someone does you a great disservice by providing the solution: Use the query-builder to the full - this is the built-in free syntax checker.

The start of any query is SELECT. Make a select query to get the fields in question. Once that works, click on one of the buttons and thereby convert it to insert/append or whatever query. Then look at the SQLview and there you have it. Also, look at the list of proscribed names: http://allenbrowne.com/AppIssueBadWord.html

 

[plog]

I'm saving this post and referring to it from now on.

First, to the future people I am referring: this is an example of why you need to be careful and specific when naming your fields and tables.

Now for you Demon: 'Value' is a reserved word in Access:

http://support.microsoft.com/kb/286335

When you use words on this list as table/query/field names, you will have issues when you write code. The system sees 'Value', thinks its a reserved word and treats it as such. It is unable to determine it is the name of your field.

The way around this is to put it in brackets ([Value]). The correct way is to not use 'Value' but to be more specific in your naming (e.g. SalesValue, UserValue), prefix 'Value' with what the value represents.

 

[DemonDNF]

..The way around this is to put it in brackets ([Value]). The correct way is to not use 'Value' but to be more specific in your naming (e.g. SalesValue, UserValue), prefix 'Value' with what the value represents.

----
Edit: That's the problem, Value is the correct technical term for this field.

And I can't prefix it, tblValue is linked to tblSpecs which is linked to tblParts. Parts can be anything; resistors, capacitors, transistors and who knows what electronic component that I have not used yet. Types of Specs are endless, pick up any datasheet. Now imagine the variety of Values for these Specs.
---
Edit 2: Oh, and I already have a linking field called SpecValue in another table. It's less confusing to use brackets in this case.
---

Square brackets worked like a charm. I had a nagging feeling Value was a reserved word, but didn't understand why it has been working for weeks now; in forms, reports and VBA.

I figure it must be the first time in SQL. If I have to use square brackets around Value in SQL, I can live with that.

But yes, I do understand the importance of staying away from reserved words (Basic, Fortran, Rpg, Assembler, Cobol, C, Java and a few others I'm forgetting). It's just that Access uses so many everyday terms. This is the first one I hit.

Having that list will come in handy though now that I have it.

Robert