Refresh field on another field recalculating

I have a form that has one field that is determined by a query; it is a price that updates based on the selection of a different field. When you change the selection, this field changes accordingly.

However, in this same form, I have a field that is set and puts the firm value in a field in a table; it is determined by using an Event Procedure in the AfterInsert of the form itself.

Private Sub Form_AfterInsert()
Me!PriceEach = Me!Price
End Sub

This works great unless the original field gets changed, it won't automatically reset to the new value. Is there any way to set this?
cozmo_trollAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If I understand you correctly: Use the AfterUpdate event of Me.Price to run this. This event will fire anytime the value in [Price] is changed by the enduser.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
After Insert *only* is triggered when a New record is added (aka inserted).

Try the Before Update event instead:

Private Sub Form_BeforeUpdate (Cancel As Integer)
Me!PriceEach = Me!Price
End Sub

mx
cozmo_trollAuthor Commented:
No, we actually use this in the AfterInsert of the form itself. The [Price] cannot be manually changed.

Thanks,
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

cozmo_trollAuthor Commented:
Before update doesn't do anything either... the PriceEach just stays the same as it was.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
AfterInsert is 'after' the fact ... after a Record has been Saved.  Setting a value at this time will only result in the record becoming Dirty (edited) immediately ...

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
The Form BU event triggers and time a Bound control has been changed.

"Before update doesn't do anything either."
Then something else is going on.

mx
cozmo_trollAuthor Commented:
DatabaseMX: I don't understand what you are saying; is this a statement or a suggestion to try?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Which post are you referring to ?

mx
cozmo_trollAuthor Commented:
AfterInsert is 'after' the fact ... after a Record has been Saved.  Setting a value at this time will only result in the record becoming Dirty (edited) immediately ...

mx

The Form BU event triggers and time a Bound control has been changed.

"Before update doesn't do anything either."
Then something else is going on.

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
My suggestion was @ http:#a34439058  ...

If that is not working, can you upload the db?

mx
cozmo_trollAuthor Commented:
The DB is 7MB and I don't know what I can remove to make it still reflect the issue.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
1) Compact & Repair ... that will shrink.
2) Zip up ... more skrinkage
3) explaining exactly how to reproduce the issue ...

mx
cozmo_trollAuthor Commented:
I think this needs to go on the AfterChange of the selector field on the main form; the Price and PriceEach fields are on the subform. How can I test/try this with the subform first?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Subform ?  No previous mention of that.  Do you mean On Change ?  Not really.  That event triggers once  for *every* character typed into a text or combo box.

mx
cozmo_trollAuthor Commented:
I guess I said the first field was determined by a query; this query is related directly to the value of a field in the main form. When that one field changes, it changes the price in every field in the subform.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
If you can upload ... that would probably get to this faster ... even 7MB ... ?

mx
cozmo_trollAuthor Commented:
I really don't want to upload this for the world to see; is there an email I can send it to? Or a way to delete it once you get it?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
eMail not allowed to individual users. You can requested via the Request Attention button that it be removed when done.

So, you tried:

Private Sub Form_BeforeUpdate (Cancel As Integer)
Me!PriceEach = Me!Price
End Sub

or as LSM suggested

Private Sub YourControlName_AfterUpdate (Cancel As Integer)
    Me!PriceEach = Me!Price
End Sub

?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
< When that one field changes, it changes the price in every field in the subform>

Is that what the trouble is? Or do you want this to happen?

If this is NOT what you want to happen: IF your subform is a continuous or datasheet form you'll not be able to get around this unless you bind that control to a field. An Access form used as a continuous or datasheet form is basically the same control repeated x number of times, and Access simply fills each of those controls with values from the bound source. If you include an unbound control, then Access considers that the SAME control with the SAME datasource, and therefore will show the SAME value in all controls.
cozmo_trollAuthor Commented:
MX: I have tried these, please specify exactly what item I should attach the code to? The form? The field I want to change? The field that gets changed first?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"I have tried these, "

How did you try these if you don't know where to attach the code ? And, not sure what I can say where ... beyond what I've already shown.

This code goes in the FORM BeforeUpdate event, which ends up looking like this:

Private Sub Form_BeforeUpdate (Cancel As Integer)
Me!PriceEach = Me!Price
End Sub

and this code goes in whatever the control name is when you want to make this update happen:

Private Sub YourControlName_AfterUpdate (Cancel As Integer)
    Me!PriceEach = Me!Price
End Sub

mx
cozmo_trollAuthor Commented:
MX -
I have tried putting the code in to many different areas. I am asking for your explicit direction so I can just do it right instead of trying it in 20 different spots.

