SQL Update query in Access 2007

[cornishbrummy]

I have a form in my Access database which is used to change customers addresses and phone numbers. Once the info is updated on the form, users will click a command button on the form to update the table in Access. This command button has some VB code behind it. To update the table, I'm using an SQL update statement. However, when I step through this it says it's going to update all the row on the table which I obviously don't want - I only want it to update the relevant row on the table. Also, if I continue through the code it doesn't update any of the records on the table anyway!! My code is below:-

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
    
    
    '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
    
    
    
    'Fill SQL update statement
    
    SQLUpdate = "UPDATE tblReported SET tblReported.Full_Name=Full_Name, 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 " & _
                "WHERE tblReported.Debt_ID=Debt_ID ;"
                
                        
                    
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQLUpdate
    DoCmd.SetWarnings True
    
    MsgBox "Account with Debt ID " & DebtID & " has had it's address updated", vbOKOnly

Does anyone know what the problem is??

[brucevde]

You need to concatenate the value of the variables to the sql statement. As it is now the database fields are being updated with themselves.

SQLUpdate = "UPDATE tblReported SET tblReported.Full_Name='" & Full_Name & "', tblReported.Address_Line1='" & Address_Line1 & "',...

[cornishbrummy]

Hi,
Thanks for that - this works in exactly the way I want it to and has finally ended my frustration!!!

Thanks

[cornishbrummy]

I have one other problem - when I'm trying to pull back information onto the form from the table. The user enters an account number then hits a button to retrieve the information from the table. When I do this it returns the following error message:-

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

Below is the first few lines of code behind this button:-

Dim DebtID As String
DebtID = Me.txtDebtID

If IsNull(Me.txtDebtID) = True _
Then LResponse = MsgBox("Please enter a Debt ID.", vbCritical, "Error")

If LResponse = vbOK Then Exit Sub



'Set the individual fields that are displayed on the form

'fields taken directly from tblImport table

Me.txtCustName = DLookup("Full_Name", "tblReported", "Debt_ID = " & DebtID) Me.txtAdd1 = DLookup("Address_line1", "tblReported", "Debt_ID = " & DebtID)
Me.txtAdd2 = DLookup("Address_Line2", "tblReported", "Debt_ID = " & DebtID)
Me.txtAdd3 = DLookup("Address_line3", "tblReported", "Debt_ID = " & DebtID)

The line it falls over on is Me.txtCustName = .......
I'm assuming it's to do with the end part of this where it's trying to compare Debt_ID (from the table) with DebtID (my variable). The variable is a string and the field from the table is in text format.

Any ideas why, then, that it's falling over??

[brucevde]

Database text fields need to be delimited

Me.txtCustName = DLookup("Full_Name", "tblReported", "Debt_ID = '" & DebtID & "'")