Relational problem: Record selection that depend on others

[bertusvanrooy]

Hi there,

I have developed an application that is used as a customer equipment (Computers etc.) survey tool (for current equipment with costs). The aim is to feed all current data into the system per equipment i.e. the monthly rental, service charges, depreciation etc. so as to calculate the real Total Cost of Ownership.

This part I have done and it works well. My challenge is as follows.

I have a Many-to-Many table relationship that links Computers to the Options that they can have. For instance a PC Model A can be configured with a particular hard drive i.e. Seagate 40GB. This same Seagate 40GB can also be used in PC Model B. Each PC and Option has a price associated with it.

Please see attachment for relationship that I use for this.

I want to develop a PC configurator that will allow the user to select a particular PC from a list and then show the Options available for this model (This part I have no problem with). The user must then be able to select the options he wants through a control like Checkboxes maybe. It should keep a running total of the PC cost + the options selected and store it against a custom configuration / quote that can be recalled at a later stage.

What makes it a bit tricky is that some Options are dependant on others. For instance, Interface Type A requires that Bracket Type B also be selected. Also if I Deselect Interface Type A, Bracket Type B must also be Deselected, maybe with a message to the user warning him about this.

Any help in steering me in the right direction will be highly appreciated.

Regards,

Bertus van Rooy

 

[sambo]

One Solution (although it would require some duplication):

Drop a child table off of Tbl_Options... call it Tbl_OptionsSub.
This will be a One to Many (Options to Options Sub). For each Record in Tbl_Options you can now drop down many OptionSub(s) that match (exactly) records that are already in Tbl_Options.

--tblOption--
OptionID  (Primary Key)
Option
Price

--tblOptionSub--
OptionSubID  (Primary Key)
OptionID (Foreign Key From tblOption)
SubOption  (This field represents all values from tblOption that are also SubOptions of the current record)

Now when you update tblOption!Option on your form, you can check to see what the SubOptions of the newly selected Option are. If you find a match in the two lists, then you will Select(or Deselect) that Option from your form.

So if tblOption includes Options A,B,C,D...
And Option A includes SubOptions B,D...
Now if you Select Option A on your form, you can go check to see what the related options are...
In this case B and D are related to A.
So you can now add Options B and D to your RunningSum of Options...

Let me know if you need help with the code.

Good Luck..

 

[bertusvanrooy]

Options - Sub_Options Questions

Hi Sambo,

Sorry for only replying now, I have been away for a while.

Thanks for your idea, it looks as if it could be the answer.

I just have one or two questions about the practical side of it. Seeing that I will have a Master Item i.e. the PC, with Options and Sub_Options, how would i approach the form layout?

I see the workflow as follows: Select a Master Item from a Combo box, then having the Options displayed in a sub_form. Should I then have another sub_form for the sub_options?

I would appreciate any ideas and maybe some help with the code to achieve this.

Thanks again for your input.

PS: I have attached a screenshot of my current Maser Item edit screen.

Bertus

 

[bertusvanrooy]

Sorry, here is the attachment

Sorry, here is the attachment