When I put your code into the PriceEach field (which is the name of the field we need to change when the other field is changed) it does nothing.
cozmo_trollAuthor Commented:
Attached is the database.

Go to frmMasterOrderForm
In there, you will see a form for entering order information with a subform below for entering the order data. Currently, it is working properly up to this point... the user chooses a Price Level and then enters their order, the detail defaults the Price/Override to the Price, but allows the user to Override it if they choose to.

What I need to happen is when the user changes the Price Level, that both the Price and the PriceEach default back to that price. This works for the Price right now (after you refresh the form), but the PriceEach just stays the same. I want the PriceEach to follow the Price, but still allow an override if necessary. 2011-SHOW-SEASON-ORDER-DB-1.mdb
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I get the error message below when trying to open this db.

If you have an ACCDB upload that

mx
Capture1.gif
cozmo_trollAuthor Commented:
I don't know what an ACCDB is; we don't have anything special to open it?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
What version of Access are you working in ?

If A2003 or prior, then you need to upload a working db.  I was unable to open the mdb you uploaded.  The error message sort of implies it was maybe created in A2007/10 and converted back to A2003 ...?

mx
cozmo_trollAuthor Commented:
Ok, yes, it was created in 2007 but saved as 2003 so more people could use it. Attached is it saved back to 2007. 2011-SHOW-SEASON-ORDER-DB---2007.accdb
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
ok ... I will look at it in a bit ...

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sorry ... I don't see anything called Pricing Level ... do you mean PRICING combo box?

If so ... lead me through exact steps to reproduce the issue, including expected results ... referring to main form name, subform names ... etc., as appropriate.

mx
cozmo_trollAuthor Commented:
Mx-

In frmNewMain the item that changes is indeed the PRICING (PriceStructure) combo box. When this combo box is changed it will affect the PRICE box in the QryPriceLookup subform below. What the problem is, is that we also need it to update the PriceEach in the same subform. Right now, if you change the PRICING and refresh the form it correctly updateds the PRICE, but not the PriceEach. PriceEach needs to retain it's ability to be overridden with a manually entered price after the PRICING is changed.

To run a test, start with the PRICING set to Master Distributor, enter a new item AR02-B. The PRICE is 10. Now, change the PRICING to Volume Dealer and hit refresh... the price of that AR02-B changes to 20. This is all correct, but again, we just need the PriceEach to update to 20 as well.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sorry to be a pain, but I don't see anything called 'PriceEach' ... do you mean Price/Override ?

And anytime I change PRICING to a different value, whatever row I'm in on the subform below becomes Dirty.  And if I'm a New Record row ... I get an error "The value you entered isn't valid for this field"  ... and the row for the New Record becomes Dirty.

mx

cozmo_trollAuthor Commented:
PriceEach is the table name for Price/Override.

Also, the only 2 pricing levels that have pricing for AR02-B are Volume Dealer and Master Distributor; i just entered those for the trial.

Thanks
cozmo_trollAuthor Commented:
any thoughts on this?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
hi ... sorry, I had to leave for the Consumer Electronics Show in Vegas ... rtn sun eve.  have it in my out look calendar for sun night ... if still not resolved.

mx
cozmo_trollAuthor Commented:
ok, thanks
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"To run a test, start with the PRICING set to Master Distributor, enter a new item AR02-B. The PRICE is 10. Now, change the PRICING to Volume Dealer and hit refresh... the price of that AR02-B changes to 20. This is all correct, but again, we just need the PriceEach to update to 20 as well."

Well, I sort of see the problem ... but, to me it's kind of a Catch 22.  For a given line item, how are you expecting to distinguish an actual Price Override from a preset value that matches PRICE per PRICING ?  
If I 'run the test' ... then for QTY 10, we have  $10 (PRICE), $10 (PRICE OVERRIDE), $100 (LINE TOTAL) across the row. But what if I do an OVERRIDE to $15, such that LINE TOTAL is not $150.  Then, change to Volume Distributor, which makes PRICE $20.  But you already have an OVERRIDE in effect, so ... would not make sense to change that to $20.

?
cozmo_trollAuthor Commented:
The PRICE is the base price for that specific order, once a customers total order reaches a certain thresh hold, the salesman may make a decision to upgrade them to better pricing. The override will be used in only very limited situations so what we really want is if the PRICING gets changed that it defaults everything on that order back to that new base pricing, regardless of whether or not an override has been used. Can you help with this?
cozmo_trollAuthor Commented:
Were you able to get this figured out after my last post?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sorry ... so slammed ... have not forgotten about it, as well as a couple of other more complex Q's ...

