changing current record

[ryetee]

I have a continuous form which displays rows from a table.
I have 2 controls in particular Me.Price and Me.Gross
Price is without tax, gross is with .
If Gross is changed then the before_update does variou things and calls a sub called applychanges which effectively looks up the tax(es) for the client and recalculates Price

This all works. So I have something like (this is simplified somewhat!)
Me.Price = Me.Gross / (1 + (Me.TaxUsed.Column(2) / 100) + (Me.TaxUsed.Column(3) / 100))

Most customers have the tax included in the gross. Some however don't. So the user wants to effectively up the gross by the tax and then recalculate the price.
I have the following code, which is an event to a button on the form header, to change the Gross and I then call the sub applychanges. The gross is changed ok with the code below but the price only gets changed on the first row presumably because it's the current record. In actual fact if I move the cursor to say the 4th row before clicking the button I get a write conflict. Is this something I can't really do or am I missing something?

With rst
.MoveLast
.MoveFirst
'lngPosition = 0
Do While Not .EOF
' lngPosition = lngPosition + 1
' rst.AbsolutePosition = lngPosition
.Edit

dblGross = rst![Gross]
rst!Gross = dblGross * 1.262
.Update

.MoveNext
applychanges
Loop

.Close
End With

Set db = Nothing
Set rst = Nothing

 

[Ranman256]

this is a database, you don't use code to cycle thru records,
use an update query.
no code needed , and it runs faster.

 

[James Dickinson]

replace everything you posted with this and set the [table name]

currentdb.execute "update [tablename] set Gross = Gross * 1.262",dbfailonerror + dbseechanges

me.requery

 

[ryetee]

replace everything you posted with this and set the [table name]

currentdb.execute "update [tablename] set Gross = Gross * 1.262",dbfailonerror + dbseechanges

me.requery

it's slightly more complicated than that
the 1.262 was my figure based on what I know. It's actually 2 different taxes added together and could be a number of different figures. I also need to update various other fields based on what's in the gross field. I can do it long hand but I wanted to reuse code. if the rules change I'll have to change it in 2 different places

 

[isladogs]

Using an update statement is still the right way to go.
Much faster and simpler to write

You just need to substitute the correct value in place of 1.262

If it depends on the gross figure then use values from a lookup table tblTaxBands with fields similar to this:
TaxID - autonumber PK
Gross - currency
TaxBand1 - number
TaxBand2 - number
Its not a normalised table but may be appropriate for this purpose

Then use a procedure where you
a) get the taxband values for the gross amount
b) determine the conversion (multiplying) factor
c) use that factor in your update statement

 

[June7]

Please post code between CODE tags to retain indentation and readability. Provide the complete procedure - how is rst declared and set?

One option is for code to physically change focus to each record on form.

I am very confused. If Price is without tax then why would you need to lookup tax to recalculate Price? If 1.262 is not correct then what should be used? If requirement is too complicated for the UPDATE action then you need to explain details.

 

[James Dickinson]

Well if it's more complicated then I guess we need options:

option 1 select case

dim curTaxAmount as currency
Select case [Whatever your complication is]

case [xxx]: curTaxAmount  = 1.262
currentdb.execute "update [tablename] set Gross = Gross * " & curTaxAmount ,dbfailonerror + dbseechanges

case [yyy]: curTaxAmount  = 2.354 + me.somevaluefrommyform
currentdb.execute "update [tablename] set [somefield] = "blah", Gross = Gross * " & curTaxAmount ,dbfailonerror + dbseechanges

case [zzz]: curTaxAmount  = 4.568 + globalvariablesomevalue
currentdb.execute "update [tablename] set  [somefield] = "someotherblah" Gross = Gross * " & curTaxAmount ,dbfailonerror + dbseechanges

etc
etc
end select

me.requery

 

[ryetee]

thanks all. i ended up with an update query passing it some parameters where necessary.