Update multiple records in Access 2007

[mattsmom]

I am trying to update multiple records in Access 2007 by inputting a date one
time in a form.

 

[boblarson]

This is good for ANY verison of Access - call an update query and set the
value to update to the form field.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[mattsmom]

Bob, thanks for the response. Yes, I've used an update query and it worked
perfectly. There are several end-users who will be inserting their
completion dates into this form and I don't really want them to run update
queries. I don't know how to link it to a form so it will run automatically
for each user.

 

[boblarson]

You can set an update query to only update certain records based on some
criteria, and I would highly suggest that method because it is way more
efficient that iterating through a recordset. However, depending on how you
have it set up, you could go add the date to the displayed records, if that
is better for you. You could use something like this:

Dim rst As DAO.Recordset

Set rst = Me.Recordset

Do Until rst.EOF
With rst
.Fields("YourFieldNameOrIndexHere") = Date
.Update
.MoveNext
End With
Loop

rst.Close
Set rst = Nothing


--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[mattsmom]

Thanks Bob, that worked!

You can set an update query to only update certain records based on some
criteria, and I would highly suggest that method because it is way more
efficient that iterating through a recordset. However, depending on how you
have it set up, you could go add the date to the displayed records, if that
is better for you. You could use something like this:

Dim rst As DAO.Recordset

Set rst = Me.Recordset

Do Until rst.EOF
With rst
.Fields("YourFieldNameOrIndexHere") = Date
.Update
.MoveNext
End With
Loop

rst.Close
Set rst = Nothing


--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________