Can you open a specific form based on user input in the first form?

[nathan_a8]

I am making a database to store component information for a large mechanical device. Right now I have a "Component" table with an input form where a new component is initially added with it's hierarchy information. eg:

Component Form/Table
Field - Example input
System - Heave plate
Subsystem - Balloons
Assembly - Port Balloon
Subassembly - Mounting
Component - Balloon mounting screw #123
Component type - Screw

A componentID is then made based on a numeric value assigned to each System through Subassembly and the component name. For the above example the componentID could be 1.2.12.4.Balloon_mounting_screw_#123. From here I'd like to automatically open the "Screw" form that prompts the user to input information for the component they just created. eg:

Screw Form/Table
Field - Example input
ComponentID - 1.2.12.4.Balloon_mounting_screw_#123 (prepopulated)
Generic part name - Brass socket head screw
Thread size - M5 x0.80mm
Shaft length - 20mm
Drive type - hex socket


Drive size - 4mm


My problem is depending on the "Component Type" field in the "Component" table, a new form/table needs to be opened. If the component type was Ball Bearing instead, a different table ("Ball Bearing" table) with relevant information for a ball bearing would be needed. I'm experimenting with a navigation form with all the available component type forms available for the user to select, but this relies on the user to fill out the "Component" form and then navigate to the corresponding component type form, navigate to the correct new record and fill it out. Is there a way to have the component type field in the "Component" form also select which subform/navigation form to open next? Or based on the newly created componentID, open whatever form has that new component ID in its corresponding table?

Related to this, can each record in a table have a one-to-one relationship with a different table depending on the record? So if the component type is screw, the componentID needs to relate to the "Screw" table, if the component type is shaft the componentID needs to relate to the "Shaft" table.

The purpose of this database would be to pull reports like, pull all screws in the device to determine tool requirements for assembly, or pull all components with their relevant details, for that component type, that are in the Heave plate(System)/Balloon(Subsystem)/Port balloon(Assembly).

[ranman256]

the combo box holds 2 fields, the 'comp type' & table name
TYPE, TABLE
screw, tScrews
nail, tNails

when user changes the combo to the Type, reset the subform to the table:

in the master form, youd have the componant combo box (screw,nail,etc)
and a subform that will switch tables depending on the combo
then show those items.

sub cboComp_afterupdate
  subFrm.SourceObject = cboComp

end sub

the combo has bound column = 2 (table)
but user only sees col 1COMP type) via columnWidths property: 1;0

[nathan_a8]

It looks like I need to dig into VBA and not just the generic interfaces. I'll give this a shot, thank you!

[Minty]

I would slightly question needing a table for every component type.
That feels like a design error to me. If you add another component type do you really need to create a new table for it?

[nathan_a8]

@Minty, that is what I have come to so far. Ideally I would make this database NOT in Access and in MongoDB or some other NoSQL/Non relational database as the information needed for each component will be different. But I'm going to use Access for the user friendliness and easy forms and reports.
As for as needing a table for every component type. Idk how else it would work. If the component type is a screw, I need to know x, y and z dimension in a, b and c units. If the component is a coupler I need U, V and W dimension in d, e and f units. And so on, I could have a table with all dimensions in it but I'd need the info for hundreds of different possible characteristic dimensions of different types of parts. Is there a simpler way?

[June7]

Maybe Entity Attribute Value data model would suit.

tblParts
PartID
PartDescription

tblPartDims
PartID_FK
DimensionType (such as Length, Width, etc)
Dimension (number input)
DimensionUnit (inch, mm, etc.)