Hi,
I am not sure I understand the business problem you are trying to solve.
This is how I read your code (skipping the debug.print lines):
1. Add the trans amount to the account value
2. Compare the new account value to the budget value, if it is greater than
the budget then
3. Open a recordset on the table called "tblAccount"
4. For the first record in the table:
5. Assign the value stored in AccountName to the variable called "str"
6. Assign the value stored in AccountValue to the variable called "value"
7. Assign the value stored in AccountBudget to the variable called "budget"
8. Add the variable "value" and the variable "budget" together and store the
result in the variable called "value".
9. Move to the next record and repeat steps 5 through 8. Continue until you
get to the end of the recordset.
You assign values to your variables but never send the change back to the
recordset. Your code is looping through all the records in the table and
doing nothing. Also, if you are expecting the number stored in the table to
be the new number that shows on your form you need to force the form to save
the current record. I would use:
If me.dirty then me.dirty=false
between steps one and two.
If you add:
rs.edit
rs!YourFieldToUpdate = value
rs.update
before the rs.movenext it will write the number contained in the variable
you call value back to the table.
Now that I've written it out in plain language you probably are intending to
increase the budget amount since the Account Value is now over budget. The
field to update would be rs!AccountBudget. However if you update
AccountBudget with the ammount in "value" you will be more than doubling
your budget and you probably don't intend for that to happen.
Let me know if I'm reading this wrong.
Bob Peterson
P.S.
"value" is a reserved word and should not be used as a variable name.
See -
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
-----Original Message-----
From: [email protected]
[mailto:[EMAIL PROTECTED] On Behalf Of David Lamb
Sent: Thursday, November 17, 2005 6:14 PM
To: [email protected]
Subject: [AccessDevelopers] updating a table from a form
This code is for a family budget application. New money is deposited into
an "income" account until the account goes over a specified level, then all
of the accounts (categories like groceries, gas, etc.) are increased by
their budget amount. When the code runs, it seems to work. Debug.print
shows the updated figures in the immediate window.
But when I open the table, the values remain unchanged. How do I make this
stick in the table? The key code in question is inside the if-then
statements. Many thanks for any help you can offer.
Private Sub TransAmount_AfterUpdate()
Dim db As Database, rs As Recordset
Dim str As String
Dim value As Currency
Dim budget As Currency
If Combo9 = "credit" Then
DoCmd.RunCommand acCmdRefreshPage
frmSubTransAccount!AccountValue = _
frmSubTransAccount!AccountValue + TransAmount
If frmSubTransAccount!AccountValue > _
frmSubTransAccount!AccountBudget Then
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblaccount")
rs.MoveFirst
Do Until rs.EOF
str = rs!AccountName
value = rs!AccountValue
Debug.Print str & " value = " & value
budget = rs!AccountBudget
Debug.Print str & " budget = " & budget
value = value + budget
Debug.Print str & " new value = "; value
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End If
End Sub
------------------------ Yahoo! Groups Sponsor --------------------~--> Get
fast access to your favorite Yahoo! Groups. Make Yahoo! your home page
http://us.click.yahoo.com/dpRU5A/wUILAA/yQLSAA/q7folB/TM
--------------------------------------------------------------------~->
Please zip all files prior to uploading to Files section.
Yahoo! Groups Links
------------------------ Yahoo! Groups Sponsor --------------------~-->
1.2 million kids a year are victims of human trafficking. Stop slavery.
http://us.click.yahoo.com/WpTY2A/izNLAA/yQLSAA/q7folB/TM
--------------------------------------------------------------------~->
Please zip all files prior to uploading to Files section.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AccessDevelopers/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
