MS Access - update field in VBA?

[aclivity]

Long shot, hopefully someone can advise? I'm usually OK with VBA on Excel, Access however is a bit of a change of scene for me.

I'm trying to update a field in a table - I have a lookup which gets the current value, I want to append some data then save it back to the table.

TA Deliverable is a table in my database, the key is an autonumber field called ID. I want to update a field called Deliverable Notes.

The code is as follows:

Dim rs As DAO.Recordset

Set DB = CurrentDb
Set rs = DB.OpenRecordset("TA Deliverable")
rs.FindFirst "[TA Deliverable].[ID] = " & Deliverable_ID
rs.Edit
rs("Deliverable Notes") = New_Deliv_Notes
rs.Update
rs.Close 

When it reaches the "rs.FindFirst" line it goes straight to an error condition

MS Access said:

Operation is not supported for this type of object

Any clues?

[JayBM]

my only 2 thoughts would be to:

1. include the brackets for the find first i.e.

rs.FindFirst("[TA Deliverable].[ID] = " & Deliverable_ID)

2. you could also build the search criteria in a string and then use it for the find first; this would allow you to see if its the structure of your search string?

[Landlord]

This is usually because of the type of recordset you've opened. Try;

Set rs = DB.OpenRecordset(<blah>), dbOpenTable

Edit: Becuase you're opening a table, not a query. Also, I don't use DAO anymore, I prefer ADO and am therefore can't remember the correct recordset type. Take a look at help on "OpenRecordset" for the different types.

Further edit: You'd probably be better doing;

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [TA Deliverable] SET [Deliverable Notes] = '" & New_Deliv_Notes & "' WHERE [ID] = " & Deliverable_ID;"
DoCmd.SetWarnings True

The SetWarnings bits are incase you don't want the "You are about to update n rows..." message box.

Edited by Landlord on Tuesday 2nd March 09:42

[Landlord]

JayBM said:

my only 2 thoughts would be to:

1. include the brackets for the find first i.e.

rs.FindFirst("[TA Deliverable].[ID] = " & Deliverable_ID)

Just FYI. If you were to do this, Access would expect you to be associating the value to an object. The lack of brackets around the criteria is fine/correct.

[aclivity]

Thanks for all the answers. I think the DAO recordset was a non-starter, though.

This is the one that works, thanks!

Landlord said:

You'd probably be better doing;

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [TA Deliverable] SET [Deliverable Notes] = '" & New_Deliv_Notes & "' WHERE [ID] = " & Deliverable_ID;"
DoCmd.SetWarnings True

The SetWarnings bits are incase you don't want the "You are about to update n rows..." message box.

As an aside, I posted the same question on a MSDN forum. No answer yet: Pistonheads - Better at supporting microsoft applications than MSDN.