"Cascading" tables and forms

[Zydeceltico]

Can you take one form (Inspection) and a subInspection, then lead us through the LineStop process (step by step)? Just using plain English describe what should happen and what you need to record.

I've tried several different code phrasings this morning trying to pass InspectionEvent_FK from frmInspectMill to frmLineStop while they are both open without success.

I am now convinced (with nothing to back it up) that I can't pass that value from one form to the other because it is not actually saved on the sub-inspection form when I open frmLineStop. the sub-inspection form is in a Dirty state.

I also realized that when conditions require a line stop, I should actually save the sub-inspection form data to its table before opening frmLineStop (or rather perform a "save to table" at the same time as opening frmLineStop).

So one command button that:
1) saves the sub-inspection data;
2) collects the value of InspectionEvent_FK from the sub-inspection form while it is closing;
3) opens frmLineStop and
4) passes the value of InspectionEvent_FK to Me.InspectionEvent_FK

Now let's see if I can figure that out.

Any assistance is greatly appreciated!

Thanks as always,

Tim

 

[jdraw]

Agree generally, but instead of working with the physical database and "thinking about foreign key to pass etc", work with the logical requirement.
For example, you have recognized that your form loads all use similar code, so it's possible to put that into a function and pass specific parameters to identify the exact form and related process depending on which sub-inspection is active. The details of what should be recorded and where will also be dependent on that sub-inspection.

There will be sample code somewhere to have a callable function to load one of form A,B or C. I'm sure someone here will have an example if they happen upon this thread.

I don't have any generic data flow software at the moment, but that or pencil and paper may be the quickest way to identify (and test) the "common theme" for sub-inspection
and potential "line stop" logic. Once you know and have tested the logic required for solution, you can do the required programming for physical implementation.

Again, you know your processes and work environment better than any reader. Readers have to understand your business and requirements before we can offer focused responses.

 

[jdraw]

I agree with your general approach to the sub-Inspection -- that is if LineStop is needed, you must save your current info to table even if that info is not complete (not sure how much you need for a sub Inspection interrupted with a Line Stop).

I don't think your current Linestop table requires all of the InspectionEvent FK fields.
Since each LineStop has its own ID/PK, you might reduce your fields to InspectionEventID and InspectionType (mill, Fabrication, Paint etc). In reality only 1 of those fields would be populated for any given LineStop - as I understand your set up.
You may not need InspectionType, since it can probably be derived from the Inspection_Event table.

Good luck.

 

[Zydeceltico]

I agree with your general approach to the sub-Inspection -- that is if LineStop is needed, you must save your current info to table even if that info is not complete (not sure how much you need for a sub Inspection interrupted with a Line Stop).

I don't think your current Linestop table requires all of the InspectionEvent FK fields.
Since each LineStop has its own ID/PK, you might reduce your fields to InspectionEventID and InspectionType (mill, Fabrication, Paint etc). In reality only 1 of those fields would be populated for any given LineStop - as I understand your set up.
You may not need InspectionType, since it can probably be derived from the Inspection_Event table.

Good luck.

While at lunch, I had the same "a-ha" as you mention - all of the sub-inspection info can be derived from the Inspection_Event table.

I have it working well now - for a single sub-inspection form.

I have this code on the Form_Load event for frmLineStop:

    Me.InspectionEvent_FK = Forms!frmInspectMill!InspectionEvent_FK

- - works great.

And I have this on frmLineStop Unload:

Private Sub Form_Unload(Cancel As Integer)
    On Error Resume Next
    DoCmd.Close acForm, "frmInspectMill", acSaveYes
    DoCmd.Close acForm, "frmInspectionEvent", acSaveYes
End Sub

Now I need to figure out how to replace the hard-coded "frmInspectMill" with some sort of variable so when frmLineStop closes it replaces that with the name of whichever sub-inspection form is open. Another learning curve.

Do you have any examples or guidance on your website?

I looked at a lot of it last night. Lots of useful info. Thanks!

Tim

 

[Zydeceltico]

Will something like this work if placed on every sub-inspection form's Click event for the button to open frmLineStop?

Private Sub cmdOpenLineStop_Click()
Dim NameToPass
NameToPass = Me.Name
    DoCmd.OpenForm "frmLineStop", , , , , NameToPass
