[resolved] searching in access tables

I know this should be easyb tu I just can't seem to get it right. I am trying to take information from one table and update another table with it, what is getting me is the seek function, or how to pinpoint the location of entry to be update.

Code:

---

Dim db As Database
Dim WellInfo As Recordset, PluggedInputTable As Recordset
Dim currentWell As String, SQLt As String
Set db = CurrentDb
Set WellInfo = db.OpenRecordset("WellInfo")
SQLt = "SELECT * FROM PLUGGEDINPUTTABLE ORDER BY PLUGGEDINPUTTABLE!WellId;"
Set PluggedInputTable = db.OpenRecordset(SQLt)
PluggedInputTable.MoveFirst
Do Until PluggedInputTable.EOF
    currentWell = PluggedInputTable!WellID
   
  varbookmark = .Bookmark
  ' WellInfo.Find (WellID = currentWell)

---

It sounds like you are going about this the wrong way. :)

All you have to do is execute a SQL statement to update one tables record with the values of the other tables record you retrieve with a subquery.

Something like...

(Ima bit rusty with the sql code but maybe someone else can polish it up.

Code:

---

UPDATE Table1 SET Field1 = (SELECT Fieldx FROM Table2 WHERE SomeField = 'SomeValue')

---

in case anyone is interested here was the final code i used...it updates two fields from one table to another (the master table has many more fields) the table to be updated is WellInfo. The table with the updating information is PluggedInputTable

Code:

---

UPDATE PluggedInputTable INNER JOIN WellInfo ON PluggedInputTable.WellID = WellInfo.WellID SET WellInfo.DatePlugged = [PluggedInputTable].[DatePlugged], WellInfo.[Current Status] = [PluggedInputTable].[Status]
WHERE ((([WellInfo.WellID])=[PluggedInputTable.WellID]));

---