SQL INSERT INTO USING VBA PROBLEM

Question

I am trying to learn SQL in Access. Currently I have a form with two fields on it: Fname & Lname. When I click button to update the records I want to run sql vba code in a module to update the table (tblDemo) with the values in the form fields. I have been unable to get it to work. Below is the code I have been working with.

"INSERT INTO [tblDemo] ([Fname], [Lname]) " &_

         "VALUES("'" & Me.Fname & "'," & "'" & Me.Fname & "';"

Suggestions are vry much appreciated.

Answer 1

Ken, in answer to your previous question, yes I was getting the Update 1 row msg. Thanks. ScottGem, thanks to you also.

 I included the Me.Requery in the code module and still had a problem. But, I figured out that having the table open when I ran the module didn't display the information until I had closed it and then re-opened it, then I saw that the data was properly inserted into a new row.

Again, thanks to all.

Answer 2

I'd also recommend that you adopt the representation of the literal quotes character by a pair of contiguous quotes characters, as in Vanderghast's second example, rather than by a single quote character:

"INSERT INTO [tblDemo] ([Fname], [Lname]) " &_

         "VALUES(""" & Me.Fname & """,""" & Me.Lname & """)"

Otherwise you'll run into problems with names like mine in its original non-Anglicized form, O'Siridean, and those of other Irish bogtrotters like me.

The closing semi-colon is unnecessary when building an SQL statement as a string expression in VBA.

Answer 3

paste the resulting SQL statement into a post.

Answer 4

Scottgem,

I did run the Debug.Print and all the values are there, no errors at all. But it still won't update the data to the table.

Here were the results:

INSERT INTO [tblDemo] ([Fname], [Lname], [Address], [City], [State], [Zip]) VALUES ("Tom","Thumb","567 Easy St" , "Portland" , "OR" , "97334")

Answer 5

Try using the Debug.Print strSQL before you run it. Then look in the Immediate Window to see what the statement generated was.