Updating a field in a record

[Gerimc]

I have a field in a table called balance carried forward, i have this field stored in a table called yearlyrates. When i run my form i want it to pull in the value from the table, this I have done. The figure can change from time to time and is calculated using a number of other values. I want to compare the figure i have stored in the table against the figure i am calculating and if the figures are not the same. I want to take the calculated figure and store it in the table, overwriting the figure i have already stored there. Any idea how i would go about this ?

 

[TomHW]

Going from what you have written I see a table named 'yearlyrates' with a field named 'balance carried forward'. If there is only one row in the table then you can just run:

Public Sub UpdateFigure(dblCalculatedFigure as Double)
   Dim strSQL as String

[COLOR=green]   ' This is the SQL statement that updates the row.
   '  It sets every row to the value passed to the sub
   '  (Which is fine if there is only one row)[/color]
   strSQL = "UPDATE yearlyrates SET yearlyrates.[balance carried forward] = " & dblCalulatedFigure & ";"

   ' This runs the query
   DoCmd.RunSQL strSQL
End Sub

If you want it to update the figure in one particular row you need to add a WHERE clause to the end of the SQL statement.

This is just a simple subroutine that takes the value you want to update [balance carrid forward] to. You can put these statements anywhere and just change the dbl variable and SQL statement accordingly.

Hope this helps,
Tom