How to use VBA to update a record in a table in MS Access?

You can either write SQL or use a recordset.
SQL:

Currentdb.Execute "Update [SLR Failure Audits] Set [Failure Reason]='" & theReason & "'"

Select allOpen in new window

Note that under normal circumstances you would probably include a where clause.

Recordset:

Dim rs as dao.Recordset
Set rs=Currentdb.openrecordset("Select * from [SLR Failure Audits]  Where RecordID=???")
rs.edit
  RS![Failure Reason]=theReason
rs.update

Select allOpen in new window

I’m getting a run-time error ‘3061’ “Too few parameters. Expected 1” for line 3.  The field with the record ID is named “Incident”.

Public Sub SLRFailureAuditsUpdate(ByVal theReason As String, theTicket As String)
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("Select * from [SLR Failure Audits]  Where Incident=theTicket")
rs.Edit
  rs![Failure Reason] = theReason
rs.Update
End Sub

Select allOpen in new window

Okay, when doing SQL in code,it can often be a good idea to toss the SQL into a variable which allows you to better debug it.

Public Sub SLRFailureAuditsUpdate(ByVal theReason As String, theTicket As String)
Dim rs As dao.Recordset
Dim SQL as string
SQL="Select * from [SLR Failure Audits]  Where Incident=theTicket"
Debug.Print SQL
Set rs = CurrentDb.OpenRecordset(SQL)
rs.Edit
  rs![Failure Reason] = theReason
rs.Update
End Sub

Select allOpen in new window

Try that, take a look at the SQL it is producing, in your immediate window, and see if you can figure out what went wrong.

Same error but now on the below line

Set rs = CurrentDb.OpenRecordset(SQL)

Select allOpen in new window

Also, I not sure what you mean by looking at the SQL it is producing.

In the VBA editor, if you hit Ctrl-G, or go to View->Immediate window, you get a small window which is handy for debugging. The line Debug.Print will put information into that immediate window. E.g. it will let you see the full SQL string, which should enable you to spot where your error is.

Anders, you know it should read:

Set rs = CurrentDb.OpenRecordset("Select * from [SLR Failure Audits]  Where Incident = '" & theTicket & "'")

Select allOpen in new window

/gustav

Yes I know that Gustav. I was trying to teach how to debug it, rather than just giving the answer :), so that next time he comes across a similar situation the OP can solve it on his own :)

Oh, missed that, sorry.

/gustav

I did notice that I was missing the second "ByVal".  In the immediate window I see "Select * from [SLR Failure Audits]  Where Incident=theTicket"

Public Sub SLRFailureAuditsUpdate(ByVal theReason As String, ByVal theTicket As String)
Dim rs As dao.Recordset
Dim SQL As String
SQL = "Select * from [SLR Failure Audits]  Where Incident=theTicket"
Debug.Print SQL
Set rs = CurrentDb.OpenRecordset(SQL)
rs.Edit
  rs![Failure Reason] = theReason
rs.Update
End Sub

Select allOpen in new window

The below line did work and I appreciate the lesson, but I don't understand how the results for the immediate window would have pointed me in the right direction.

Set rs = CurrentDb.OpenRecordset("Select * from [SLR Failure Audits]  Where Incident = '" & theTicket & "'")

Select allOpen in new window

Thank you very much for the explanation.