SQL Update query in VBA for Access

I have the following VBA code behind a button on a form in Access:-

Code:

---

Private Sub cmdUpdate_Addresses_Phone_Click()

'Create Variable for DebtID to be stored as refernce for table lookups
    Dim DebtID
   
    'Create Variable for SQL insert Statement
    Dim SQLUpdate As String
   
   
    'Create variables to hold values that are being inserted
    Dim Debt_ID As Long
    Dim Full_Name As String
    Dim Address_Line1 As String
    Dim Address_Line2 As String
    Dim Address_Line3 As String
    Dim Address_Line4 As String
    Dim Address_Line5 As String
    Dim Post_Code As String
    Dim Telephone_No As String
    Dim Alternative_Tel_No As String
    Dim Tel_No_Work As String
    Dim Tel_No_Other As String
    Dim Solicitor_Address1 As String
    Dim Solicitor_Address2 As String
    Dim Solicitor_Address3 As String
    Dim Solicitor_Address4 As String
    Dim Solicitor_Address5 As String
    Dim Solicitor_Postcode As String
    Dim Insurance_Address1 As String
    Dim Insurance_Address2 As String
    Dim Insurance_Address3 As String
    Dim Insurance_Address4 As String
    Dim Insurance_Address5 As String
    Dim Insurance_Postcode As String
    Dim Solicitor_Phone As String
    Dim Insurance_Phone As String
    Dim Result_ID As String
    Dim Fee As Currency
    Dim collector As String
       
   
    'Fill DebtID variable
    DebtID = Me.txtDebtID
   
    'Fill variables with data from tables or form
    If IsNull(Me.txtDebtID) Then DebtID = "" Else Debt_ID = Me.txtDebtID
    If IsNull(Me.txtCustName) Then Full_Name = "" Else Full_Name = Me.txtCustName
    If IsNull(Me.txtAdd1) Then Address_Line1 = "" Else Address_Line1 = Me.txtAdd1
    If IsNull(Me.txtAdd2) Then Address_Line2 = "" Else Address_Line2 = Me.txtAdd2
    If IsNull(Me.txtAdd3) Then Address_Line3 = "" Else Address_Line3 = Me.txtAdd3
    If IsNull(Me.txtAdd4) Then Address_Line4 = "" Else Address_Line4 = Me.txtAdd4
    If IsNull(Me.txtAdd5) Then Address_Line5 = "" Else Address_Line5 = Me.txtAdd5
    If IsNull(Me.txtPostCode) Then Post_Code = "" Else Post_Code = Me.txtPostCode
    If IsNull(Me.txtPhone1) Then Telephone_No = "" Else Telephone_No = Me.txtPhone1
    If IsNull(Me.txtPhone2) Then Alternative_Tel_No = "" Else Alternative_Tel_No = Me.txtPhone2
    If IsNull(Me.txtPhone3) Then Tel_No_Work = "" Else Tel_No_Work = Me.txtPhone3
    If IsNull(Me.txtPhone4) Then Tel_No_Other = "" Else Tel_No_Other = Me.txtPhone4
    If IsNull(Me.txtSolAdd1) Then Solicitor_Address1 = "" Else Solicitor_Address1 = Me.txtSolAdd1
    If IsNull(Me.txtSolAdd2) Then Solicitor_Address2 = "" Else Solicitor_Address2 = Me.txtSolAdd2
    If IsNull(Me.txtSolAdd3) Then Solicitor_Address3 = "" Else Solicitor_Address3 = Me.txtSolAdd3
    If IsNull(Me.txtSolAdd4) Then Solicitor_Address4 = "" Else Solicitor_Address4 = Me.txtSolAdd4
    If IsNull(Me.txtSolAdd5) Then Solicitor_Address5 = "" Else Solicitor_Address5 = Me.txtSolAdd5
    If IsNull(Me.txtSolPostCode) Then Solicitor_Postcode = "" Else Solicitor_Postcode = Me.txtSolPostCode
    If IsNull(Me.txtSolPhone) Then Solicitor_Phone = "" Else Solicitor_Phone = Me.txtSolPhone
    If IsNull(Me.txtInsAdd1) Then Insurance_Address1 = "" Else Insurance_Address1 = Me.txtInsAdd1
    If IsNull(Me.txtInsAdd2) Then Insurance_Address2 = "" Else Insurance_Address2 = Me.txtInsAdd2
    If IsNull(Me.txtInsAdd3) Then Insurance_Address3 = "" Else Insurance_Address3 = Me.txtInsAdd3
    If IsNull(Me.txtInsAdd4) Then Insurance_Address4 = "" Else Insurance_Address4 = Me.txtInsAdd4
    If IsNull(Me.txtInsAdd5) Then Insurance_Address5 = "" Else Insurance_Address5 = Me.txtInsAdd5
    If IsNull(Me.txtInsPostCode) Then Insurance_Postcode = "" Else Insurance_Postcode = Me.txtInsPostCode
    If IsNull(Me.txtInsPhone) Then Insurance_Phone = "" Else Insurance_Phone = Me.txtInsPhone
    If IsNull(Me.Combo136) Then Result_ID = "" Else Result_ID = Me.Combo136
    If IsNull(Me.txtoutfee) Then Fee = 0 Else Fee = Me.txtoutfee
    If IsNull(Me.txtCollector) Then collector = "" Else collector = Me.txtCollector
   
   
   
    'Fill SQL update statement
   
    If Me.Combo136 = "" Then
        SQLUpdate = "UPDATE tblReported SET tblReported.Full_Name='" & Full_Name & "', tblReported.Collector='" & collector & "', tblReported.Address_Line1='" & Address_Line1 & "', tblReported.Address_Line2='" & Address_Line2 & "', tblReported.Address_Line3='" & Address_Line3 & "', tblReported.Address_Line4='" & Address_Line4 & "', tblReported.Address_Line5='" & Address_Line5 & "', tblReported.Post_Code='" & Post_Code & "', tblReported.Telephone_No='" & Telephone_No & "', tblReported.Alternative_Tel_No='" & Alternative_Tel_No & "', tblReported.Tel_No_Work='" & Tel_No_Work & "', tblReported.Tel_No_Other='" & Tel_No_Other & "', tblReported.Solicitor_Address1='" & Solicitor_Address1 & "', tblReported.Solicitor_Address2='" & Solicitor_Address2 & "', tblReported.Solicitor_Address3='" & Solicitor_Address3 & "', tblReported.Solicitor_Address4='" & Solicitor_Address4 & "', " & _
                "tblReported.Solicitor_Address5='" & Solicitor_Address5 & "', tblReported.Solicitor_Postcode='" & Solicitor_Postcode & "', tblReported.Solicitor_Phone='" & Solicitor_Phone & "', tblReported.Insurance_Address1='" & Insurance_Address1 & "', tblReported.Insurance_Address2='" & Insurance_Address2 & "', tblReported.Insurance_Address3='" & Insurance_Address3 & "', tblReported.Insurance_Address4='" & Insurance_Address4 & "', tblReported.Insurance_Address5='" & Insurance_Address5 & "', " & _
                "tblReported.Insurance_Postcode='" & Insurance_Postcode & "', tblReported.Insurance_Phone='" & Insurance_Phone & "', tblReported.Fee='" & Fee & "'" & _
                "WHERE tblReported.Debt_ID='" & Debt_ID & "' ;"
    Else
        SQLUpdate = "UPDATE tblReported SET tblReported.Full_Name='" & Full_Name & "', tblReported.Collector='" & collector & "', tblReported.Address_Line1='" & Address_Line1 & "', tblReported.Address_Line2='" & Address_Line2 & "', tblReported.Address_Line3='" & Address_Line3 & "', tblReported.Address_Line4='" & Address_Line4 & "', tblReported.Address_Line5='" & Address_Line5 & "', tblReported.Post_Code='" & Post_Code & "', tblReported.Telephone_No='" & Telephone_No & "', tblReported.Alternative_Tel_No='" & Alternative_Tel_No & "', tblReported.Tel_No_Work='" & Tel_No_Work & "', tblReported.Tel_No_Other='" & Tel_No_Other & "', tblReported.Solicitor_Address1='" & Solicitor_Address1 & "', tblReported.Solicitor_Address2='" & Solicitor_Address2 & "', tblReported.Solicitor_Address3='" & Solicitor_Address3 & "', tblReported.Solicitor_Address4='" & Solicitor_Address4 & "', " & _
                "tblReported.Solicitor_Address5='" & Solicitor_Address5 & "', tblReported.Solicitor_Postcode='" & Solicitor_Postcode & "', tblReported.Solicitor_Phone='" & Solicitor_Phone & "', tblReported.Insurance_Address1='" & Insurance_Address1 & "', tblReported.Insurance_Address2='" & Insurance_Address2 & "', tblReported.Insurance_Address3='" & Insurance_Address3 & "', tblReported.Insurance_Address4='" & Insurance_Address4 & "', tblReported.Insurance_Address5='" & Insurance_Address5 & "', " & _
                "tblReported.Insurance_Postcode='" & Insurance_Postcode & "', tblReported.Insurance_Phone='" & Insurance_Phone & "', tblReported.Result_ID='" & Result_ID & "', tblReported.Fee='" & Fee & "'" & _
                "WHERE tblReported.Debt_ID='" & Debt_ID & "' ;"
    End If
                       
                   
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQLUpdate
    DoCmd.SetWarnings True
   
    MsgBox "Account with Debt ID " & DebtID & " has had it's address updated", vbOKOnly
   
    Call Reset_update_form


