Update help

[EBee]

I am trying to update data in my record and it's not updating. What 'em i doing wrong. .

data looks like
want. . . . . . . . .. . . .
TIP GET20 GET10 GET5 GET1 GET25
23.75 1 0 0 3 3


thanks
erwin


Private Sub Update_data()
Dim db As Database
Dim rs As Recordset


Set db = CurrentDb()
Set rs = db.OpenRecordset("Get_change_tbl&quot

rs.MoveFirst
Do Until rs.EOF



If rs!Tip >= 20 And rs!Tip < 30 Then
rs.Edit
rs!Get20 = rs!Tip - 20
rs.Update
End If

If rs!Get20 >= 10 And rs!Tip < 20 Then
rs.Edit
rs!Get10 = rs!Get20 - 10
rs.Update
End If

If rs!Get10 >= 5 And rs!Tip < 10 Then
rs.Edit
rs!Get5 = rs!Get10 - 5
rs.Update
End If

If rs!Get1 >= 1 And rs!Tip < 5 Then
rs.Edit
rs!Get1 = rs!Get5 * 1
rs.Update
End If

If rs!Get1 > 1 And rs!Get1 < 0.99 Then
rs.Edit
rs!Get25 = rs!Get1 / 0.25
End If


rs.MoveNext
Loop




MsgBox &quot; Done. . Thank you &quot;


End Sub

 

[JaneC]

Hello EBee

I think you need to assign the Tip to a working variable so you can decrement it as you go through. The following code should work.

Private Sub Update_data()

Dim db As Database
Dim rs As Recordset
Dim wrkTip As Double

Set db = CurrentDb()
Set rs = db.OpenRecordset(&quot;Get_change_tbl&quot

rs.MoveFirst
Do Until rs.EOF

wrkTip = rs!Tip


If wrkTip >= 20 Then
rs.Edit
rs!Get20 = Int(wrkTip / 20)
rs.Update
wrkTip = wrkTip - (rs!Get20 * 20)
End If

If wrkTip >= 10 Then
rs.Edit
rs!Get10 = Int(wrkTip / 10)
rs.Update
wrkTip = wrkTip - (rs!Get10 * 10)

End If

If wrkTip >= 5 Then
rs.Edit
rs!Get5 = Int(wrkTip / 5)
rs.Update
wrkTip = wrkTip - (rs!Get5 * 5)
End If

If wrkTip >= 1 Then
rs.Edit
rs!Get1 = Int(wrkTip)
rs.Update
wrkTip = wrkTip - rs!Get1
End If

If wrkTip > 0 Then
rs.Edit
rs!Get25 = wrkTip / 0.25
rs.Update

End If


rs.MoveNext
Loop

End Function


Good Luck

JC

 

[EBee]

I am still not getting a corect breakdown of change that i needed , it is almost there i think- I just need more help
Below is that data i am getting from the module. The twenties are fine but from there its not correct. The Get1 and Get25 seems correct just the Get10 and Get5 needs some work.


Tip_round_down Get20 Get10 Get5 Get1 Get25
$63.75 3.00 1.00 10.00 3.00 3.00
$73.75 3.00 1.00 10.00 3.00 3.00
$3.75 2.00 1.00 10.00 3.00 3.00

thank you
erwin

Private Sub Update_data()

Dim db As Database
Dim rs As Recordset
Dim wrkTip As Double

Set db = CurrentDb()
Set rs = db.OpenRecordset(&quot;Get_change_tbl&quot

rs.MoveFirst
Do Until rs.EOF

wrkTip = rs!Tip_round_down


If wrkTip >= 20# Then
rs.Edit
rs!Get20 = Int(wrkTip / 20)
rs.Update
wrkTip = wrkTip - (20 * rs!Get20)


ElseIf wrkTip >= 10# Then
rs.Edit
rs!Get10 = Int(wrkTip / 10)
rs.Update
wrkTip = wrkTip - (rs!Get10 * 10)



ElseIf wrkTip >= 5# Then
rs.Edit
rs!Get5 = Int(wrkTip / 5)
rs.Update
wrkTip = wrkTip - (rs!Get5 * 5)


ElseIf wrkTip >= 1# Then
rs.Edit
rs!Get1 = Int(wrkTip)
rs.Update
wrkTip = wrkTip - rs!Get1


ElseIf wrkTip > 0 And wrkTip < 1 Then
rs.Edit
rs!Get25 = wrkTip / 0.25
rs.Update

End If


rs.MoveNext
Loop

MsgBox &quot; DONE&quot;

End Sub

 

[JaneC]

The problem is with the Elseifs. Change the code to be the saem as I posted and it will work.

Jane

 

[EBee]

I had it the way you suggested and i still not getting the correct 10's and 5's, my 10's is always 1 and the 5' are changing values throughout the table but non that i can see constant. is it because i use Private Sub instead of Function

thanks
erwin


Private Sub Update_data()

Dim db As Database
Dim rs As Recordset
Dim wrkTip As Double

Set db = CurrentDb()
Set rs = db.OpenRecordset(&quot;Get_change_tbl&quot

rs.MoveFirst
Do Until rs.EOF

wrkTip = rs!Tip_round_down


If wrkTip >= 20# Then
rs.Edit
rs!Get20 = Int(wrkTip / 20)
rs.Update
wrkTip = wrkTip - (20 * rs!Get20)
End If

If wrkTip >= 10# Then
rs.Edit
rs!Get10 = Int(wrkTip / 10)
rs.Update
wrkTip = wrkTip - (wrkTip * 10)

End If
If wrkTip >= 5# Then
rs.Edit
rs!Get5 = Int(wrkTip / 5)
rs.Update
wrkTip = wrkTip - (rs!Get5 * 5)

End If

If wrkTip >= 1# Then
rs.Edit
rs!Get1 = Int(wrkTip)
rs.Update
wrkTip = wrkTip - rs!Get1
End If

If wrkTip > 0 And wrkTip < 1 Then
rs.Edit
rs!Get25 = wrkTip / 0.25
rs.Update

End If


rs.MoveNext
Loop

MsgBox &quot; DONE&quot;

End Sub

 

[EBee]

Oh, I think the problem is the type, what type should my Get20, Get10. . . . Integer/Number/Currency. .etc.

thanks

 

[JaneC]

Hi,

There is a typo in the get10 bit.


If wrkTip >= 10# Then
rs.Edit
rs!Get10 = Int(wrkTip / 10)
rs.Update
wrkTip = wrkTip - (wrkTip * 10)

should be -> wrkTip = wrkTip - (rs!Get10 * 10)

End If

I have the Get fields defined as Long Integer.

Can't think of anything else !

JC

 

[EBee]

i made all the changes and i still get

Get10 = 1 all the time
Get5 = 4 for 20, 10 for 50. .etc. . and so

The 20's, 1's, and the .25 are fine

i can not figure out why

erwin

 

[EBee]

I made some changes and it works

erwin

Private Sub Update_data()

Dim db As Database
Dim rs As Recordset
Dim wrkTip As Double

Set db = CurrentDb()
Set rs = db.OpenRecordset(&quot;Get_change_tbl&quot

rs.MoveFirst
Do Until rs.EOF

wrkTip = rs!Tip_round_down


If wrkTip >= 20# Then
rs.Edit
rs!Get20 = Int(wrkTip / 20)
rs.Update
wrkTip = wrkTip - (20 * rs!Get20)
ElseIf wrkTip > 10 And wrkTip < 20 Then
rs.Edit
rs!Get20 = 0
rs.Update
End If

If wrkTip >= 10# Then
rs.Edit
rs!Get10 = Int(wrkTip / 10)
rs.Update
wrkTip = wrkTip - (10 * rs!Get10)
ElseIf wrkTip > 1 And wrkTip < 10 Then
rs.Edit
rs!Get10 = 0
rs.Update
End If
If wrkTip >= 5# Then
rs.Edit
rs!Get5 = Int(wrkTip / 5)
rs.Update
wrkTip = wrkTip - (rs!Get5 * 5)
ElseIf wrkTip > 1 And wrkTip < 5 Then
rs.Edit
rs!Get5 = 0
rs.Update

End If

If wrkTip >= 1# Then
rs.Edit
rs!Get1 = Int(wrkTip)
rs.Update
wrkTip = wrkTip - rs!Get1
ElseIf wrkTip < 1 Then
rs.Edit
rs!Get1 = 0
rs.Update
End If

If wrkTip > 0 And wrkTip < 1 Then
rs.Edit
rs!Get25 = wrkTip / 0.25
rs.Update
ElseIf wrkTip <= 0 Then
rs.Edit
rs!Get25 = 0
rs.Update

End If


rs.MoveNext
Loop

MsgBox &quot; DONE&quot;

End Sub