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

I was understanding that this is an unbound form.

It is a little ambiguous.  I was assuming the reference to 'a form with two fields on it' (fields not controls, note) meant it was bound, but maybe it's just an imprecise use of the word fields.  Hopefully the OP will enlighten us one way or the other.

Answer 4

As, if I understand your original post correctly,  this code being executed in the module of a form bound to the table

I was understanding that this is an unbound form. But I was assuming that the OP was looking in the table directly. If the form is unbound then the added record will never show in the form. It will only show in the table, or if other code is used to retrieve the record to display in the form. If the form is bound, then there is no reason to use this code as it will create duplicates in the table.

The question about confirmation (Insert 1 row) message is important. That message should display given the code being shown.