How to populate fields depending on another field in a form

[tmd63]

I have a number of tables and a table listing the linked tables. These are a series of parts tables with data for each type, but I need to list and update/add depending on the type of part (so I may have a type capacitor or type resistor, with fields of value, voltage for capacitors, but value, power for resistors and I need to either add or update the values after selecting the type. How can I do this without using a sub field form?

 

[gemma-the-husky]

You generally would have supplementary tables to "feed" combo boxes to enable you to select from a prescribed range of values.
You need to include forms to maintain the values you want to allow.

Access offers a mutli-value field (MVF) which does the same thing in a seemingly more accessible way, but this actually does the same process behind the scenes, but using the access would prevent you moving away from access in the future.

If you don't have supporting tables, you would have to allow free text entry, and that is likely to result in errors.

Even simple things like a size will result in typos, and rather than showing, say 10mm where appropriate, you are likely to get 10mm, 10 mm, 10mmm, and just 10, if you don't control the input.

 

[GPGeorge]

I have a number of tables and a table listing the linked tables.

Spoiler: etc

These are a series of parts tables with data for each type, but I need to list and update/add depending on the type of part (so I may have a type capacitor or type resistor, with fields of value, voltage for capacitors, but value, power for resistors and I need to either add or update the values after selecting the type. How can I do this without using a sub field form?

A table listing other tables is a non-typical design choice. Maybe you can provide sample data to help us get the big picture here.

A screen shot of the relationships diagram would be good too.

Thank you.

 

[tmd63]

To give an idea these are tables that have a link by Part Number 'ONLY'.
So the relationship is a table which lists the part under strField1 and I can add the strTblName to each part via a separate table (if needed)
But depending on which table, there can be upto 28 strFields (strField1 to strField28). More could be added later.
I want a simple form that displays the correct field per part and allows new parts to be added by selecting the correct master table.
Normally each table is opened and the part fields visually inspected, But you have to find each part field on a word document and use multiple screens to cross check. I am trying to make a single form that will look like the original to make checking and entry easier.
Note: the public table formats can not be altered! Only data in those fields can be changed/added/checked.

 

[tmd63]

Therein lies a problem.

Do you think that Boeing has a parts table for every different type of part in an airplane? I can assure you they do not. They have/had too many parts tables but only because new applications refused to use the parts masters created by other applications.

You should have only ONE Parts table. There are ways to deal with multiple types of attributes if you are interested in fixing this problem.

Regardless, If you have many small lookup lists to manage, it becomes problematic to make a new table for each and if you embed the value list, the user can't easily change it. Here is a link to a mini-app that I include in almost all of my applications.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

If I could re-make the tables. I would have them in a single ODBC part database and use separation queries into the CAD libraries, but I have to work the other way round in this case (unfortunately).

 

[tmd63]

That is what I am trying to do, Using one form with 28 fields, the population of which is dependent on the name of the table that populates it. maybe using a subform.

 

[Mark_]

Non sequitur; had OP waited one week to post, he would have responded exactly 500 days later.

 

[tmd63]

If it was up to me. I would have created this with nomalised tables and used queries to pull the information together for the Altium Libraries but some backward non-programming idiot made this and now I have to sort their sh17.

 

[Gasman]

If it was up to me. I would have created this with nomalised tables and used queries to pull the information together for the Altium Libraries but some backward non-programming idiot made this and now I have to sort their sh17.

I once inherited a system, that while looked OK from the outside, was a mess inside code wise.
I just rewrote it properly.

 

[Mark_]

If it was up to me. I would have created this with nomalised tables and used queries to pull the information together for the Altium Libraries but some backward non-programming idiot made this and now I have to sort their sh17.

As you have been dealing with this over a year, what is the time COST for continuing to support it as is VS time COST to rework it to normalize your data? The moment you realize "It could have saved hundreds of hours" should be the point you start working on a better layout. Once you have built out a non "sh17" system, you then get to wade through the "sh17" to import and get it working properly.

 

[tmd63]

The down side is if the SH17 system was built by your boss and he refuses to change it. Even if it is SH17!

 

[Mark_]

The down side is if the SH17 system was built by your boss and he refuses to change it. Even if it is SH17!

So not a programming problem, a manglement problem. Unfortunately we can't help with that.