How to move records to a new ID or delete? (1 Viewer)

[caferacer]

Dear All,

I have an Equipment Form with a Supplier subform. The Supplier subform also has a subform, Equipment Pricing Records.

I have the ability to change the Supplier via a combo box, but Equipment Pricing Records become orphaned.

What I would like to do is when a Supplier is about to be changed, for the user to have the option to either a) delete the sub records, or b) the sub records to move across to the new Supplier.

I know I will need to use the after update event once the delete or move decision is made, but I can’t think how to identify the records and then either delete or move them to the new Supplier ID.

The EquipmentID will never change.

Fields are;

EQUIPMENT form;
EquipmentID

SUPPLIER form;
SupplierID
EquipmentIDFK

PRICING RECORD form;
PricingRecordID
SupplierIDFK
EquipmentIDFK

Any pointers would be appreciated.

Thanks and regards.

Mark.

 

[MarkK]

See how your pricing record table has a foreign key on both equipment and supplier? One supplier might sell many pieces of equipment, and one piece of equipment might be sold by many suppliers, and that is a three table solution, and a textbook many-to-many relationship.

You should have two "main forms," Equipment and Supplier, and a common subform, PricingRecord. On the equipment mainform, this will show who supplies the item and at what price. On the supplier mainform, this will show what is supplied and at what price.

There should be no changing of IDs, only the adding of records, and sometimes, but more rarely, deleting.

Hope that helps,

 

[caferacer]

Hi MarkK,

Hmmm, thanks for the reply. Have given this some thought.

I have got a junction table, which both Equipment & Supplier are linked by, but the Pricing Record form pulls the Equipment & Supplier ID's straight from the Equipment and Supplier tables not the Junction table? Perhaps this is where the possible problem and therefore solution may be?

My DB is fairly large - I'm gonna re-create a another to model the problem and play about with this.

I do have two main forms a) Equipment with subform Suppliers with Subform PricingRecord and b) Vendors with Subform Equipment with Subform PricingRecord. PricingRecord is the same form and therefore common.

Instead of having
main(Equipment)>sub(Supplier)>>subsub(Pricing Record)
should main form 1 be,
main(Equipment)>sub(Supplier) & sub(PricingRecord).

And then likewise for main form 2, instead of having
main(Supplier)>sub(Supplier)>>subsub(Pricing Record)
should it me
main(Supplier)>sub(Supplier) & sub(PricingRecord).


Thanks for the help
Mark.

 

[MarkK]

I don't think you got it there.
Say you have tables . . .

tEquip
EquipID (PK)
EquipName

tSupply
SupplyID (PK)
SupplierName

tPrice
PriceID (PK)
EquipID (FK)
SupplyID (FK)
Price
IsWholesale
ExpiryDate

On the equipment main form you can put a Price subform, but the Price table has a foreign key to Supplier, so you put a combo on the field, and then the price record, all by itself, shows the supplier. You don't need the supplier table to be present, so your scheme you have here . . .

main(Equipment)>sub(Supplier) & sub(PricingRecord).

. . . should be, IMO . . .

Main(Equipment) >>> PricingSubform (Which shows the supplier)

And that's it. You don't need the supplier table in that subform. And to add a supplier, you only need to add a Price for that supplier, so in a new record, select a supplier from the combo on the foreign key, and the link is made. All in the Price table.

See it now? The data in the join table is much richer than you are giving it credit for.

Hope this helps,

 

[caferacer]

Hi,

Yeah, I think I can see where your coming from now. I suppose the main reason why I had the 'mid form' (if you can call it that) was to use it to group the equipment by supplier therefore restricting the pricing records down to only equipment supplied by a particular supplier.

But, that should be achievable just through using the Join table without the added layer of complication of a 'mid form', which is what I think you are saying?

Sorting and grouping of records into supplier and equipment I guess will have take place through the row source of the pricing form or create a bespoke query to base the pricing form on? This will take the place of the 'mid form'

I have created my model DB now so will have a play with this tomorrow.

Thanks again.

Regards

Mark.

 

[MarkK]

You're welcome, and feel free to post your sandbox db if you want. There's a cool pattern you can do with the pricing subform so you can use it on both main forms. It's easier for me to do than to describe how to do.

Yeah, that join table is more powerful than it seems like at first glance. In lots of scenarios it's actually the join table where the most activity occurs!!!

Cheers,

 

[caferacer]

Hi,

I have attached my sandbox DB.

Its presently configured how it works today and therefore includes the issue and therefore reason for raising this thread.

Apologies, terminology is a little different than in the thread. Need to substitute ‘products’ for ‘equipment’.

The main form is frmProducts. What I want to be able to do is move a Product and all its Pricing Records from one supplier to another.