mx
cozmo_trollAuthor Commented:
Ok, I just don't know what else to try and assume you had an easy fix just a matter of explaining why we do it that way.
cozmo_trollAuthor Commented:
Does anyone else have any suggestions on this since mx is busy? I need to have it functioning this week.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sorry ... I will try to look at this tonight ...

mx
cozmo_trollAuthor Commented:
thanks!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sorry again ... IT has refreshed several of the computers in our group to newer units ... and I have been slammed with fixing little issues.  Hope to have time tonight when I get home ...

mx
cozmo_trollAuthor Commented:
OK, thank you for the update... just REALLY need it by the end of the week if possible.
cozmo_trollAuthor Commented:
mx- I am desperate for this... I haven't explored any other options because you have been going to look at it for several weeks... do you have any suggestions?

thanks!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I am really sorry.  Extremely tapped out the last couple of weeks, not much time at home.  I can try and look again maybe tomorrow (sat).  In the meantime, you might want to hit the Request Attention button and as the mods to sent this out to additional experts to look at ...

mx
als315Commented:
Try this. Two queries were added and event for price selection was changed 2011-SHOW-SEASON-ORDER-DB---2007.accdb
cozmo_trollAuthor Commented:
als315,

Thank you for responding, but when I open the frmMasterOrderForm, it keeps asking for a parameter value for Combo11. Did you test this from the frmMasterOrderForm or did you create something completely different?

Thanks,
als315Commented:
I have not worked with this form. All changes were made in form frmNewMain. If you need to have it in frmMasterOrderForm, copy event from frmNewMain
cozmo_trollAuthor Commented:
I tried frmNewMain and it doesn't work either
als315Commented:
Sorry. Change query "QryPrice" to:
SELECT DISTINCTROW tblItemPrices.ItemID, tblItemPrices.Price
FROM tblItemPrices
WHERE (((tblItemPrices.PriceSheet)=[Forms]![frmMasterOrderForm]![frmOrder].[Form]![frmNewMain].[Form]![Combo11]));
cozmo_trollAuthor Commented:
Sorry, I don't understand. Where is "QryPrice"?
cozmo_trollAuthor Commented:
Also, I  don't see how I can make this work for frmMasterOrderForm correctly... it keeps asking for Combo11 before that would ever be established...
als315Commented:
It is better to use this
2011-SHOW-SEASON-ORDER-DB---2007.zip
cozmo_trollAuthor Commented:
It will not let me change the price level to test?
cozmo_trollAuthor Commented:
Ok, nevermind... it will let me do it now that I saved rather than a Read-Only copy... the Price/Override changes perfectly now, but anyway we can have the Price Change as well like it did prior?

Thanks!!!
als315Commented:
You will be able edit price, of you return query "QryPriceLookup" to your original version:

SELECT tblOrderDetail.OrderItemID, tblOrderDetail.OrderDetailID, tblOrderDetail.ItemID, tblOrderDetail.Qty, DLookUp("Price","tblItemPrices","ItemID=" & [ItemID] & " AND PriceSheet=" & [Forms]![frmMasterOrderForm]![frmOrder]![frmNewMain]![Combo11]) AS Price, [PriceEach]*[Qty] AS LineTotal, tblOrderDetail.PriceEach
FROM tblOrderDetail;
als315Commented:
Next version, with original QryPriceLookup
2011-SHOW-SEASON-ORDER-DB---2007.zip
cozmo_trollAuthor Commented:
Ok, it is still not changing the "PRICE" field, but I think I can get that with the previously made comments above. I will attempt to figure out what new Queries/Commands you added and implement this into our current working DB.
als315Commented:
Add string:
Me.QryPriceLookup_subform.Form.Requery
before
Me.QryPriceLookup_subform.Form.Refresh
in After update event of Combo11

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cozmo_trollAuthor Commented:
Perfect! Thanks so much!
als315Commented:
I don't need points, but you had to select correct answer for other users
cozmo_trollAuthor Commented:
I don't if I understand; I clicked ACCEPT AS SOLUTION for your solution because that is the only thing that worked?
als315Commented:
You can cancel request:
"To cancel this request and generate a request in Community Support for Moderator review, state your reason for objecting and click the 'Object' button. "
cozmo_trollAuthor Commented:
I didn't mean to award the solution to myself. Please open so i can award points to the correct expert. Also, i would like to request that the attachments be removed from the question.
als315Commented:
I think you can now close question again
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.