0

Trying to update table in ms Access using VBA but getting failed , used multiple option but table not getting updated

Dim bod As Date
Dim assets As String
Dim emname As String
Dim ecode As String
Dim Astatus As String
      
Astatus = "SOLD"    
        
msaved = True
DoCmd.SetWarnings False            
bod = Me.datetime.Caption
price1k = Me.cmbox1_1AID.Value
emname = Me.empname.Value
ecod = Nz(Me.code.Value, 0)
        
//tried using this but not getting updated no error
DoCmd.RunSQL "UPDATE [Asset E7450 List] SET Code = '" & ecod & "',[Date & Time] ='" & bod & "' ,Status ='" & Astatus & "',[Sold To] ='" & empname & "', where Asset-ID ='" & price1k & "'"
      
// Tried using this no update no error 
CurrentDb.Execute "UPDATE [Asset E7450 List] SET Code = '" & ecod & "',[Date & Time] ='" & bod & "' ,Status ='" & Astatus & "',[Sold To] ='" & empname & "', where Asset-ID ='" & price1k & "'"
    
// Tried this method no data update no error
Dim strSQL As String
strSQL = "UPDATE [Asset E7450 List] SET Code = '" & ecod & "',[Date & Time] ='" & bod & "' ,Status ='" & Astatus & "',[Sold To] ='" & empname & "', where Asset-ID ='" & price1k & "'"
DoCmd.RunSQL strSQL
         
MsgBox " Booking Request Accepted ", vbInformation
June7
  • 19,874
  • 8
  • 24
  • 34
Amit jha
  • 17
  • 7

1 Answers1

0

If [Date & Time] is a date/time type field, use # delimiter instead of '. If field is a number type, don't use any delimiter.

Remove the comma in front of WHERE clause.

Need [ ] around Asset-ID field name because of the hyphen.

Advise not to use spaces nor punctuation/special characters in naming convention.

June7
  • 19,874
  • 8
  • 24
  • 34
  • Thanks for the respond .. i changed here plz let me know if its correct .. actually still its not getting save .. strSQL = "UPDATE [Asset E7450 List] SET Code = '" & ecod & "',[Date & Time] =#" & bod & "# ,Status ='" & Astatus & "',[Sold To] ='" & empname & "', where [Asset-ID] =" & price1k & "" – Amit jha Aug 30 '21 at 18:31
  • Still have a comma in front of WHERE. Asset-ID is a text field? – June7 Aug 30 '21 at 18:32
  • i removed that but still not getting updated – Amit jha Aug 30 '21 at 18:36
  • Don't see anything else wrong with concatenated string. Review https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access – June7 Aug 30 '21 at 18:37
  • strSQL = "UPDATE [Asset E7450 List] SET Code = '" & ecod & "',[Date & Time] =#" & bod & "# ,Status ='" & Astatus & "',[Sold To] ='" & empname & "', where [Asset-ID] =" & price1k & "" DoCmd.RunSQL strSQL MsgBox " Booking Request Accepted ", vbInformation Me.subform.Requery Me.code.Enabled = False DoCmd.SetWarnings True – Amit jha Aug 30 '21 at 18:38
  • Could use CurrentDb.Execute and don't have to deal with warnings. `Debug.Print strSQL` to see the constructed string in Immediate Window then copy/paste it into query object to test. You are still showing a comma in front of WHERE. – June7 Aug 30 '21 at 18:40
  • tried changing , CurrentDb.Execute "UPDATE [Asset E7450 List] SET Code = '" & ecod & "',[Date & Time] =#" & bod & "# ,Status ='" & Astatus & "',[Sold To] ='" & empname & ", where [Asset-ID] =" & price1k & "" if i do debug.print do not get any output – Amit jha Aug 30 '21 at 18:49
  • Why do you not understand? Again, there is a comma in front of WHERE, remove it. `& ", where` should be `& " where`. – June7 Aug 30 '21 at 18:50
  • would request your assistance, let me know how can i share you all the details so you can help me to find why this is not getting update .. please – Amit jha Aug 30 '21 at 18:52
  • You could post in a forum that allows attaching files and provide db for analysis. Fix the comma as instructed. I can see nothing else wrong with SQL. – June7 Aug 30 '21 at 18:53
  • CurrentDb.Execute "UPDATE [Asset E7450 List] SET Code = '" & ecod & "',[Date & Time] =#" & bod & "# ,Status ='" & Astatus & "',[Sold To] ='" & empname & "' where [Asset-ID] =" & price1k & "" – Amit jha Aug 30 '21 at 18:54
  • when i used debug.print strSQL this is the response ... UPDATE [Asset E7450 List] SET Code = 'AO526',[Date & Time] =#31-08-2021 12:26:24 AM# ,Status ='SOLD',[Sold To] ='AKASH JAIN where [Asset-ID] =MGS-BLR-L003 – Amit jha Aug 30 '21 at 18:57
  • it shows correct data but not getting updated in table – Amit jha Aug 30 '21 at 18:58
  • Well, that output shows missing apostrophe delimiters for `[Sold To] ='AKASH JAIN where [Asset-ID] =MGS-BLR-L003`. Your latest version of SQL code does not have them. – June7 Aug 30 '21 at 19:05
  • Thank you so much for the assistance .. finally its updating the Table . Thank you lot again ... may i know your good name & location. – Amit jha Aug 31 '21 at 03:04
  • Name is June in Alaska. If answer resolved issue, please mark it accepted. – June7 Aug 31 '21 at 03:14
  • please help me with this Dim dupsql dupsql = "SELECT * FROM [BookingTable]WHERE Code = ='" & ecode & "'" DoCmd.FindRecord dupsql, , True, , True MsgBox " The Entered Code is already in Use! ", vbInformation – Amit jha Aug 31 '21 at 05:14
  • would like to validate if data is available in table so it should confirm – Amit jha Aug 31 '21 at 05:15
  • Could use DLookup instead of opening a recordset. This is a new issue and should start a new question. – June7 Aug 31 '21 at 05:30
  • yes i have posted in new quest .. request your assistance – Amit jha Aug 31 '21 at 05:40