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

Do you get the confirmation dialogue "You are about to insert 1 row etc"?  If so are you confirming the insert?

Assuming the answers to both of the above are 'yes', have you examined the table itself to see if the new row has been inserted?  As, if I understand your original post correctly,  this code being executed in the module of a form bound to the table the form will not include the new row during its current session until requeried by adding the line:

Me.Requery

to the code.

Answer 4

I don't see anything wrong with that. Try pasting the SQL into SQL View of Query design mode and see if you get any messages.

Answer 5

I think we crossed paths on my last post. After posting it, I edited it to show the results. Please check my last post again.