CODEWITHSUNDEEP
vbams-access
Mahmoud
Mahmoud

Reputation: 17

Updating Record in a table VBA Access

I am trying to update the record in an Access table using the below code and get an error that the data type is mismatch in criteria expression :

If Not (Me.frmdamagesub.Form.Recordset.EOF And Me.frmdamagesub.Form.Recordset.BOF) Then
  With Me.frmdamagesub.Form.Recordset
    Me.txtquantity = .Fields("Quantity")
    Me.txtquantity.Tag = .Fields("Quantity")
    Me.cmdedit.Caption = " Update"
    CurrentDb.Execute " UPDATE damaged_card " & _
    " SET Quantity='" & Me.txtquantity & "'" & _
    " WHERE Quantity=" & Me.txtquantity.Tag

  End With
End If

Upvotes: 2

Views: 21864

Answers (1)

vacip
vacip

Reputation: 5426

Since you are comparing to texts, try it like this:

CurrentDb.Execute " UPDATE damaged_card " & _
" SET Quantity='" & Me.txtquantity & "'" & _
" WHERE Quantity='" & Me.txtquantity.Tag & "'"

Notice the added '-s.

Debugging

If it still fails, check the values in runtime and try running the SQL manually and see if it works that way. To extract the statement in runtime, have VBA output it into the Immediate window (ctrl+g) like this:

 debug.print " UPDATE damaged_card " & _
" SET Quantity='" & Me.txtquantity & "'" & _
" WHERE Quantity='" & Me.txtquantity.Tag & "'"

Do this before you try to execute it.

Then you can go to Access, create a new query, change the view to SQL view, and paste the produced SQL statement. Before executing it with Run, it is worth taking a look at the affected rows by clicking the View button and selecting Datasheet View.

Query View button

This displays all the rows from the target table that will be affected by the change. If no rows are displayed, that means no row fits your criteria, and nothing will be changed.

To be honest, your query doesn't make much sense, as it says: "update the Quantity to 5 where the Quantity is 5". You might want to rethink you where clause.

Refine your query in Access, and once it works, paste it back to the VBA code.

Upvotes: 1

Related Questions