The supplier combo is highlighted yellow. If I change the current SupplierID=51 (Great Gardens), to SupplierID=52 (Green Space), then the pricing records become orphaned, which is the problem?

Thanks once again for the help MarkK.

Regards

Mark.

 

[MarkK]

Here's the pattern I mean. Note the new forms called fProduct and fSupplier and the fPriceListDS subform. The subform checks the parent form's name, and hides the field on which the link is made to the mainform, so on the fProduct main form, you can simply select a new supplier in the Combo to add that supplier for that product. Then, double click the supplier combo (or the Product Combo) to go to that item in the other form. Note that when you add a child record in one parent form, it also appears in the other related parent form, since it IS the join table between them that drives the subform.

It's an apparently complex many-to-many relationship, but this makes it as simple as it can get, IMO.

Does that make sense?

 

[caferacer]

Hi Markk,

Thanks very much for this. I'm working away today, back tomorrow, when I will be able to take a look.

Regards
Mark

 

[caferacer]

Hi MarkK,

I see where you are going with this. What is important functionality for us is to be able to select a Product from a particular Supplier filtering to limit the Pricing Records accordingly. Or alternatively, the other way around, selecting a Supplier and then filter Pricing Records by Products.

Also, have the ability to switch the Supplier for a particular set of filtered records to another Supplier en masse, without orphaning any pricing records, which is the problem I was having. With this new layout I am not sure where to start to achieve this functionality? Is it possible to incorporate this into this example without too much effort?

Although in this sandbox DB it seems ridiculous functionality to want to have, but with the complex information we have for the real Products it is important?

Thanks for the continued support.

Regards

Mark.

 

[MarkK]

I don't understand what it means to "select a Product from a particular Supplier filtering to limit the Pricing Records accordingly." What is a "particular supplier filtering," or are you missing a comma?

Also, do you want to move prices or copy them? To move them, edit the price's foreign key to the ID of the target supplier. To copy them, first make copies, then edit the ID, as above.

But I don't get it. Typically, one supplier will offer products at specific prices. Why should this change en masse or be moved to a new supplier?

 

[caferacer]

Yeah, that is terribly written. Just need the ability to filter the PricingRecords by Supplier and Product from one combo. Therefore the combo would list

ProductA|SupplierA
ProductA|SupplierB
ProductA|SupplierC
ProductB|SupplierA
ProductB|SupplierB
ProductB|SupplierC
…..
…..
With one combo selection I’d like to be able to choose Product & Supplier and have no other pricing records shown apart from those relevant to the combo selection. Sorry about my terminology.

The database is being used in support of a bid management tool which has the ability to produce some synthetic pricing, scenario planning and forecasting. Once deals and contracts are won (hopefully) and a supply solution is chosen, then essentially the database has served its purpose for that bid. I do understand your reason for questioning, but it is very useful to have this functionality. Having the ability to switch supplier once selected from the combo saves going through them all individually. At least that was the plan until I noticed the pricing records we being orphaned. My original DB i posted demonstrates the functionality I need, but orphans the pricing records when the supplier is changed instead of moving them. I believe your solution should eliminate the orphan issue, but with my limited Access & VBA ability, I am unsure how to reproduce the combo selection and filtering and then switching of supplier ID (with pricing records) into my sandbox DB?

Hope this is as bit clearer.

Appreciate your time and help.

Regards

Mark

 

[MarkK]

I don't get this requirement, "the ability to filter the PricingRecords by Supplier and Product from one combo."

I don't see how that is possible. In respect to price, Supplier and Product are distinct and discrete criteria, and cannot be accurately represented in one combo. By definition, a price "address" requires the selection of one supplier AND one product, which would be a minimum of TWO combos.

Unless I don't get it, which happens . . .

 

[caferacer]

The functionality is in the attached database in the frmProducts - the combo hightlighted in blue. This is how its being used at the moment.

From carrying out some more research today, with regard to the attached database, to achieve my original aim of the thread, I am thinking it’s some form of update query I will need to run, so If there are Pricing Records present and the Supplier Name is changed via the (frmProductSuppliers) combo (highlighted yellow), then all (frmPriceList) Pricing Record SupplierID’s will change to match the combo selection.

In reading the information on the update query, it may be worth providing some kind of initial message box to ensure the user is aware of what they are about to commit to. I have the ability to do this, but the VBA and SQL to achieve the ID changes is too advanced for my skills.

Regards

 

[MarkK]

Sorry, I don't understand the advantage of this approach. I think I posted a simpler solution to this problem in an earlier thread.
Cheers,

 

[caferacer]

Thanks for all your help anyway. I'll try and muddle through and create some VBA/SQL code.

Regards

 

[MarkK]

Yeah, you bet, all the best.