How to insert a 'pause' before executing next line of code?

[BJF]

Hello -

I have a form which calculates and stores a cost into a field through the 'on current' event of the form. I used code which cycles through my recordset on the click of a button and updates the entire recordset when clicked.

The code runs but doesn't seem to alot enough time to allow the 'on current' event to run and store the number I need as it hits each record.

Is there something i can add to the code to make it pause a second or two or enough time to run the code on each of my records?

the code on the button is:

Dim TotalRecordsTemp As Integer

Dim rst As Object
Set rst = Me.RecordsetClone
On Error Resume Next
rst.MoveLast
On Error GoTo 0
TotalRecordsTemp = rst.RecordCount

For i = 1 To TotalRecordsTemp
DoCmd.GoToRecord , , acNext
Next i

MsgBox "Standard Cost update is complete!", vbOKOnly
DoCmd.Close

thanks for any suggestions
Brian

 

[michaeljryan78]

DO Events is a great way to do this.

 

[RuralGuy]

You shoud know that the RecordCount is not guaranteed accurate until *after* you have done a .MoveLast.

 

[BJF]

Hi thanks Michael and Ruralguy for responding -

Im not sure how to go about this based on your suggestions-

I have limited informal vba knowledge and ususally piece together code based on the many examples i find online; such is the case with the code i pasted in my question.

Please explain

Thanks,
Brian

 

[michaeljryan78]

The OnCurrent Event fires when the next record is loaded on the form. If you have a continuos form, then placing the button on the form and calling the code should be sufficient. It is updating the entire recordset, therefore when you load the next record, the OnCurrent fires again, but you just updated the entire recirdset on the previous press of the button.

 

[BJF]

Hi Michael -

I dont have a continuous form, its in single form format because its got a subform and does a lot of fairly complex computations based on dropdowns. I believe it is set up the best way possible for my application. I build a product based on many different tables through dropdown menus, costs are deriven and then on current event of form a particuliar number that i need which is called 'CostPerOneInch' is stored based on some math done in the code. If i go to a particular record one at a time my code works great. But i want to designate a time to update all records at the press of a button using my code to cycle through all records thereby executing the on current event, its just happening so fast right now the code does not have enough time to run and store the number for each record.

Based on the code i've shown, can i put in a line of code that will slow down the cycling?

Thanks,
Brian

 

[michaeljryan78]

Oh, I get it! You are moving to the next record on the form! I would create a function that calculates what you need. You can call the function as you iterate through the recordset without navigating the actual form so that some of the code that is running (but maybe not needed) in the onCurrent Event is not firing as you navigate the form.

 

[michaeljryan78]

You may want to naviage through the recordset and call the proccedure for each record:
I don't see the math in this code, so I assume that it is a proceedure in code...
Dim TotalRecordsTemp As Integer

Dim rst As Object
Set rst = Me.RecordsetClone
On Error Resume Next
rst.MoveLast
On Error GoTo 0
TotalRecordsTemp = rst.RecordCount

rst.movefirst
do while rst.eof = false
'call math proccedure here
rst.movenext

MsgBox "Standard Cost update is complete!", vbOKOnly
rst.close set rst = nothing

''this is air code, sorry if there are typos!

 

[BJF]

Thanks Michael -

I think i understand what i need to do! I'll give it a try right now and post if it is successful

Thank you for your suggestion,
Brian