A Loop in a Loop

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

This Loop only updates the records to the first value. On 3.52 / 5 it
should update each record as such...
..71
..71
..70
..70
..70
but it's doing all .71
I think perhaps it's a timing issue?
Thanks
DS

Private Sub Command0_Click()
Dim myNums As String
Dim myNumDivByX As Currency
Dim portion(100) As Currency
Dim i As Currency
Dim leftover As Currency
Dim absleftover As Currency
Dim addon As Currency
myNumDivByX = Round(Me.TxtmyNum / Me.TxtX, 2)

leftover = Me.TxtmyNum - (myNumDivByX * Me.TxtX)
absleftover = Abs(leftover)
If leftover < 0 Then
addon = -0.01
Else
addon = 0.01
End If
For i = 1 To Me.TxtX
If i <= absleftover * 100 Then
portion(i) = myNumDivByX + addon
Else
portion(i) = myNumDivByX
End If
myNums = myNums & portion(i) & vbCrLf
Me.TxtNew = myNums
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM tblTSC WHERE
tblTSC.OldCheckID = " & Forms!Form3!TxtCheckID & "", dbOpenDynaset)
With rst
Do Until .EOF
.Edit
!ChkTotal = Me.TxtNew
.Update
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Next i
End Sub
 
So I tried this and it's all .70 now !!!
The other one is all .71 Where is the point that the divide is created? I
should be able to make one record, update then go on to the next. I don't
understand why this isn't working.
Thanks
DS

myNumDivByX = Round(Me.TxtmyNum / Me.TxtX, 2)

leftover = Me.TxtmyNum - (myNumDivByX * Me.TxtX)
absleftover = Abs(leftover)
If leftover < 0 Then
addon = -0.01
Else
addon = 0.01
End If

For i = 1 To Me.TxtX
If i <= absleftover * 100 Then
portion(i) = myNumDivByX + addon
Else
portion(i) = myNumDivByX
End If

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM tblTSC WHERE
tblTSC.OldCheckID = " & Forms!Form3!TxtCheckID & "", dbOpenDynaset)

With rst
Do Until .EOF
.Edit
!ChkTotal = myNums & portion(i) & vbCrLf
.Update
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Next i
 
Here is my test code...It works fine. .71, .71, .70, .70, .70
Next is the actual code...It returns .71, .70,.70,.70,.70
What on earth is the difference?!?!?
I copied and pasted and changed a couple of field names, am I missing
something?
Thanks
DS

TEST CODE
myNumDivByX = Round(Me.TxtmyNum / Me.TxtX, 2)
leftover = Me.TxtmyNum - (myNumDivByX * Me.TxtX)
absleftover = Abs(leftover)
If leftover < 0 Then
addon = -0.01
Else
addon = 0.01
End If

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM tblTSC WHERE tblTSC.OldCheckID =
" & Forms!Form3!TxtCheckID & "", dbOpenDynaset)

With rst
Do Until .EOF
For i = 1 To Me.TxtX
If i <= absleftover * 100 Then
portion(i) = myNumDivByX + addon
Else
portion(i) = myNumDivByX
End If
.Edit
!ChkTotal = myNums & portion(i) & vbCrLf
.Update
.MoveNext
Next i
Loop
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

THE REAL CODE
myNumDivByX = Round(Me.TxtDPTotal / Me.TxtDivideCheck, 2)

leftover = Me.TxtDPTotal - (myNumDivByX * Me.TxtDivideCheck)
absleftover = Abs(leftover)
If leftover < 0 Then
addon = -0.01
Else
addon = 0.01
End If
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM tblChecksTMP WHERE
tblChecksTMP.ChkOldCheckID = " & Forms!frmPadDivider!TxtDPOldSalesID & "",
dbOpenDynaset)

With rst
Do Until .EOF
For i = 1 To Me.TxtDivideCheck
If i <= absleftover * 100 Then
portion(i) = myNumDivByX + addon
Else
portion(i) = myNumDivByX
End If
..Edit
!ChkTotal = myNums & portion(i) & vbCrLf
..Update
..MoveNext
Next i
Loop
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
 
Found The Problem.
The TxtDPTotal was transferring from another form as 3.5108 and not as 3.52
in truth.
All is well.
Thanks
DS
 
Back
Top