Syntax for Insert into VBA access

Question

I have an unbound form that I use to add records to a table. I am just using the name of the field in the values() list without any """" or & and it seems to work fine. Is there a problem with this syntax?

DoCmd.RunSQL "INSERT into Patient2 (LastName, FirstName, MiddleInitial, SSN, MRN, DOB, GenderID, RaceID, HispanicorLatino)" & _
        "VALUES (LastName, FirstName, MiddleInitial, SSN, MRN, DOB, GenderID, RaceID, HispanicorLatino)"

Answer 1

Consider this a bug: the sql is only working because of some coincidences. Change the name of a textbox on your form or move the button that most likely holds the DoCmd to another form and your code will break. I'm going to explain in some detail what is going on and then I'll suggest what to do based on the two types of Access users I think might have walked into this situation.

to get here you had to select a table and then hit create form on the ribbon. Then, as you wrote, you unbound the form (removed the recordsource). At that point all of the form text boxes were probably showing the $NAME error so you unbound them as well. Next as you tried to set up the form to insert data so you added a button with that DoCmd to run the sql.

When Access auto-generates a form it defaults to naming the textbox bound to a table by the fields name. You can infer what I think about that. So, in your table you have FirstName the field and in your form you have FirstName the textbox.

In the code behind of the form all the form' properties are in scope. This means code in a button on a form has access to all the forms textboxes. So, through DoCmd Access starts to parse the SQL. in the SELECT part of the statement Access looks for the appropriate table fields so FirstName scopes to the table field. But in the VALUES part Access expects Parameters and variables so FirstName the textbox is in scope.
Someone new to Access could run into this bug: One of the big advantages of Access is rapid form development. Simply click on a table and hit create form like you have already done. However, I would not call these forms user-friendly. It takes some time to figure out how to use the automatic forms, but the new Access user should start with the automatic forms for speeds sake. Here is a link to an answer where I ranted about this in too little space: Database to keep track of inventory/ live stock

A more experienced user could have been trying to create a form to enter multiple records at once. Here is a link to an answer where I ranted about that: populating multiple fields in subform using combobox and AfterUpdate Event Prodcedure