Unable to update error

S

SaraO

New Member
Joined
Feb 4, 2019
Messages
21
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.

VBA Code: 
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
 
Sort by date Sort by votes
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
VBA Code: 
If Not cDB.ExecSQL(strSQL) Then
    MsgBox "Unable to update record.", vbExclamation, "Error"
End If

Have you tried running the SQL in the database?
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
You must log in or register to reply here.

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top