Update query not populating a table. Timing of calculating/updating issue?

[crobaseball]

Hello

I've got an update query which I'm using to update a table when a price change takes place. It then has to do 2 things: 1.) Update the past prices. 2.) Update the current price by the percentage increase. Here's the SQL code from what I've created in the Design View:

PARAMETERS PriceIncreasePercentage Decimal, SelectSeries Text ( 255 );
UPDATE Lines SET Lines.PrevPrice3 = [PrevPrice2], Lines.PrevPrice2 = [PrevPrice], Lines.PrevPrice = [Price], Lines.PrevLanded3 = [PrevLanded2], 
Lines.PrevLanded2 = [PrevLanded], Lines.PrevLanded = [Price]+[Freight], Lines.PrevDealerA3 = [PrevDealerA2], Lines.PrevDealerA2 = [PrevDealerA], 
Lines.PrevDealerA = ([PrevLanded]*[DealerAMultiplier]), Lines.PrevMargin3 = [PrevMargin2], Lines.PrevMargin2 = [PrevMargin], Lines.PrevMargin = 
Round((([PrevDealerA]-[PrevLanded])/[PrevDealerA])*100,2), Lines.Price = Round([Price]*(1+[PriceIncreasePercentage]/100+0.00001),2)
WHERE (((Lines.Series)=[SelectSeries]));

I think I've got this programmed correctly to update everything correctly, but I'm running into an issue of certain fields coming back with null values, specifically Lines.PrevMargin and Lines.PrevDealerA. These 2, are, of course, the ones which use other values to create a calculation for them. I think what is happening is that Access is updating things simultaneously, and the fields in use aren't populated for the calculation. What I need to happen is for things to be calculated sequentially, so that the proper fields have the right values when the calculation takes place. I know this can happen if I can some how put a "pause" or "break" in the code to have things update to a point, then run through the next round of updates/calculations.

Anyone have thoughts here?

[orange]

Please explain the Update the past prices. It doesn't seem to be a typical request. Most often, and we don't know your situation nor any details, one would update the Product Price
, but would not go back to old Orders or Purchases and revise Product Price. Normally you save the AgreedUponPice/CustomerPrice with the OrderDetail record.

[crobaseball]

The reason we save the old price is for our own internal cost measures. So what will happen: a supplier will update their prices. I go in and use this update query to update that fundamental price. We will then increase/decrease our margins, depending on whether we want to hold the price to our customers steady, or whatever else. But I have to save the old pricing information for us to then make this decision. i.e. - once the supplier pricing adjusts, I have to see the old pricing in order to determine our new pricing going fwd.

This update query reflects a price change from our suppliers. I go in, increase the prices by, say, 1%, and then need the past prices to cascade down. The current price becomes the previous price, the prev price becomes the next prev price, and the 3rd becomes the 2nd. This also occurs for margin, dealer A, landed, etc.

make more sense?

[crobaseball]

So old orders don't get updated to change prices, I simply need to keep the old prices to give a reference point for making our internal adjustments.

[crobaseball]

Regardless, if I absolutely have to make this 2 separate queries, I can do that, but I'd much prefer to do it as one. Like I said, if I can somehow indicate in vba for Access to make the changes in the order I want, this will work.

[orange]

OK, makes more sense.
Before doing an UPDATE query, I recommend you do a ReadOnly SELECT query to make sure you are about to update the correct records /fields. But I would not recommend the storage of calculated fields regardless.
eg
[PrevLanded]*[DealerAMultiplier]) and
Lines.PrevMargin2 = [PrevMargin], Lines.PrevMargin =
Round((([PrevDealerA]-[PrevLanded])/[PrevDealerA])*100,2

These values can be determined via query.
Can you post a jpg of your table LINES structure?

[crobaseball]

Actually, after doing some more searching on google, I found a nice solution creating a button control on a form.

What you can do is program the button to run a query, halt, run another query, halt, etc. until done. The link to the solution is here:
http://www.dbforums.com/microsoft-ac...nce-click.html

To summarize: create a form with a button control to "Run a Query" which is under miscellaneous. You then have to change the button macro to VBA code. Once you get that changed, go into the VBA, and instead of this:

DoCmd.OpenQuery "QueryA"

You put in this:

DoCmd.OpenQuery "QueryA"
DoEvents
DoCmd.OpenQuery "QueryB"
DoEvents
DoCmd.OpenQuery "QueryC"

Really you can create the button control to do anything, delete the commands for what it does, then put in the code as described, but what's nice is that it sets up the VBA to run a query in general so you have very little editing you then have to do.