"Cascading" tables and forms (1 Viewer)

Zydeceltico · Mar 19, 2019

Hi All -

I have a DB to perform various inspections of products coming off our various manufacturing lines.

I have a main table (tblInpsectionEvent) that stores general data about a given inspection (e.g., Date/Time, Job Number, Inspector's name, etc.).

I then have four sub-inspection tables. There is no redundancy of fields across tables. Each table has approximately ten fields that are 110% unique to the area of activity reflected in one of the four inspection tables.

All of these tables have their own forms and primary keys.

There are four command buttons on the main inspection to open one of the four sub-inspection forms.

The PK of the main inspection table is passed as a foreign key to whichever sub-inspection form/table is opened.

There is always a sub-inspection ID associated with an InspectionEvent ID.

There is a 3rd table and form called Line Stop which only is opened and a record written in the infrequent circumstance when a mill line, welding line, fabrication line, or paint line stops running for whatever reason.

So here's the big question: I cannot wrap my mind around how to relate/link/access table or form Line Stop from an inspection/sub-inspection combined event given that I have four sub-inspections.

When form Line Stop is opened it is important to relate it to the current InspectionEvent and it's sub-inspection record also.

I've tried relating tblLineStop to the other tables directly several different ways and then playing with the forms/reports to see if I can get where I want but I'm just getting frustrated.

Ultimately I would like to have a row somewhere in some table that records FKs for the InspectionEvent_PK, a sub-inspection FK(e.g., InspectMill_FK), and LineStop_PK (or FK depending on design) - - - - but I cannot figure out how to make it work.

Thanks for any and all thoughts and insights!

Tim

jdraw · Mar 19, 2019

I haven't looked at details, but I note that any one of several "Lines" could stop. It seems to me you would want to identify which Line (mill line, welding line, fabrication line, or paint line) was stopped and supply the details. It seems to me that the tblLineStop is missing the Linename or number. But as I said, I haven't looked at details.

It might be useful if you take any given Line and "stop" it logically and identify exactly what you need to record. Easier to do this on paper and not get into the physical database until you have a plan.

Also, are the reasons for a Line Stop categorized in some manner specific to the Line involved. For example: would paint line stop because of oil canning, too narrow???

Good luck.

Zydeceltico · Mar 19, 2019

jdraw said:
It seems to me that the tblLineStop is missing the Linename or number. But as I said, I haven't looked at details.

It might be useful if you take any given Line and "stop" it logically and identify exactly what you need to record. Easier to do this on paper and not get into the physical database until you have a plan.

Thank you for your thoughts. They help.

I call "Linename" "Workstation" and that is related WAY back up the tree by product number to job number which is directly related to InspectionEvent. I have thought about making a direct link to workstation but the typical reasons for shutting down a line are more related to the product than the workstation but I'll toss that idea around a bit more. Products are Workstation specific.

Also - yeah - I've been scrawling it on paper too.

But the wording of your thoughts might have prodded me to try yet another pencil-to-paper "sketch."

Thanks,

Tim

Cronk · Mar 19, 2019

In your tblLineStop, add 2 fields
InspectionEvent_PK
SubInspectionTypeID

The latter field would be 1, 2, 3 or 4 depending on which sub inspection type. Add a lookup table for this data.

BTW, there's a compile error in frmLineStop

Zydeceltico · Mar 19, 2019

jdraw said:
Also, are the reasons for a Line Stop categorized in some manner specific to the Line involved. For example: would paint line stop because of oil canning, too narrow???

No, the reasons for a Line Stop are not currently categorized. But Yes, the paint line can stop for oil canning believe it or not. 98% of the time line stopping issues are activity specific. However - oil canning and only one or two other common issues - are that 2% that crosses all activities. Oil canning is a major issue that can manifest in any activity. A primary target if you will.

I struggled months ago - as you might remember - when I first began setting this up - to have only one table of issues "categorized in some manner specific to the Line involved" but I could never wrap my mind around the mechanics of designing table structure, forms, and reports to take that approach - especially because the vast majority of inspection items are truly activity specific. That's why I have multiple sub-inspection tables, I don't understand the other way of table design. I wish I did but I don't have enough experience.

jdraw · Mar 19, 2019

Does a LineStop event always occur during an Inspection? Do you have a diagram (even a rough one) showing what happens generally on a specific line, or at a specific inspection.
You could try describing this sort of thing. You'd be surprised how writing the steps down can help identify flow or some overlooked thing.

Zydeceltico · Mar 19, 2019

Cronk said:
In your tblLineStop, add 2 fields
InspectionEvent_PK
SubInspectionTypeID

The latter field would be 1, 2, 3 or 4 depending on which sub inspection type. Add a lookup table for this data.

I've tried adding InspectionEvent_FK to tblLineStop and then related tblLineStop to tblInspectionEvent on that field and it works like a charm - -except that I can't relate it to whatever sub-inspection I am in the middle of doing when I realize I need to stop the line.

In other words, I need to catch InspectionEvent_PK, the sub-inspection_PK, and the LineStop_PK all in one row in one record. It's pretty important to have the LineStop_PK and whichever sub-inspection_PK to be directly related some how.

In other words - just recording the "type" won't get me where I need to get.

Cronk said:
BTW, there's a compile error in frmLineStop

Thanks for that catch!

Zydeceltico · Mar 19, 2019

Zydeceltico said:
I've tried adding InspectionEvent_FK to tblLineStop and then related tblLineStop to tblInspectionEvent on that field and it works like a charm - -

I'm wondering if I should just not relate/link tblLineStop to either of the other tables and simple capture the PKs in unbound controls on frmLineStop and record in tblLineStop?

Is there a reason not to do this?

Thanks,

Tim

Zydeceltico · Mar 19, 2019

jdraw said:
Does a LineStop event always occur during an Inspection?

Yes, a LineStop always occurs during an inspection. But an inspection does not always result in a line stop. Line stops occur when a major flaw, mechanical breakdown, etc. occurs.

jdraw said:
Do you have a diagram (even a rough one) showing what happens generally on a specific line, or at a specific inspection.
You could try describing this sort of thing. You'd be surprised how writing the steps down can help identify flow or some overlooked thing.

I have done this (not recently) but I will do another one because I know you're right - maybe I missed something.

Zydeceltico · Mar 19, 2019

Cronk said:
Add a lookup table for this data.

I've actually been trying to envision a lookup table that would house the datatypes you describe as well as somehow relate each of the subinspection table PKs to that type thinking I could create some sort of bridge/junction table - but I'm having a mental roadblock from looking at this for a bit too long probably.

My general idea is that somehow that bridge/junction table would connect tblLineStop and tblInspectionEvent.

I'm going to take jDraw's advice and try to sketch this on paper and see if it makes sense.

jdraw · Mar 19, 2019

I'm wondering if I should just not relate/link tblLineStop to either of the other tables and simple capture the PKs in unbound controls on frmLineStop and record in tblLineStop?

Is there a reason not to do this?

I wouldn't as a first step. But, if you are in an inspection or sub inspection it seems there is always the possibility to hit the "Stop the Line". So that could be called from any Inspection/subInspection? And based on the conditions that caused the Stop, those conditions or related items would be recorded along with the Line and personnel involved. Seems this could be some sort of function called from various events/processes in your work flow.
Just a little more fuel for the fire....

Zydeceltico · Mar 19, 2019

jdraw said:
if you are in an inspection or sub inspection it seems there is always the possibility to hit the "Stop the Line". So that could be called from any Inspection/subInspection?

You are correct. But it would always be from a sub-inspection form - every time.

But you are also right - it would be best to be able to call a LineStop from anywhere.

Zydeceltico · Mar 19, 2019

jdraw said:
And based on the conditions that caused the Stop, those conditions or related items would be recorded along with the Line and personnel involved.

Exactly. Exactly for these reasons.

Cronk · Mar 19, 2019

I've tried adding InspectionEvent_FK to tblLineStop and then related tblLineStop to tblInspectionEvent on that field and it works like a charm - -except that I can't relate it to whatever sub-inspection I am in the middle of doing when I realize I need to stop the line

My mistake. Of course you also need include also the sub Inspection ID in tblLineStop.

Zydeceltico · Mar 19, 2019

Cronk said:
My mistake. Of course you also need include also the sub Inspection ID in tblLineStop.

I've tried that - the InspectionEvent_PK and the sub-inspection_PKs linked to tblLineStop but I get an error.

I think it is in the On_Load VBA code where I try to pass the InspectionEvent_PK into tblLineStop when the InspectionEvent_PK has already been passed to sub-inspection table/form.

Maybe this can be done - and I just don't know how to handle it.

Or maybe I am missing something else.

What do you think?

Zydeceltico · Mar 19, 2019

Zydeceltico said:
I think it is in the On_Load VBA code where I try to pass the InspectionEvent_PK into tblLineStop when the InspectionEvent_PK has already been passed to sub-inspection table/form.

Yeah - just tried it again by clicking cmdOpenLineStop. And the VBA editor opens up with this compile error: "Method or data member not found." and this line of code highlighted: "Me.InspectionEvent_FK=" . That happens on the Form_Load() Event.

The full code for that event is

Code:

Private Sub FOrm_Load()
    Dim rs AS DAO.Recordset
    If Not Trim(Me.OpenArgs & " ") & CLng(Me.OpenArgs)
        Set rs = Me.Recordset
        rs.FindFIrst "InspectionEvent_FK = " & Clng(Me.OpenArgs)
        If rs.Match Then
            DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
            Me.InspectionEvent_FK = Me.OpenArgs
        End If
    End If
End Sub

When I close the VBA Editor then frmLineStop opens but the InspectionEvent_FK has not been passed.

Is there a way to pass InspectionEvent_FK to two different tables sort of at the same time? Or does that break rules?

Is there another workaround?

jdraw · Mar 19, 2019

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.

Also, for your own sake, you may want to set up some variable to show what inspection/subInspection you are currently on, so that if LineStop is activated, you have a known position within your processing. You can also use a series of debug.print type statements and us the immediate window to follow your path through the various forms/modules.

It might also help if you could lead us through a scenario to get familiar with your database/application.

Zydeceltico · Mar 19, 2019

jdraw said:
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. It might also help if you could lead us through a scenario to get familiar with your database/application.

Let's pretend to do a Mill Inspection as that is the only form currently that has cmdButton to open frmLineStop. Also - all of this is happening on a rugged tablet.

Every Friday our scheduler puts out a new schedule with all of the info found in tblJobs which tells me an enormous amount about the job to be run. All of the info in tblJobs is unique to the 9 digit Job Number which is why I don't record anything more than I do during a process inspection.

So on any given day at the start of a shift (and various points throughout the day, I open the db, click Inspections from the Main Menu which opens frmInspectionEvent. I enter the job number, inspector name, first piece (yes or no), and the operators name. I walk to a mill and find all of the info for frmInspectionEvent in the operators paperwork. Then I actually begin the inspection of the various characteristics of the product being produced. This is when I click "Mill Inspection." I begin doing my inspection. There will be another step at a later date where part-specific data is recorded by clicking the drawing number on frmInspectMill" but that can wait - and I have that one figured out just not implemented yet. Soooooo.......while I am doing my part inspection - at the mill - if I find a serious anomaly (which I would want to record at that moment - and which is usually one of only maybe 10 general things) - I can stop the line while further inspection is done. That is when i would want to open frmLineStop and also when I would want to click ""Line Stop Start." I want to record the length of our line stops which vary depending upon many factors. ALso - sometimes - managers above me will override my line stop and I want to record who is responsible for the decision. To be fair, sometimes I make that call myself for border-line cases.

At this point I would like to either simply wait for the few minutes to resolve the line issue and click Line Stop End and have data recorded to all three tables OR design some means of returning to this record when the line begins running again - even if that takes a day.

jdraw said:
Also, for your own sake, you may want to set up some variable to show what inspection/subInspection you are currently on, so that if LineStop is activated, you have a known position within your processing. You can also use a series of debug.print type statements and us the immediate window to follow your path through the various forms/modules.

I would love to do this but I do not know how. Maybe a little guidance on how to set something like this up? An example? I'm very good with examples.

jdraw · Mar 19, 2019

See the Debugging link in my signature for Debug.print.

Zydeceltico · Mar 20, 2019

jdraw said:
See the Debugging link in my signature for Debug.print.

Trying my hand at debugging. Your page is quite clear but I think I may need to do even more reading up.

In the interim - while looking at the code - something occurred to me. I am using the same code on the load event of all sub-inspection forms as I am frmLineStop. The original purpose of the code is to pass the InspectionEVent_PK from frmInsectionEvent to whichever sub-inspection form I have opened (e.g., frmInspectMill). This is the code and it uses OpenArgs:

Code:

Private Sub Form_Load()
  Dim rs As DAO.Recordset
  If Not Trim(Me.OpenArgs & " ") = "" Then
  Debug.Print
    'See if record exists
    Set rs = Me.Recordset
    'MsgBox Me.OpenArgs
    rs.FindFirst "InspectionEvent_FK = " & CLng(Me.OpenArgs)
    If rs.NoMatch Then  'it does not exist so you need to create it
      DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
      Me.InspectionEvent_FK = Me.OpenArgs
    End If
  End If
End Sub

This code is in the Load event of all sub-inspection forms AND frmLineStop.

It becomes InspectionEvent_FK when it is placed in a sub-inspection form AND it is not yet recorded in the underlying table because the sub-inspection form is still open and has not been saved when I open frmLineStop.

Could this be the culprit? Is there another way to pass InspectionEvent_PK to both the sub-inspection form and then later frmLineStop if I open frmLineStop at all?

OR would I want to pass InspectionEvent_FK from the sub-inspection form to frmLineStop - - -

OR is there a simpler way to just transparently pass that numerical value across to frmLineStop as numerical value and the fields aren't literally related/linked in Relationships?

Something like Me.InspectionEvent_FK = Forms!frmSubInspection.InspectionEvent_FK placed in the OnLoad event on frmLineStop?

Grasping at straws now.

Thanks!

"Cascading" tables and forms (1 Viewer)

Registered User.

Attachments

Super Moderator

Registered User.

Registered User.

Registered User.

Super Moderator

Registered User.

Registered User.

Registered User.

Registered User.

Super Moderator

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Attachments

Super Moderator

Registered User.

Super Moderator

Registered User.

Similar threads

Users who are viewing this thread