Hello All,
I am very new to excel, VBA, and SQL although I have had programming experience, it has been a long time ago.
I have created a form in excel to be an invoice. I have a MS SQL database that contains the customer info.
I have completed the portion of the code that will search the db with the key of [Phone] and will return the information and populate the form if it is found or return a msgbox if not. This works well.
I have been trying to get another Sub to work that will again search on the key [Phone] and when found then UPDATE the row.
here is the code…I am getting a Run-time error ‘-2147217900(80040e14) Automation error.
Could I get a few of you to check it over and see if you can spot what I am not doing correctly?
Private Sub CommandButton3_Click()
‘to Update or Insert new into the form
‘ Create a connection object.
Dim cnPMCustomers As ADODB.Connection
Set cnPMCustomers = New ADODB.Connection
‘ Provide the connection string.
Dim strConn As String
‘Use the SQL Server OLE DB Provider.
strConn = “PROVIDER=SQLOLEDB;”
‘Connect to the PMCustomers database on the local server.
strConn = strConn & “DATA SOURCE=(local);INITIAL CATALOG=PMCustomers;”
‘Use an integrated login.
strConn = strConn & ” INTEGRATED SECURITY=sspi;”
‘Now open the connection.
cnPMCustomers.Open strConn
‘ declare the variable, strPhoneNum as a string
Dim strPhoneNum As String
‘ Direct variable name where to get value
strPhoneNum = ActiveWorkbook.Sheets(“Sales Invoice”).Range(“B10”).Value
Debug.Print (strPhoneNum)
‘ declare strSQL as a string
Dim strSQL As String
‘ create the columnname to columnvalue pairs in the UPDATE statement using
‘ the passed parameter PhoneNum in the WHERE statement
strSQL = “UPDATE PMCustomers.CUSTINFO SET ”
strSQL = strSQL & “Name = ‘” & Worksheets(“Sales Invoice”).Range(“B9”).Value & “‘, ”
strSQL = strSQL & “CompanyName = ‘” & Worksheets(“Sales Invoice”).Range(“E9”).Value & “‘, ”
strSQL = strSQL & “Phone = ‘” & Worksheets(“Sales Invoice”).Range(“B10”).Value & “‘, ”
strSQL = strSQL & “CellPhone = ‘” & Worksheets(“Sales Invoice”).Range(“E10”).Value & “‘, ”
strSQL = strSQL & “BillToAddress1 = ‘” & Worksheets(“Sales Invoice”).Range(“B13”).Value & “‘, ”
strSQL = strSQL & “BillToAddress2 = ‘” & Worksheets(“Sales Invoice”).Range(“B14”).Value & “‘, ”
strSQL = strSQL & “BillToCity = ‘” & Worksheets(“Sales Invoice”).Range(“B15”).Value & “‘, ”
strSQL = strSQL & “BillToState = ‘” & Worksheets(“Sales Invoice”).Range(“B16”).Value & “‘, ”
strSQL = strSQL & “BillToZip = ‘” & Worksheets(“Sales Invoice”).Range(“B17”).Value & “‘, ”
strSQL = strSQL & “Country = ‘” & Worksheets(“Sales Invoice”).Range(“B18”).Value & “‘, ”
strSQL = strSQL & “ShipToAddress1 = ‘” & Worksheets(“Sales Invoice”).Range(“F13”).Value & “‘, ”
strSQL = strSQL & “ShipToAddress2 = ‘” & Worksheets(“Sales Invoice”).Range(“F14”).Value & “‘, ”
strSQL = strSQL & “ShipToCity = ‘” & Worksheets(“Sales Invoice”).Range(“F15”).Value & “‘, ”
strSQL = strSQL & “ShipToState = ‘” & Worksheets(“Sales Invoice”).Range(“F16”).Value & “‘, ”
strSQL = strSQL & “ShipToZip = ‘” & Worksheets(“Sales Invoice”).Range(“F17”).Value & “‘, ”
strSQL = strSQL & “ContactPhone = ‘” & Worksheets(“Sales Invoice”).Range(“F18”).Value & “‘, ”
strSQL = strSQL & “PaymentType = ‘” & Worksheets(“Sales Invoice”).Range(“A21”).Value & “‘, ”
strSQL = strSQL & “CCNumber = ‘” & Worksheets(“Sales Invoice”).Range(“C21”).Value & “‘, ”
strSQL = strSQL & “CCExpireDate = ‘” & Worksheets(“Sales Invoice”).Range(“E21”).Value & “‘, ”
strSQL = strSQL & “Email = ‘” & Worksheets(“Sales Invoice”).Range(“C23”).Value & “‘”
strSQL = strSQL & ” Where Phone = ‘” & strPhoneNum & “‘”
Debug.Print (strSQL)
cnPMCustomers.Execute strSQL
‘ close connection
cnPMCustomers.Close
End Sub
Thank you in advance as this is making me crazy!!
Rhonda
