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

 

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

 

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