Unable to update error

[SaraO]

Hi,

I have created a database with help of others. Everything has been working great for a long time. Now all of a sudden I keep getting an error message saying "Unable to update record". Can someone please help me figure out why this started happening and how to fix it.

This is the code that seems to be causing the error.

Private Sub cmdEdit_Click()

   'Edit a customer
   'declare the variables
   Dim findvalue        As Range
   Dim cNum             As Integer
   Dim DataSH           As Worksheet
   'error handling
   On Error GoTo errHandler:
   'hold in memory and stop screen flicker
   Application.ScreenUpdating = False
   Set DataSH = Sheet2
   'check for values
   If Emp1.Value = "" Or Emp2.Value = "" Then
      MsgBox "There is not data to edit"
      Exit Sub
   End If
   'clear the listbox
   lstCustomers.RowSource = ""
   'find the row to edit
  
   Dim cDB As clsDB
   Dim strSQL As String
  
   Set cDB = New clsDB
  
   If cDB.IsOpen Then
      
    strSQL = "Update [Customers$] Set " & _
               "[Customer Number]=" & Chr(34) & Emp1a.Text & Chr(34) & ", " & "[F Name]=" & Chr(34) & Emp2.Text & Chr(34) & ", " & "[M Name]=" & Chr(34) & Emp2a.Text & Chr(34) & ", " & "[L Name]=" & Chr(34) & Emp2b.Text & Chr(34) & ", " & "[Address]=" & Chr(34) & Emp3.Text & Chr(34) & ", " & "[Address2]=" & Chr(34) & Emp3a.Text & Chr(34) & ", " & "[Prov]=" & Chr(34) & Emp3b.Text & Chr(34) & ", " & "[PC]=" & Chr(34) & Emp3c.Text & Chr(34) & ", " & _
               "[Phone Number]=" & Chr(34) & Emp4.Text & Chr(34) & ", " & "[Cell Number]=" & Chr(34) & Emp6.Text & Chr(34) & ", " & "[Email Address]=" & Chr(34) & Emp5.Text & Chr(34) & ", " & "[Mailing Address]=" & Chr(34) & Emp5d.Text & Chr(34) & ", " & "[S-Contact]=" & Chr(34) & Emp5a.Text & Chr(34) & ", " & "[S-Phone]=" & Chr(34) & Emp5b.Text & Chr(34) & ", " & "[S-Email]=" & Chr(34) & Emp5c.Text & Chr(34) & ", " & "[UBO Username]=" & Chr(34) & Emp10.Text & Chr(34) & ", " & _
               "[UBO Password]=" & Chr(34) & Emp11.Text & Chr(34) & ", " & "[Sign Up Date]=" & Chr(34) & Emp12.Text & Chr(34) & ", " & "[Res/Buss]=" & Chr(34) & Emp13.Text & Chr(34) & ", " & "[Rental Property]=" & Chr(34) & Emp14.Text & Chr(34) & ", " & "[Homeowner Name]=" & Chr(34) & Emp15.Text & Chr(34) & ", " & "[HO Primary Phone]=" & Chr(34) & Emp16.Text & Chr(34) & ", " & "[HO Secondary Phone]=" & Chr(34) & Emp17.Text & Chr(34) & ", " & _
               "[HO Email]=" & Chr(34) & Emp18.Text & Chr(34) & ", " & "[Plan]=" & Chr(34) & Emp19.Text & Chr(34) & ", " & "[Router Rental]=" & Chr(34) & Emp20.Text & Chr(34) & ", " & "[Access Point]=" & Chr(34) & Emp21.Text & Chr(34) & ", " & "[Documents Signed]=" & Chr(34) & Emp22.Text & Chr(34) & ", " & "[Contract Type]=" & Chr(34) & Emp23.Text & Chr(34) & ", " & "[Contract Term]=" & Chr(34) & Emp24.Text & Chr(34) & ", " & _
               "[Expiry Date]=" & Chr(34) & Emp25.Text & Chr(34) & ", " & "[Property Type]=" & Chr(34) & Emp26.Text & Chr(34) & ", " & "[No of Units]=" & Chr(34) & Emp27.Text & Chr(34) & ", " & "[Underground Const]=" & Chr(34) & Emp27a.Text & Chr(34) & ", " & "[Const Type]=" & Chr(34) & Emp27b.Text & Chr(34) & ", " & "[Sector]=" & Chr(34) & Emp28.Text & Chr(34) & ", " & "[Mainline]=" & Chr(34) & Emp29.Text & Chr(34) & ", " & "[Fiber No]=" & Chr(34) & Emp30.Text & Chr(34) & ", " & "[Vault]=" & Chr(34) & Emp31.Text & Chr(34) & ", " & "[Splice Case]=" & Chr(34) & Emp32.Text & Chr(34) & ", " & _
               "[MST]=" & Chr(34) & Emp33.Text & Chr(34) & ", " & "[MST-Port]=" & Chr(34) & Emp34.Text & Chr(34) & ", " & "[C-Frame]=" & Chr(34) & Emp35.Text & Chr(34) & ", " & "[C-Chassis]=" & Chr(34) & Emp36.Text & Chr(34) & ", " & "[C-Tray/Port]=" & Chr(34) & Emp37.Text & Chr(34) & ", " & "[L-Frame]=" & Chr(34) & Emp38.Text & Chr(34) & ", " & "[L-Chassis]=" & Chr(34) & Emp39.Text & Chr(34) & ", " & "[L-Tray/Port]=" & Chr(34) & Emp40.Text & Chr(34) & ", " & _
               "[D-Rack]=" & Chr(34) & Emp41.Text & Chr(34) & ", " & "[Switch]=" & Chr(34) & Emp42.Text & Chr(34) & ", " & "[S-Port]=" & Chr(34) & Emp43.Text & Chr(34) & ", " & "[Status]=" & Chr(34) & Emp44.Text & Chr(34) & ", " & "[Route Flagging]=" & Chr(34) & Emp45.Text & Chr(34) & ", " & "[Mainline Duct]=" & Chr(34) & Emp46.Text & Chr(34) & ", " & "[Mainline Fiber]=" & Chr(34) & Emp47.Text & Chr(34) & ", " & "[Drop Duct]=" & Chr(34) & Emp48.Text & Chr(34) & ", " & _
               "[Install NID]=" & Chr(34) & Emp49.Text & Chr(34) & ", " & "[Install MST]=" & Chr(34) & Emp50.Text & Chr(34) & ", " & "[Splice MST]=" & Chr(34) & Emp51.Text & Chr(34) & ", " & "[Drop Fiber]=" & Chr(34) & Emp52.Text & Chr(34) & ", " & "[Splice NID]=" & Chr(34) & Emp53.Text & Chr(34) & ", " & "[NID Type]=" & Chr(34) & Emp54.Text & Chr(34) & ", " & "[D-Fiber Length]=" & Chr(34) & Emp55.Text & Chr(34) & ", " & "[Conduit Color]=" & Chr(34) & Emp56.Text & Chr(34) & ", " & _
               "[Construction Notes]=" & Chr(34) & Emp57.Text & Chr(34) & ", " & "[Install Date]=" & Chr(34) & Emp58.Text & Chr(34) & ", " & "[Install Month]=" & Chr(34) & Emp58a.Text & Chr(34) & ", " & "[Installer]=" & Chr(34) & Emp59.Text & Chr(34) & ", " & "[Router Type]=" & Chr(34) & Emp60.Text & Chr(34) & ", " & "[MAC Address]=" & Chr(34) & Emp61.Text & Chr(34) & ", " & "[Router Location]=" & Chr(34) & Emp62.Text & Chr(34) & ", " & "[Media Converter]=" & Chr(34) & Emp63.Text & Chr(34) & ", " & "[MC Location]=" & Chr(34) & Emp64.Text & Chr(34) & ", " & _
               "[Fiber Length]=" & Chr(34) & Emp65.Text & Chr(34) & ", " & "[Fiber Placement]=" & Chr(34) & Emp66.Text & Chr(34) & ", " & "[Faceplate]=" & Chr(34) & Emp67.Text & Chr(34) & ", " & "[HD-Ticket]=" & Chr(34) & Emp68.Text & Chr(34) & ", " & "[Install Scheduled]=" & Chr(34) & Emp69.Text & Chr(34) & ", " & "[Install Notes]=" & Chr(34) & Emp70.Text & Chr(34) & ", " & "[Disconnect Date]=" & Chr(34) & Emp71.Text & Chr(34) & ", " & "[Disconnect Month]=" & Chr(34) & Emp71a.Text & Chr(34) & ", " & "[Router Returned]=" & Chr(34) & Emp72.Text & Chr(34) & ", " & _
               "[Powercord Returned]=" & Chr(34) & Emp73.Text & Chr(34) & ", " & "[Transceiver Returned]=" & Chr(34) & Emp74.Text & Chr(34) & ", " & "[Disconnect Reason]=" & Chr(34) & Emp75.Text & Chr(34) & ", " & "[Disconnect Billing]=" & Chr(34) & Emp76.Text & Chr(34) & ", " & "[Email BMJ]=" & Chr(34) & Emp77.Text & Chr(34) & ", " & "[Staff]=" & Chr(34) & Emp78.Text & Chr(34) & ", " & "[Disconnect Notes]=" & Chr(34) & Emp79.Text & Chr(34) & ", " & _
               "[Signed Up]=" & Chr(34) & Emp80.Text & Chr(34) & ", " & "[MDU Class]=" & Chr(34) & Emp80a.Text & Chr(34) & ", " & "[Building Notes]=" & Chr(34) & Emp81.Text & Chr(34) & ", " & _
               "[MDU Drop Duct]=" & Chr(34) & Emp82.Text & Chr(34) & ", " & "[MDU Drop Fiber]=" & Chr(34) & Emp83.Text & Chr(34) & ", " & "[MDU Splice ML]=" & Chr(34) & Emp84.Text & Chr(34) & ", " & "[MDU Install NID]=" & Chr(34) & Emp85.Text & Chr(34) & ", " & "[MDU Splice NID]=" & Chr(34) & Emp86.Text & Chr(34) & ", " & "[MDU Mainline]=" & Chr(34) & Emp87.Text & Chr(34) & ", " & "[No of Fibers]=" & Chr(34) & Emp88.Text & Chr(34) & ", " & "[Fiber Count]=" & Chr(34) & Emp89.Text & Chr(34) & ", " & "[P-Plan]=" & Chr(34) & Emp90.Text & Chr(34) & ", " & _
               "[MDU MST]=" & Chr(34) & Emp91.Text & Chr(34) & ", " & "[MDU Port]=" & Chr(34) & Emp92.Text & Chr(34) & ", " & "[Work Order #]=" & Chr(34) & Emp93.Text & Chr(34) & ", " & "[Field Notes]=" & Chr(34) & Emp94.Text & Chr(34) & ", " & "[Scouted]=" & Chr(34) & Emp95.Text & Chr(34) & ", " & "[TechHD]=" & Chr(34) & Emp95a.Text & Chr(34) & ", " & "[CO Rack]=" & Chr(34) & Emp96.Text & Chr(34) & ", " & "[CO Switch]=" & Chr(34) & Emp97.Text & Chr(34) & ", " & "[CO Port]=" & Chr(34) & Emp98.Text & Chr(34) & ", " & "[E-Rack]=" & Chr(34) & Emp99.Text & Chr(34) & ", " & "[E-Switch]=" & Chr(34) & Emp100.Text & Chr(34) & ", " & _
               "[MDU Install Notes]=" & Chr(34) & Emp101.Text & Chr(34) & ", " & "[Problem Status]=" & Chr(34) & Emp102.Text & Chr(34) & ", " & "[Problem Date]=" & Chr(34) & Emp103.Text & Chr(34) & ", " & "[Resolution]=" & Chr(34) & Emp104.Text & Chr(34) & ", " & _
               "[PST]=" & Val(Emp7.Text) & ", " & _
               "[PST Number]=" & Val(Emp8.Text) & ", " & _
               "[Copies]= " & Val(Emp9.Text) & _
               " Where [ID]= " & Val(Emp1.Text)
              
      If Not cDB.ExecSQL(strSQL) Then
         MsgBox "Unable to update record.", vbExclamation, "Error"
      End If
   End If
      
   'error block
   On Error GoTo 0
   Exit Sub
errHandler:
  
   'show error information in a messagebox
   MsgBox "An Error has Occurred " & vbCrLf & _
          "The error number is: " & Err.Number & vbCrLf & _
          Err.Description & vbCrLf & "Please notify the administrator"
End Sub

 

[Norie]

What we've been suggesting isn't going to stop the error popping up, it's supposed to help you find out why the error is popping up

You haven't posted code for the function/method cDB.ExecSQL, which you are calling here, but whatever is happening in that code is causing it to return a False value, hence the message box

If Not cDB.ExecSQL(strSQL) Then
    MsgBox "Unable to update record.", vbExclamation, "Error"
End If

Have you tried running the SQL in the database?