-1

I was trying to create script that will do soft deletion. Apparently, there was a run-time error I encountered after running the script. Please help. 

Private Sub cmdDelete_Click()
Dim db As DAO.Database
    'delete record
    'check existing selected record
    'If Not (Me.frmGatewaySub.Form.Recordset.EOF And Me.frmGatewaySub.Form.Recordset.BOF) Then
        'confirm deletion

        If MsgBox("Are you sure you want to soft delete this?", vbYesNo) = vbYes Then
            'soft delete now
            Set db = CurrentDb

            Call db.Execute( _
                "Update dbo_gateway" & _
                " set deleted_at= now()" & _
                " where id=" & Me.txtID.Value, dbSeeChanges)

            'clear text box
            cmdClear_Click
            Me.frmGatewaySub.Form.Requery

        End If

    'End If

End Sub
Ted
  • 3
  • 3
  • 1
    [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) – Andre Aug 08 '18 at 07:54
  • Your image link won't open for me. Post error message as text in the question. For a start, the SQL needs a space in front of `set` and `where`. No need for Call nor the parens for `db.execute`. Why are you referencing Tag property? Why are you referencing the form recordset? Edit question with more info. – June7 Aug 08 '18 at 08:36
  • Are you sure you want to use `Me.txtID.Tag`? That seems very unusual to me, I'd think you want `Me.txtID.Value` – Erik A Aug 08 '18 at 09:10
  • Your SQL string doesn't have spaces at the end, between the continuation lines, so will read something like `Update dbo_gatewayset deleated_at...`. Secondly if you really mean to use the tag value it is stored as a string, so may not do what you are expecting when comparing it to your ID field. – Minty Aug 08 '18 at 09:46
  • Hi. Thank you for your thoughts guys. It's my first week using VBA so I'm still a newbie debugging the errors. :) I already updated my code and still getting an error message Syntax error (missing operator) in query expression 'id='. Please help. :( @Minty – Ted Aug 08 '18 at 09:58
  • [How to debug VBA code](http://www.cpearson.com/excel/DebuggingVBA.aspx) – Andre Aug 08 '18 at 10:03

1 Answers1

1

Your textbox is empty, thus:

" where id=" & Me.txtID.Value, dbSeeChanges)

becomes:

" where id="

So, double-check your code and prevent it from running if txtID is Null.

Gustav
  • 53,498
  • 7
  • 29
  • 55