Iteration VBA on records in a table when updating using a formula.

[ashish]

Hi,
I am new to Access VBA and trying to achive the following:
I have a table in which there are following fields and values:

ProfileWeight	Length	NoOfJoints	Runout	WeightOfBillet
0.184	6.10	25	152.5	Error
2.180	5.50	25	135.5	Error
3.560	6.50	25	162.5	Error

"NoOfJoints" is by default 25 and formula to calculate the Runout is: Lenght*NoOfJoints
The formula to calculate "WeightOfBillet" is: IIf(Runout>=50,"Error",ProfileWeight*RunOut)

Now, the formula would give error as the "Runout" is very high. So, is it possible to reduce "NoOfJoints" by 1 and iterate till the "Runout" is <50
e.g. if the Number of joints is 8 for the first record then we would get the Runout as 48.8. At this stage the "WeightOfBillet" formula should be true and give the result as: 0.184*48.8=8.972



I am a newbie to Access VBA and will highly appreciate if you can advise me to create a VBA function for the above.
Regards,
Ash

[TheShabz]

Do you need to iterate? You can do in VBA:
Dim IntTemp as Integer
Dim dblRunout as Double

intTemp = 49 \ length (note that its \ and not / for dividing here as we only want the whole number, no decimals)
dblRunout = intTemp * length

Just an idea. Something to that effect.

[ashish]

Hi TheShabz,
Thanks for your comment.
I have achieved this with another approach. I have created 1st macro with the query to reduce the count by -1 and update the NoOfCuts, Then update the runout using the formula. Then the next Macro will include the 1st Macro and iterate/repeat count to 25.
As, I wanted to do this in VBA so I converted the Macro to the VBA.
The code below may help someone else:
'------------------------------------------------------------
' Macro2
'
'------------------------------------------------------------
Function Macro2()
On Error GoTo Macro2_Err
'Start - Other commands before running Macro1
DoCmd.OpenQuery "Upd_EraseCalc", acViewNormal, acEdit
If (False) Then
DoCmd.OpenQuery "Upd_BW", acViewNormal, acEdit
End If
'End - Other Commands before running Macro1
'Run Macro1 from Here
DoCmd.RunMacro "Macro1", 25, "True"
'Some more post Update Queries
If (True) Then
DoCmd.OpenQuery "Upd_BW", acViewNormal, acEdit
End If
DoCmd.OpenQuery "Upd_BS", acViewNormal, acEdit
DoCmd.OpenQuery "Upd_BW2", acViewNormal, acEdit
DoCmd.OpenQuery "Upd_RO2", acViewNormal, acEdit

Macro2_Exit:
Exit Function
Macro2_Err:
MsgBox Error$
Resume Macro2_Exit
End Function

[TheShabz]

as long as you got it working, that's all that matters. Please mark this thread as solved, though, so others can search for it.