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]

Have you tried executing the SQL from the code manually in the database?

That might give you more insight on what the problem is.

 

[Special-K99]

Display the value of strSQL, examine the SQL statement generated and locate the error.
If it worked before and you haven't changed anything then it can only be the data, not your code

 

[SaraO]

I'm fairly new to VBA and had a lot of help with this so I'm not sure how to do that.

 

[Special-K99]

Dunno who you're replying to.
If it's me then just use a MsgBox to display the data

 

[SaraO]

Dunno who you're replying to.
If it's me then just use a MsgBox to display the data

I was replying to you. Sorry I'm still learning how this forum works.

 

[Norie]

What I'm suggesting is the following:

1. Output the SQL from the code to the Immediate Window (CTRL+G) by adding Debug.Print strSQL to the code.
   
   
2. Copying the SQL statement that's been output.
   
   
3. Opening the database you are trying to run the SQL code against.
   
   
4. Running the copied SQL in that database manually.

For 1 the suggested code would go before this.

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

 

[SaraO]

What I'm suggesting is the following:

1. Output the SQL from the code to the Immediate Window (CTRL+G) by adding Debug.Print strSQL to the code.
   
   
2. Copying the SQL statement that's been output.
   
   
3. Opening the database you are trying to run the SQL code against.
   
   
4. Running the copied SQL in that database manually.

For 1 the suggested code would go before this.

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

This code is in a userform. Not sure how to run it manually. Stuck on step 4.

 

[Norie]

If you add the code I suggested in 1 then next time you get the error press CTRL+BREAK.

You should now be in break mode and you should see the SQL statement in the Immediate WIndow (CTRL+G).

If you do copy it, open your database and try and run it manually - exactly how to do that is hard to tell without knowing which database you are using.

 

[SaraO]

No matter what I do, error still pops up.