Using unbound form controls to populate append query

Have been all over for an answer to this, striking out all over!

I have an Access 2010 db with a table, the table has 3 fields: Field1, Field2, Field3.
I have an unbound form used for data entry with 3 unbound text boxes, Field1, Field2, Field3.

I want to use the values in the text boxes to append a new record to the existing table. Tried building the sql string like so:

sSQL = "INSERT INTO tblOne ( Field1, field2, field3 )" & vbLf
sSQL = sSQL & " VALUES ('" & Me.Field1.Value & "', '" & Me.Field2.Value & ", " & Me.Field3.Value & ");" & vbLf
'sSQL = sSQL & " FROM forms!tblOne"

Open in new window

I'm not very good at sql, but intuitively the 3d line of the sSql construct (the "FROM" line referring to a form name) looks wrong. But even going without it, I get a syntax error 3075.

Thanks in advance for helping lead me back to the beaten path on this one.
iamdaedalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
You don't need the FROM clause.

The problem is missing quotes.

Try this.
sSQL = "INSERT INTO tblOne ( Field1, field2, field3 ) "
sSQL = sSQL & " VALUES ('" & Me.Field1.Value & "', '" & Me.Field2.Value & "', '" & Me.Field3.Value & "') "

Open in new window


Note if any of the fields are numeric you don't need quotes.

So if Field3 was numeric you would use this.

sSQL = "INSERT INTO tblOne ( Field1, field2, field3 ) "
sSQL = sSQL & " VALUES ('" & Me.Field1.Value & "', '" & Me.Field2.Value & "', " & Me.Field3.Value & ") "

Open in new window

0
jerryb30Commented:
sSQL = "INSERT INTO tblOne ( Field1, field2, field3 )"
sSQL = sSQL & " VALUES ('" & Me.Field1 & "', '" & Me.Field2 & "', '" & Me.Field3  & "')"
assuming field values are all text and the form is open
0
jerryb30Commented:
Of course, you will need to run the SQL statement. Put a break in at that point for
debug.print sSQL
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

iamdaedalAuthor Commented:
Jerry, yours did the trick. I tried the first solution offered by imnorie but got an error about accessing the property of a control that didn't have the focus. Referring to the control name without reference to its property worked beautifully. Many, many thanks!
0
iamdaedalAuthor Commented:
Already commented not knowing I'd have this opportunity. Newbie learning the ropes...
0
NorieAnalyst Assistant Commented:
The code Jerry posted and the code I posted are practically identical.
0
iamdaedalAuthor Commented:
But like I said in my first response, referring to the .Text property of the controls threw an error about accessing the property of a control that doesn't have the focus. Referring only to the control throws no error and works beautifully.
0
NorieAnalyst Assistant Commented:
The code I posted doesn't refer to the Text property and it works fine when I test it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.