End Sub

---

The format of the variables match the format of each field in the table 'tblreported' (i.e. the field full_name is a text field in the table, etc.). However, when I run the code I get the following error message:-

Run-time error '3464':
Data type mismatch in criteria expression.

Bearing in mind, so far as I can see, the data types match between the variables and the table, does anyone have any idea why this is happening and how to solve the problem?

can you stop the code right after the SQL string is created and view the contents of the SQL in the immediate window. You should be able to see the value that is causing the problem. If not copy the string into a Query window and it will highlight the value that is causing the error when it tries to run.

I cant use the query window beacuse the data is coming from a form and I can't use info from forms in a query. When I use the immediate window, it shows me the values of the variables but how do I know which one is the one causing the problem??

schaefer meant to copy the fully made string (with the values inside it) that you had printed to the Immediate window (or got from the Watch window), that way it does not refer to any forms/controls.

If you can't find the problem with that, copy & paste the fully made string here, and tell us what the data types of the fields are.

I think the problem is Debt_ID. You have it defined as a long but are using it as a text string in your SQL statement.

Code:

---

   
    'Create variables to hold values that are being inserted
    Dim Debt_ID As Long
   
    'Fill DebtID variable
    DebtID = Me.txtDebtID


...
                "WHERE tblReported.Debt_ID='" & Debt_ID & "' ;"
    Else
