VBA or SQL Statement?? (1 Viewer)

[VygonDBA]

Hi all,

I have a conundrum that's driving me mad!

I have a categories table with 4 categories that are user selectable on the front end form (P, INCR, SP & VUK), I then have 4 tables that define various failure modes applicable to the categories (tables named FailureModeP, FailureModeINCR etc).

We need a solution whereby the user selects a category in the front end form and then based on that category selection the next drop down box on the form (Failure Mode) would have a list of failure modes that correspond to the categories table. i.e. user selects INCR category and only the failure modes in the FailureModeINCR table are available for selection.

My initial thought was to revise the row source SQL statement on the Failure ID Combo Box on the form to incorporate a multiple IF statement however, would it be better to use VBA?

Many thanks for any suggestions.

 

[jdraw]

I'm not sure I understand your situation. It sounds similar to the use of Cascading combo boxes, where, if you choose type A, then only Failures related to type A are available for selection.
If this is your case, there are 2 related free videos at Datapig
http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html

Good luck.

 

[Mihail]

Bad design of DB.
For sure you don't need 4 tables, one for each failure mode.
You need a table with failure names.
One for failure modes
... and one more (so named "bridge table") with 2 fields:
ID_FailureName
ID_Failure mode

 

[VygonDBA]

Bad design of DB.
For sure you don't need 4 tables, one for each failure mode.
You need a table with failure names.
One for failure modes
... and one more (so named "bridge table") with 2 fields:
ID_FailureName
ID_Failure mode

I agree but that's unfortunately what I'm left with and I'm not allowed to redesign it "because it works"!

 

[VygonDBA]

I'm not sure I understand your situation. It sounds similar to the use of Cascading combo boxes, where, if you choose type A, then only Failures related to type A are available for selection.
If this is your case, there are 2 related free videos at Datapig
http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html

Good luck.

Yup this is what I'm after, perfect thanks. Some redesign is in order me thinks! Thanks again.