CODEWITHSUNDEEP
ms-accessvbams-access-2010
dmorgan20
dmorgan20

Reputation: 363

Record Set - No current record

I am having some difficulty with record sets.

CODE:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tbl_Suggestions_Historic WHERE ID = ' " & ID & " '")

    rs.Edit
    rs("Status") = "Closed By User"
    rs("ReasonClosed") = ClosedWhy
    rs.Update

Set rs = Nothing

PROBLEM: I have a list box with a list of accounts. When an account is click a textbox populates with the record id, I want the changes to update that particular record.

ERROR: The message received is 'No Current Record'

Any help is appreciated

Upvotes: 0

Views: 102

Answers (1)

Erik A
Erik A

Reputation: 32682

The likely error is that your ID column is a number, not a string, and thus you should remove the quotes:

Set rs = db.OpenRecordset("SELECT * FROM tbl_Suggestions_Historic WHERE ID = " & ID)

If it is a string, you need to be mindful of your spaces. You're including a space before and a space after the ID.

However, this is a good use case for an update query instead of a recordset. Imo, you should remove all your recordset code, and just use this:

CurrentDb.Execute "UPDATE tbl_Suggestions_Historic SET [Status] = 'Closed By User', [ReasonClosed] = '" & ClosedWhy & "' WHERE ID = " & ID

Or, if you want to do it properly, use parameters. I haven't used them since you didn't

See below for an example using parameters

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.CreateQueryDef("","UPDATE tbl_Suggestions_Historic SET [Status] = 'Closed By User', [ReasonClosed] = ? WHERE ID = ?" )
qdf.Parameters(1) = ClosedWhy
qdf.Parameters(2) = ID
qdf.Execute

Upvotes: 1

Related Questions