... 
                "WHERE tblReported.Debt_ID='" & Debt_ID & "' ;"
    End If

---

If you remove the Quotes from around the variable in your sql statement it should help unless it is actually defined as a text value in the table.

Also you can get rid of this line

Code:

---

If IsNull(Me.txtDebtID) Then DebtID = "" Else Debt_ID = Me.txtDebtID

---

If DebtID is null then you probably want to abort rather than attempt an update.

Thanks Schaefer, removing the quotes from around this variable worked!!

After removing surrounded quotes of Debt_ID you may still have problem when DebtID Is Null, that will be explained in point 2. below.

To help you to write the code easier: As the code you are writting in Access VBA, Access has a built in function called: Nz(varName, ValueIfNull).
Your code will be much easier to read and shorter if you use this function.

Code:

---

1. Instead of :
    If IsNull(Me.txtCustName) Then Full_Name = "" Else Full_Name = Me.txtCustName
you can have:
    Full_Name = Nz(Me.txtCustName, "")

2. Istead of :
    If IsNull(Me.txtDebtID) Then DebtID = "" Else Debt_ID = Me.txtDebtID
you can have:
    Debt_ID = Nz(Me.txtDebtID, "")

However, DebtID is a Long, both of the above will fail the SQL when DebtID Is Null,
because the WHERE clause will become (after removing quoes [']):
    "WHERE tblReported.Debt_ID=;"
So, you need to have:
    Debt_ID = Nz(Me.txtDebtID, 0) '-- or -1 or "Null"
with that, you will have:
    "WHERE tblReported.Debt_ID=0;"

---

In case your code has to port to such as VB6 that does not have function Nz() then you can make one yourself such as:

Code:

---

Function Nz(varName As Variant, ValueIfNull as Variant) As Variant
  If IsNull(varName) Then Nz = ValueIfNull Else Nz = varName
End Function

---