Finding and updating a record

[Alex]

Hi, and Happy new Year to all on the NG - hope someone can help me.

Within a function, I need to open a table, find a record and update it, and
I am not quite sure how to do this

I have used:
rs.Open "tblSWPurchases", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

to open table tbl;SWPurchases. I now need to locate the record where
[recordId] = RecId (a variable), and then update a field [Licid] with a
variable and then close the table down.

i have tried various syntax to FindFirst where [RecordId] = RecId, but
without success.

COuld some kiond person either advise me or point me in the direction of a
tutorial

Thanks

Alex

 

[Scott McDaniel]

You can do this without opening a recordset:

CurrentProject.Connection.Execute "UPDATE tblSWPurchases SET [Licid]='" &
YourVariable & "' WHERE [recordId]=" & YourID

If you need to do something else with this record, however, you'll probably
want to open a recordset and manipulate it:

Dim rs As New ADODB.Recordset

rs.Open "SELECT * FROM tblSWPurchases WHERE [RecordId]=" & YourID,
CurrentProject.Connection

rs("licid") = YourVariable
rs.Update

rs.Close

You can do this via DAO as well, if this is Access 97 ... lookup the
OpenRecordset method in online help for examples of how to do this

 

[Alex]

Thanks Scott - worked a treat !

Alex