DAO update field

Resolved · Low Priority · Version 2003

Wed 10 Sep 2008, 13:24 Delegate Gillian said...

Gillian has attended:
Access VBA course
Excel Advanced course

DAO update field

Is there a VBA DOA method to update a field in a table recorset? I am updating a field base don the trimmed value of another field in the table.
I can do it by using tabledef and calling on SQL to do it, but I need to know how to do it without SQL because I want to use the VBA 'replace' method tp update another field.

Is this possible with DAO? Should I put the table into an array instead?

Fri 26 Sep 2008, 12:57 Trainer Laura said...

RE: DAO update field

Hi Gillian

Thanks for the question.

DAO will do what you want to do I think. Below is a simple example of walking through a complete table updating a field.



Sub EditRow_DAO()

    Dim DB As DAO.Database

    Dim tblItems As DAO.Recordset

    'get the current database and recordset to work with

    Set DB = CurrentDb

    Set tblItems = DB.OpenRecordset("tbl_Items")

Do Until tblItems.EOF

tblItems.Fields("TrimmedName") = Trim(tblItems.Fields("OriginalName"))

tblItems.Update

tblItems.MoveNext

Loop



    'close the recordset and database

    tblItems.Close

    DB.Close

End Sub