That makes sense. Thanks Bob. I'll give that a try. --- In AccessDevelopers@yahoogroups.com, "Robert Peterson" <[EMAIL PROTECTED]> wrote: > > 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: AccessDevelopers@yahoogroups.com > [mailto:[EMAIL PROTECTED] On Behalf Of David Lamb > Sent: Thursday, November 17, 2005 6:14 PM > To: AccessDevelopers@yahoogroups.com > 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 --------------------~--> AIDS in India: A "lurking bomb." Click and help stop AIDS now. http://us.click.yahoo.com/VpTY2A/lzNLAA/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/
