CODEWITHSUNDEEP
mysqlsqlsql-servervba
Athemoe
Athemoe

Reputation: 5

How do I add multiple fields in the same row in SQL (ms Access)?

I spent two days on this and I still can't seem to solve it.

This is my current code in VBA that works, but for one field only. I tried to modify it to make it work for multiple fields but failed in doing so.

Dim SQL as String

SQL = "INSERT INTO tblProviders (Provider) " _
& "VALUES(" _
& "'" & Me!txtProvider & "' ) "

DoCmd.RunSQL

Where tblProviders is my table of choice, Provider is my field and txtProvider is where the user inserts the information.

If I do the following I get errors:

Dim SQL As String

SQL = "INSERT INTO tblProviders (Provider, Adress) VALUES (" & Me.txtProvider & "," & Me.txtAdress & ")"

DoCmd.RunSQL SQL

The following doesn't work either as it just makes 2 new rows instead of adding them all to the same row:

Dim SQL1 as String

SQL1 = "INSERT INTO tblProviders (Provider) " _
& "VALUES(" _
& "'" & Me!txtProvider & "' ) "

DoCmd.RunSQL SQL1

Dim SQL as String

SQL2 = "INSERT INTO tblProviders (Provider) " _
& "VALUES(" _
& "'" & Me!txtAdres & "' ) "

DoCmd.RunSQL SQL2

Upvotes: 0

Views: 954

Answers (1)

iDevlop
iDevlop

Reputation: 25272

When you build your string, you need to plan for the quotes in the final SQL statement. Try this: 

SQL = "INSERT INTO tblProviders (Provider, Adress) VALUES ('" & _
         Me.txtProvider & "', '" & Me.txtAdress & "')"  
'print the result for control
debug.Print SQL

I use a single quote ' within the double quotes ", like this "'Hi'".
One can also double the inside double quotes, like this """Hi""" , but I find it unreadable.

Upvotes: 2

Related Questions