How to populate fields depending on another field in a form (1 Viewer)

[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.

 

[Pat Hartman]

These are a series of parts tables with data for each type,

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

 

[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).

 

[Pat Hartman]

In your own application, you can define the tables however best suits the application. There is no reason to allow external forces to cause you to make poor schema design choices.

But depending on which table, there can be upto 28 strFields (strField1 to strField28). More could be added later.

If you are not going to normalize the schema, you can't normalize the forms. Each table needs a separate maintenance form since the validation rules, assuming you bother with validation, will be different.

Use the value in strTableName to choose which form to open.