VBA or SQL Statement?? (1 Viewer)

VygonDBA

Registered User.
Local time
Today, 03:10
Joined
Oct 24, 2012
Messages
12
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

Super Moderator
Staff member
Local time
Yesterday, 22:10
Joined
Jan 23, 2006
Messages
14,430

Mihail

Registered User.
Local time
Today, 05:10
Joined
Jan 22, 2011
Messages
2,373
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

Registered User.
Local time
Today, 03:10
Joined
Oct 24, 2012
Messages
12
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

Registered User.
Local time
Today, 03:10
Joined
Oct 24, 2012
Messages
12
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.
 

Users who are viewing this thread

Top Bottom