opening a Form based on combo box selection

[ChunkyChats]

Hi, I've created a form to categorise failure modes. In a combo box I have 5 1st order categories. based on the users selection I would like a pop up to appear to select the 2nd order mode which is stored in 5 separate tables. I will create forms for these as well. I am not that great at VBA so was wondering how I could achieve this either by a Macro or VBA?

Many Thanks in advance for help

Steve

 

[Minty]

5 Separate tables sounds like a poor design, can you post up a picture of the layout of your tables, as I suspect you are not storing your data in a very effective manner?

 

[ChunkyChats]

Hi, and many thanks for the response. I've attached the skeleton of the database for your perusal. If you look on the EREMasterData Form the combobox in question is called BEATCasualFactors. Based on the selection I would like a pop up to appear for the user to select an option from the supporting form. So if 'Maintenance Factors ' is selected From 'tblSection3' would appear to select the second order failure mode.

Any advice re structure gratefully received. I used separate tables for the individual components based on I felt it would be easier for data analysis which is key to what I'm trying to pull together.

Steve

 

[Minty]

You know your data much better than we do, and looking at the fields you may be correct to structure it in that way. You have a load of Lookup fields in your tables, these are generally considered a bad idea http://access.mvps.org/access/lookupfields.htm I would change those to normal stored values with separate lookup tables.

As to your original question - the easy route would be to store the name of the form you want to open as an additional column in your list from BEATCasualFactors , then simply use an after update event referring to that column. You can't do this with your lookup fields - another good reason to avoid them.

 

[ChunkyChats]

Many Thanks Minty. Thanks for the advice re lookup tables. I read the article. I'm not quite sure what you mean by this statement 'I would change those to normal stored values with separate lookup tables' could you possibly explain a little more please?

Steve

 

[Minty]

At the moment the look up values are stored in a semi-hidden Access system tables. You can see them in the design view in the relationships window. Convert them (as in create a new table) to a real table with an ID and the other data you want associated with them in additional columns.

Effectively you are doing in a manual, viewable, useable way, what access has done in a obfuscated way.