Cascading Updates Help

[rgrwatson85]

I'm trying to set up a customer/order database for a school project. This is a final project and it requires that I build a database, albeit a simple one, for a real company. So my issue is that the prices for their inventory may very well change from time to time and I do not want the price changes to reflect in the previous orders. I set the referential integrity between tblINVENTORY and tblORDERS_SUBFORM(the junction table between tblINVENTORY and tblORDERS) to not cascade updates, but it appears that changing the price for an item doesn't really apply to this situation. I will attach a test copy of my DB with two orders that I am trying to have different prices for. Look in the Add/Modify Orders form to see what I am talking about. I put what I am trying to do in the remarks section in that form. Thanks for any input guys/gals.



Morgan

[boblarson]

First off - you need to understand what Cascade Updates really means. This is normally not used because it ONLY means that if the PRIMARY KEY of one table changes (for those not using an autonumber), then the child records will automatically have their foreign key (the primary key in the parent table) updated to match. It has nothing do do with anything else.

I can't look at your db right now because I only have 2003 here at work. But you would actually need to store the price in the table and to do that you would likely need to use code in the after update event of a combo box which selects the inventory item.

[rgrwatson85]

Ok, I look forward to hearing back from you. I have been working on this to no end, and I really want to get it right.

[rgrwatson85]

Well I think I may have figured it out. I added a Price field to tblORDERS_SUBFORM. I changed my ItemName lookup to also include the price in a second column. Then I used the code Me.Price = Me.ItemName.Column(1). Of course I had to change the controls used in calculating my subtotals, but that was a pretty easy fix.

If you wouldn't mind still looking at the database when you get a chance it would be greatly appreciated, just in case I did something really dumb and didn't even realize it!

[rgrwatson85]

And I'm the king of speaking too soon. Totally doesn't work! I'll wait until I get another pair of eyes to help...

[rgrwatson85]

Well I was hoping for more of a response, but I guess I'm just SOL. I'll just mark it as solved since nobody can or will help me.