End Sub

 

[jdraw]

If each of your sub inspections have the same parent Inspection info eg job,operator etc, then you might consider Form and subform(s). It seems that you open the Min form, select job or Inspection; and if Inspection you can select 1 of several (mill, paint, fabrication.... sub inspections; and that may simplify your interface.
I'm attaching an article (that includes a link to an mdb database showing his example) that deals with a Form and multiple subforms. The key point is that on his subformControl he has multiple tabs and he loads the proper subform to the appropriate tab(SourceObject) dynamically. It shortens some code. You would have to review his comments and his sample database to see if it is applicable to your set up.
It does apply to real Form/subform construct.

Link to dynamic subform loading and the database link within is https://www.databasejournal.com/img/2006/04/DynamicSubformLoad.zip

Do you have an updated version of your database?

 

[Zydeceltico]

Do you have an updated version of your database?

The most recent within the last half-hour.

And thank you for the rest of your post. Reviewing that now.

 

[Zydeceltico]

If each of your sub inspections have the same parent Inspection info eg job,operator etc, then you might consider Form and subform(s). It seems that you open the Min form, select job or Inspection; and if Inspection you can select 1 of several (mill, paint, fabrication.... sub inspections; and that may simplify your interface.

Your assessment is correct. The one "hiccup" is with weld tests. The welder settings need to be collected as well and one of the welders has so many required settings that it lives on a 3 tab form of its own which makes it challenging - at least for me - to figure out how to make it a subform.

The other to bear in mind is that the eventual front end of this DB is going to live on a 12" rugged tablet so I am trying to be cognizant of screen real estate and usability.

Otherwise - I really appreciate the ease of use of form/subforms.

 

[Zydeceltico]

.....which makes it challenging - at least for me - to figure out how to make it a subform.

Actually, I suppose I could approach the welder settings the same way I am currently looking at handling LineStop and just pass the WeldTest_FK to the welder settings forms the same way. Then I could have all of the sub-inspections as subforms to the main form. They'll all fit real-estate-wise that way.

I'm checking out the link you sent now.

 

[Pat Hartman]

Please forgive me if I step on anyone's toes. I skimmed the discussion and didn't see any comments on the design flaw so I will comment on how to address the design flaw and once you do that, the problem becomes moot.

Without an analysis of your design and the process you are modelling I cannot be certain that you should have four separate subinspection tables so I will assume that you need them although you should think about the possibility of adding a fifth subinspection type and how that would impact the design.

Moving on. The solution -assuming you really need four subinspection tables - is to create an intermediate single table called subinspection. It would hold all the common fields such as primary key (autonumber), subinspection type (so you know which of the 4 tables need to be referenced), et. Then you would replace the sutonumber PK of the four subinspection tables with a long integer. The long integer will be populated by the PK of the new master subinspection table. Essentially what you are doing is creating a PK that will be unique across all four subtables. When you need to add a subtype1, you add a row to the master subinspection table with type = type1. You then insert a row in the type 1 table with a foreign key that points back to the subinspection table. When you need to add a subtype3, you add a row to the master subinspection table with type = type3 and then add a new row to the type3 table.

You will of course need a one time conversion of any data that currently exists in the four sub tables so that the primary key values point to a row in the new subinspection table.

The four subinspection tables are related 1-1 (PK to PK) with the new master subinspection table.

Once the schema has been changed, ALL relationships that used to point to individual subinspection tables would be changed to point to the master subinspection table and that will allow you to define RI and all your problems go away.

Think about this. I can make a different suggestion if you decide that you might actually need to add a fifth subinspection type because if that is a possibility, the schema I suggested is not the best choice.

The schema I suggested is frequently used in cases where an application has multiple entities such as students, teachers, vendors, guardians, etc and all of those have addresses and contacts and you want to use the same address and contact tables for all entities. In this schema, a student may also be a teacher, a vendor, and a guardian and you would not want to have to manage multiple duplicate address and contact records for each entity. Your situation probably doesn't have the same type of multiple uses.

 

[Zydeceltico]

Think about this. I can make a different suggestion if you decide that you might actually need to add a fifth subinspection type because if that is a possibility, the schema I suggested is not the best choice.

Thank you for the thought and great detail. I very much appreciate the detailed explanation - very clear.

What you are suggesting is possible and I can see how it would make my other concerns "moot."

One question: Why does the potential of adding another sub-inspection call for a different schema? Is the 5th sub-inspection or is the actual "adding" of a new sub-inspection to this schema? In other words, yes - I can conceive of one or two other sub-inspection types that I am currently not reflecting. If I were to add these sub-inspection type tables as "placeholders" now would that allow for using this schema?

In that light - yes - a different suggestion is likely a good idea.

Also - and just as background: It has been suggested to me numerous times that I may not need multiple sub-inspections - but I - for the life if me - cannot wrap my mind around what that would look like functionally or how to utilize said table design if I could envision it.

I have a major mental roadblock when I look at the various things I need to record for any given TYPE of inspection and conceiving of how to do it any differently. Trust me - it's not because I'm stubborn - (although I am - LOL) - I know you folks are much more experienced than me - - I just can't "see" it and don't know where to start.

 

[Pat Hartman]

Why does the potential of adding another sub-inspection call for a different schema?

Adding additional tables affects all the queries, forms, and reports that will need to reference them.

Please post the definitions of the four tables so I can better understand what the subinspections are all about.

The other solution, which is what I would use if I faced additional "tables" in the future, is called an entity-attribute-value model. Here's a definition to read - https://en.wikipedia.org/wiki/Entity–attribute–value_model

In your case, we would assume that all the unique attributes of the four inspection tables belong to one set of attributes.

This model requires a meta data table that defines the attributes for each inspection type.

tblSubInspectionMetaData
RecID
InspTypeID
FieldID
FieldDataTypeID

So data would look like
1, type1, field1, double
2, type1, field2, date
3, type1, field3, text
....
25. type2, field88, single
26. type2, field89, single
......
99. type3, field102, single
100. type3, field3, text (fields can be repeated in multiple sets)

 

[Zydeceltico]

Please post the definitions of the four tables so I can better understand what the subinspections are all about.

I had to break them up. File size was too big.

 

[Zydeceltico]

The other solution, which is what I would use if I faced additional "tables" in the future, is called an entity-attribute-value model.

In your case, we would assume that all the unique attributes of the four inspection tables belong to one set of attributes.

This model requires a meta data table that defines the attributes for each inspection type.

tblSubInspectionMetaData
RecID
InspTypeID
FieldID
FieldDataTypeID

So data would look like
1, type1, field1, double
2, type1, field2, date
3, type1, field3, text
....
25. type2, field88, single
26. type2, field89, single
......
99. type3, field102, single
100. type3, field3, text (fields can be repeated in multiple sets)

You've mentioned this to me before.

I'm intrigued - but - I have no idea how or where to begin building the visual tools/forms that we'll need to use....or how to query this - - I am assuming probably by field type. But truly - I do not comprehend what the road past building that specific table looks like.

On the other hand - I do suspect that IF (notice the BIG if) I were able to grasp the concept it would result in a more robust db & table design - which in our circumstance could very well be a benefit - - I just can't fathom how or where to begin and go forward - and I tried for quite awhile the first time you mentioned this to me many moons ago.

If there is an example db somewhere floating that I could examine I *might* be able to grasp the "physics" of it.


---Just so I've said it - - I should clarify that if new tables were required in the future it would continue to be activity specific like all of the current inspection tables are - - they are discrete to a specific "activity" meaning there is no overlap between or among the sub-inspection tables - other than overarching job details which are currently caught in my main table, tblInspectionEvent.

 

[Zydeceltico]

I'm attaching an article (that includes a link to an mdb database showing his example) that deals with a Form and multiple subforms.
It does apply to real Form/subform construct.

Link to dynamic subform loading and the database link within is https://www.databasejournal.com/img/2006/04/DynamicSubformLoad.zip

I studied the link you sent. That is a very elegant way to handle the loading dynamics definitely. The general approach of using a tabbed subform is appealing - and if that does work out for me then I would definitely attempt the dynamic loading.

I'll have to play around with it and see. Definitely an elegant means of handling multiple subforms under one umbrella Main form.