Existing Subform not staying in sync with an Existing Main form change 1

[molly]

Hi. I put a small sample file for you to check if you have time. I am drawing a blank.

http://www.savefile.com/files/2084002

I need my subform to stay in sync with my Main form at all times. Initially, it does so. However, if I change my mind about 3 main form fields (cboSoldtoID, cboShiptoID, cboFormulaID), the subform does not simply change its corresponding field while keeping the pricing data alone.

For example, on an exisiting record, if i change the cbosoldtoID in the main form, the subform does change its soldtoID however, the data disappears. I need the same subform data to apply. All i did was change the soldtoID.
The original subform data stayed in its original "state" in the table.

Same effect if i change the cboShiptoID or cboFormulaID in the main form.

Thanks so much for the tip to fix. I figured that a small sample file would explain best. Molly

 

[RuralGuy]

You have set the LinkChild/MasterFields such that the SubForm will *only* display records where:
SoldtoID
ShiptoID
FormulaID
...all match. The form is doing exactly what you have asked it to do.

(RG for short) aka Allan Bunch MS Access MVP acXP ac07 winXP Pro
Please respond to this forum so all may benefit

 

[molly]

I know that this already works on a new record. The problem is when i change an existing record's say SoldtoID.
The subform data goes away.

I have this working perfectly on 2 of my other models. So I will keep looking to see what I am missing.

Thanks

 

[RuralGuy]

If you only want the FormulaID to stay consistant then you need to remove the:
SoldtoID;ShiptoID
...from the Link properties.


(RG for short) aka Allan Bunch MS Access MVP acXP ac07 winXP Pro
Please respond to this forum so all may benefit

 

[molly]

Hi, you are correct. I looked at my old models and the same thing was occuring. I didn't know this.

Unfortunately, I cannot base the sfrmQuoteDetail on only FormulaID.

The 3combo (formula, soldto and shipto) makes each price level quote unique to 1 customer. I cannot have 1 formula price per price level fits all customers. There are valid pricing reasons.

So I wonder, if on an existing record, I change eg Family Restaurant PA soldtoID #5 to the headquarters Family Restaurant HDQ soldtoId #3, then how can i get the subform to pick up on the change automatically and replace the 5 with a 3 in the soldtoID field ?

thanks for the effort.

 

[RuralGuy]

Based on what you have said so far, it seems the shipment is the central element instead of the SoldTo...ShipTo...Formula sequence. Are you trying to track reality or play What/If's with the shipments?

(RG for short) aka Allan Bunch MS Access MVP acXP ac07 winXP Pro
Please respond to this forum so all may benefit

 

[molly]

Hi and thanks for your nice help. In this example, pretend that I worked up a price quote only to find that i inputted the wrong soldtoID. So before we send the price quote to the customer, I need to fix an existing records soldtoID.

Or it could be a shipto error or change.

The data in the subform will stay the same except that i need the subform to stay in tune with the 3combo main form.

As for formula, they sometimes give me a dummy test formulaID. And I do the quote. Later, after the customer approves the formula, the lab and production planners will make up a Real formulaID. Hence, I would need to change my formulaID in the main form and I hope that there is a way for the subform to automatically pick up on the change.

Any ideas?

Molly

 

[RuralGuy]

Just so you know, new records in the SubForm will pick up the new values of the LinkChild/MasterFields but the form will not change existing records in the SubForm. Again, it still sounds like the form is upsidedown. You would need to impliment an Update query in the AfterUpdate event of the ComboBoxes to achieve the results you describe.

(RG for short) aka Allan Bunch MS Access MVP acXP ac07 winXP Pro
Please respond to this forum so all may benefit

 

[molly]

Yes, that what it seems. A need for some After Update deal.

I don't know how.

Molly

 

[RuralGuy]

It should be more natural to make the changes you need than this. Maybe just thinking about the db a little more will yield a solution. Is this something that will happen all of the time or a one shot deal?

(RG for short) aka Allan Bunch MS Access MVP acXP ac07 winXP Pro
Please respond to this forum so all may benefit

 

[molly]

HI. All the time. Not to mention, my boss sometimes picks the wrong soldtoID and formulaID and works on the costs. And later, he needs to fix the soldto or shipto.

Last spring, a major customer had a flood problem and closed a plant. So we had to revise quotes. In my real file, I have unit freight and unit warehouse. I only posted an small version to keep the help quest simple looking.

Molly

 

[RuralGuy]

I am surprised a "quote" does not have a unique number. Then you could pick a Customer and Quote and make whatever changes you need to.

(RG for short) aka Allan Bunch MS Access MVP acXP ac07 winXP Pro
Please respond to this forum so all may benefit

 

[molly]

Funny that you should say that. I was considering using a JunctionID for both the Main and Detail forms.

And then I could take out soldto and shipto and formula ID's out of the detail form.

What do you think?

Molly

 

[molly]

RG - You figured it out. This sample uses QuoteID as an autonumber junctionId in tblQuoteMain. Then I added QuoteID into tblQuoteDetail and took out soldtoID-shiptoID-FormulaID from the detail table.

Then I made the main and subforms child / master refer to QuoteID.

http://www.savefile.com/files/2084454

All works well now. Thanks for walking thru this with me.
I will donate later.
Molly

 

[RuralGuy]

Excellent Molly! Thanks for posting back with your success.

(RG for short) aka Allan Bunch MS Access MVP acXP ac07 winXP Pro
Please respond to this forum so all may benefit