For each record in a table how do I copy field 2 to field 1?

[Guest]

In one table I have several records. Each record has a field containing this
year's price (CurrentPrice) and next year's price (NewPrice). On January
1st, I need to copy the data in the NewPrice field to replace the data in the
OldPrice field and then zero the data in the NewPrice field pending a manual
calculation and data entry of new prices for the following year. I would
like to make this switch for all records in the table at one time with one
click of a button (btnChangePrices). I'm familiar with the Me![OldPrice] =
Me![NewPrice] concept but it only works for the particular record being
displayed on the form or on a continuous form for the record that has the
focus. Can one of you help with the looping logic code?

 

[Granny Spitz via AccessMonster.com]

Can one of you help with the looping logic code?

Don't loop through the records, just use an update query like this:

UPDATE TableA
SET CurrentPrice = NewPrice, NewPrice = NULL;

If you want NewPrice to have a zero value, use NewPrice = 0.00

 

[Guest]

Sounds like a good idea but am not smart enough to implement. Tried creating
an update query using the design grid, but am not sure what to put where.
Can you help with a few more details? Is there a way to program an update
query in the code for the button click event?

 

[Granny Spitz via AccessMonster.com]

Sounds like a good idea but am not smart enough to implement.

Oh yes you are. Create a new query and go to SQL view (Insert | Query | OK |
Close then View | SQL View). Paste the update query I gave you over any
text you see and change TableA to your table name and save the query as
qryUpdatePrices. Create a button on a form and paste this code in the click
event:

DoCmd.OpenQuery "qryUpdatePrices"

Alternatively you can skip the saved query. (I put that first so you can see
how easy it is.) Just put this code in your button's click event (all one
line):

CurrentDb.Execute "UPDATE TableA SET CurrentPrice = NewPrice, NewPrice =
NULL;", dbFailOnError

Tried creating
an update query using the design grid, but am not sure what to put where.

The QBE grid is really good for easy select queries but it quickly gets
confusing when you want to do something more complicated. Your update query
would be easier to write in SQL than to do all the settings in the QBE grid.

 

[Guest]

Brilliant! Thank you so much. I corporated the single line SQL statement in
the on click event. Very nice! Thank you again.

 

[Granny Spitz via AccessMonster.com]

Brilliant! Thank you so much. I corporated the single line SQL statement in
the on click event. Very nice! Thank you again.

You're welcome, hon. Not so hard after all, is it? <g>