MS Access - How to cycle through records within a form to allow VBA update to occur for each record?
We have a particular form that reads from a table which stores start/due dates for production orders. Those dates change as the quanitty of the production order changes, either longer or shorter.
As the quantity changes, this is captured and the dates will be altered based on the quantity changed.
I have already place the necessary VBA within the form so that when the record is opened, it checks the quantity and adjusts the date accordingly. We do not do this in the update query as there is some work involved in getting the date format correct, as well as calculating the dates which will be updated.
What I am looking for is the next step, which is for the form to do this for each record, rather then the user needing to scroll through each records for the VBA to execute. And when it gets to the end it is done, and the loop does not repeat.
As the quantity changes, this is captured and the dates will be altered based on the quantity changed.
I have already place the necessary VBA within the form so that when the record is opened, it checks the quantity and adjusts the date accordingly. We do not do this in the update query as there is some work involved in getting the date format correct, as well as calculating the dates which will be updated.
What I am looking for is the next step, which is for the form to do this for each record, rather then the user needing to scroll through each records for the VBA to execute. And when it gets to the end it is done, and the loop does not repeat.
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Good call. Glad it worked for you. :)
Private Sub cmdSample_Click()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
With rst
If .RecordCount Then
.MoveFirst
Do Until .EOF 'When the last record is reached the code stops
Me.Bookmark = .Bookmark
'Run your code here
.MoveNext
Loop
End If
End With
rst.Close
Set rst = Nothing
End Sub
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER