CODEWITHSUNDEEP
sqlms-accessvbadao
user3473275
user3473275

Reputation: 31

Edit, update record with DAO.Recordset

I have two tables Table1 and Table2. In Table1 there is Column called FileID and Table2 there is column called FileID. Now FileID is not unique, so there are certain rows where FileID is repeated in both tables. I have a Form in Access, where there is field called GetDate from Table1. I need something in vba that if you make changes to the GetDate part on the form (which is from Table1 table) then it should update column Date with the current date and Time which is in Table2

I did it the following way. But its updating only the first record of Date From Table2 with current time and not the second one with FileID = 1. there are two records for FileID = 1, in table2 so it should update 2 records with current date and not one. here is my code for it. 

Option Compare Database

Private Sub GetDate_AfterUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
' Dim rst2 As DAO.Recordset


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT  [GetDate], [DATE] FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID2 where Table1.FileID = Table1.FileID", dbOpenDynaset)
'Begin row processing
Do While Not rst.EOF
rst.Edit
rst![Date] = Now
rst.Update
rst.MoveNext
Exit Do

Loop



rst.Close
Set rst = Nothing


End Sub

thanks for the help!

Upvotes: 2

Views: 10506

Answers (1)

Mike
Mike

Reputation: 144

Why do you have an exit do after your movenext, that shouldn't be there if you want to loop through all the records.

The rst.eof will exit the loop when it gets to the end of the file.

The way you have it written it will exit after the first record.

Do While Not rst.EOF
 rst.Edit
 rst![Date] = Now
 rst.Update
 rst.MoveNext
 Exit Do 'This will exit loop after first record
Loop

Do this instead

Do 
 rst.Edit
 rst![Date] = Now
 rst.Update
 rst.MoveNext
Loop Until rst.EOF

Upvotes: 2

Related Questions