Pass PK to popup form as FK - Modal (1 Viewer)

[MajP]

So if the user has opened frmParent and made a selection from cboChores and gone on to open frmChildOne - how can I stop the user from closing frmChildOne without entering any data?

One way is to use the unload event and you can cancel it by setting cancel = true. You probably want to provide a message saying they cannot close until some data is provided. Or at least give them some option to confirm they do not want to add data.

OR - if they do close frmChildOne without entering data an undo happens on frmParent - with a new message box that says "Are you sure?"

To tell if they added a child record, when control returns to the calling form. Do a dcount of the child table where Parent_FK = Parent_PK. If the count is 0 they did not enter a child record.

 

[MajP]

frmChildOne without entering data, the new record in tblParent is deleted?

I do not really understand this statement. There are few database designs where the Parent record does not exist if there is no child record. Not sure I understand why you would delete the Parent if a child was not created.

 

[Zydeceltico]

I do not really understand this statement. There are few database designs where the Parent record does not exist if there is no child record. Not sure I understand why you would delete the Parent if a child was not created.

Because in the real db - the "Parent" just stores general info in one or two fields related back to a tblJob. "Child" data is all inspections. If no "Child" data is recorded it is a meaningless entry in the "Parent."

 

[MajP]

Because in the real db - the "Parent" just stores general info in one or two fields related back to a tblJob. "Child" data is all inspections. If no "Child" data is recorded it is a meaningless entry in the "Parent."

I guess I would have to see this to see if the user interface made sense to me. I could see where creating a record creates a parent record that other child records would get later related to. It just seems like the UI might need to get split up.

However, Like I said you can check if the child was created or not and delete the parent if not. You may or may not be able to undo based on what was saved prior to opening the child so you may have to delete.

 

[Zydeceltico]

I could see where creating a record creates a parent record that other child records would get later related to.

That's why I added tblChores.

tblChores closely mimics tblJobs in the real db. Eventually, I will query things like: "How many instances of "mowing" (from tblChores) were done by "Child1" and how much "FeetDragging" was done by "Child1" while mowing?

However, Like I said you can check if the child was created or not and delete the parent if not. You may or may not be able to undo based on what was saved prior to opening the child so you may have to delete.

Deleting parent record if no child record was created is preferable.

 

[Zydeceltico]

I do not really understand this statement. There are few database designs where the Parent record does not exist if there is no child record. Not sure I understand why you would delete the Parent if a child was not created.

Maybe it would help to know that tblParent in this DB is actually a junction table in the real db. In the real DB, I have tblJobs (Chores), tblInspectionEvents (Parent), and mutiple unique inspection tables (Child tables).

tblInspection (the junction table) only has a few fields: InspectionEvent_PK, Jobs_FK, and DateTime. The actual inspection tables include an InspectionEvent_FK.

Jobs can have mutliple types of inspections and multiple instances of any given type of inspection.

 

[bdra2778]

What do I do? Remove this line: MsgBox Me.OpenArgs?

Yes, do it, this line of code is a way (trick) to get a debugging the code and prove the data is catched well. Or simply put as a comment, example:

'MsgBox Me.OpenArgs

 

[MajP]

That is not really a junction table, it is simply a bridge. A junction table allows you to do a many to many, you need two FKs in a junction. That is just another level. A job can have many inspection events (but an specific inspection event can only be related to a single job). Many inspections can be related to the inspection_event, but a specific instance of an inspection cannot relate to more than one inspection event. So there is no many to many, but simply two, one to manys. Job can have many inspection events, and an inspection event can have many inspections. In truth it is multiple one to many since it is really a Job can have many inspection events, and an inspection event is related to many child tables holding inspections.

There may be a reason why you do not create or able to create the inspection event until you completed the first inspection. I have done this with some hierarchical data where you are trying to group things. You cannot create the parent group to put things in without first seeing the things in that could make up the group.

 

[Zydeceltico]

Job can have many inspection events, and an inspection event can have many inspections. In truth it is multiple one to many since it is really a Job can have many inspection events, and an inspection event is related to many child tables holding inspections.

That is exactly accurate and perfectly mirrors what happens in our plant.

 

[Zydeceltico]

The Relationship Tool is my porn, I can't pass up a thread that posts a database without checking it out.
Here's the big issues

1. Circular paths among tables. There should only be one way to trace a path between tables in the Relationship tool. I can trace 7 paths between tblFinalProducts and tblInspectGeneral without going through the same table twice. That's wrong, you have too many relationships.

PLOG! You always deliver the brutal truth!!! LOL

OK - so check out what I've subtracted from these Relationships. I know that - with a lot of work and a LOT OF QUESTIONS - I can make this Relationship table work. I found a lot of intentional redundancy just like you did. It's just been that I know what I want to ask later and I am thoroughly not skilled with Access or VBA so direct link relationships made more sense to me - because I am 55 with 2 kids and no time and lazy. (and full of excuses)

But - you are right -the most elegant means of accomplishing what I am after actually means subtracting rather than adding.

2. Storing values in names. You do this in a lot of places so I will use tblWelderSettings9 as the example. The field name [P2Weld3Amperage] contains data that should be stored in fields, not in the name. You have a lot of fields in this format:

LetterNumber + Activity + Number + Metric
P2 + Weld + 3 + Amperage

All of those pieces of data should be stored as values in a table, not in the field name. Whatever the LetterNumber portion represents should be the field name and "P2" should be the value stored in it. [Activity] should be a field name and "Weld" should be the value stored in it. Whatever the number represents should be a field name and 3 should be stored in it. [Metric] should be a field name and "Amperage" should be stored in it. Then you set up a new field to hold whatever value is in P2Weld3Amperage now.

Instead of all those fields in tblWelderSettings9 that conform to that naming convention, you should accomodate that data with rows in another table. Tables should accomodate data vertically (with more rows) not horizontally (with more columns).

Again, didn't read what this issue was, just know it relates to forms. In my experience, I bet an underlying issue was your tables being improperly structured. Also know, even if it isn't exactly related to this issue, you will have issues in the future if you proceed with poorly structured tables.

I just re-read your advice. I don't entirely comprehend your rebuild but I'll give it a stab....and then ask more questions.

I am always grateful for your excruciatingly, direct delivery.

I really hope you read this. You are helping.

Tim

 

[Zydeceltico]

Private Sub cmdOpenChildOne_Click()
  Me.Dirty = False
  If Not IsNull(Me.Parent_PK) Then
    DoCmd.OpenForm "frmChildOne", , , , , acDialog, Me.Parent_PK
    Me.Requery
  Else
    MsgBox "Chore not selected. Parent Record Not Saved."
  End If
End Sub

What does "Me.Dirty = False" mean in the above code? I know the "Me" is referring to the current active form but what does ".Dirty = False" mean?

 

[plog]

No, that's not it. The relationship tool is a representation of your database and its relationships. You made that representation conform to what I suggested (by removing the relationships there), but you didn't actually change your tables to actually fix the issues.

Plant_FK is still in multiple tables.
FinalProduct_ID is still in multiple tables.
Im sure there's others I missed that you just removed the line for the relationship but left the fields.

Also, your tables are still storing values in field names. For this let's use an example not related to your database:

tblSalesWrong
SalesID, SalesPerson, InsideSales2017, OutsideSales2017, InsideSales2018, OutsideSales2018
1, Dave, 10, 8, 12, 10
2, Sally, 4, 7, 7, 8


That table is storing 2 pieces of data in its field names that should go into tables--the type of sale (Inside/Outside) and the Year. The structure of the sales table should be like so:

tblSalesRight
SalesID, SalesPerson, SalesType, SalesYear, SalesQuantity
1, Dave, Inside, 2017, 10
2, Dave, Outside, 2017, 8
3, Dave, Inside, 2018, 12
4, Dave, Outside, 2018, 10
5, Salley, Inside, 2017, 4
...



Instead of 2 rows of 6 columns, the data is accomodated by 8 rows of 5 columns. Then when 2019 rolls around you simply add 2 more rows per salesperson instead of reconfiguring your table with more columns.

 

[bdra2778]

What does "Me.Dirty = False" mean in the above code? I know the "Me" is referring to the current active form but what does ".Dirty = False" mean?

"Me.Dirty = False" Thats means not changes in data on current records. See this link.

 

[MajP]

Based on previous discussion about deleting or undoing an Inspection Event that does not have any child inspections, you are likely to need a query

qryEventsWithInspections

SELECT tblinspectionevent.inspectionevent_pk 
FROM   ((((tblinspectionevent 
           LEFT JOIN tblinspectweldtests 
                  ON tblinspectionevent.inspectionevent_pk = 
                     tblinspectweldtests.inspectionevent_fk) 
          LEFT JOIN tblinspectfabrication 
                 ON tblinspectionevent.inspectionevent_pk = 
                    tblinspectfabrication.inspectionevent_fk) 
         LEFT JOIN tblinspectweldassemble 
                ON tblinspectionevent.inspectionevent_pk = 
                   tblinspectweldassemble.inspectionevent_fk) 
        LEFT JOIN tblinspectmill 
               ON tblinspectionevent.inspectionevent_pk = 
                  tblinspectmill.inspectionevent_fk) 
       LEFT JOIN tblinspectgeneral 
              ON tblinspectionevent.inspectionevent_pk = 
                 tblinspectgeneral.inspectionevent_fk 
WHERE  (( NOT ( tblinspectfabrication.inspectionevent_fk ) IS NULL )) 
        OR (( NOT ( tblinspectweldtests.inspectionevent_fk ) IS NULL )) 
        OR (( NOT ( tblinspectgeneral.inspectionevent_fk ) IS NULL )) 
        OR (( NOT ( tblinspectweldassemble.inspectionevent_fk ) IS NULL )) 
        OR (( NOT ( tblinspectmill.inspectionevent_fk ) IS NULL ));

Then you can wrap this in a function

Public Function EventHasInspections(EventID As Long) As Boolean
  EventHasInspections = (DCount("*", "qryEventsWithInspections", "InspectionEvent_PK = " & EventID) > 0)
End Function

In code
eventHasInspections(13) returns true
eventHasInspections(14) returns false

 

[Zydeceltico]

Also, your tables are still storing values in field names.

I think I'm beginning to understand. To my eye - and if I really am understanding you - the most obvious area of my db that would benefit from this rework is the WeldTest "tree and branches."

I'm working on reworking all of those tables - actually creating completely new ones.

But just so I've asked - It very much looks like the tables will get much narrower and be more robust - BUT it's going to be about double the tables. Of course in the end I suspect there will be 60% fewer total fields even though there are more tables.

In your experience is that what I should expect?

I don't mean to change the topic of this post at all - in fact - I'm beginning to suspect that the "tables design" question that plog has brought up will have a very positive effect on my original question of "passing PKs." I think it will make it much easier - if I am understanding what I think I'm beginning to see. It just means more (but smaller) tables.

 

[plog]

Yes, you should have considerably less fields. No, I think you shouldn't have twice as many tables, my guess is you will have 3 more tables. Most likely some of the tables you have now will simply be formatted correctly instead of requiring more tables to accomplish what I detailed.

 

[Zydeceltico]

Yes, you should have considerably less fields. No, I think you shouldn't have twice as many tables, my guess is you will have 3 more tables. Most likely some of the tables you have now will simply be formatted correctly instead of requiring more tables to accomplish what I detailed.

Number of tables - maybe so.

I've found it simple to rework the bigger tables. I'll post later or tomorrow morning - but I am really having a challenge wrapping my head around a "bigger picture" concept.

Thinking about weld tests - - -

Actually I've gone ahead and attached where I am currently - which feels *almost* right - - but I know it isn't.

We have two different types of welders, DC or AC. Each welder has multiple guns - as many as 5. The two different types of welders have different types of settings and I need to collect the settings for any given weld test. I can't figure out how or where to make the split to the different tables - -- OR maybe I am struggling making a similar cognitive leap like the one that you'll notice I was able to make with a lot of other fields and tables.

Later on I know I am also going to need to address a similar condition when branching from tblInspectionEvent to the various types of inspections. I am missing some "logic" and knowledge of what "mechanics " are available or what is even good design practice.

In the attached DB, just ignore the hidden tables for the most part. They're just there to give me a visual.

 

[Zydeceltico]

Based on previous discussion about deleting or undoing an Inspection Event that does not have any child inspections, you are likely to need a query

MajP! Holy Mackerel!

Thank you for that. That's mighty generous and super-appreciated! I'll let you know when I am able to plug that in.

Super grateful.

Tim

 

[Zydeceltico]

Yes, you should have considerably less fields. No, I think you shouldn't have twice as many tables, my guess is you will have 3 more tables. Most likely some of the tables you have now will simply be formatted correctly instead of requiring more tables to accomplish what I detailed.

Attached is my current incarnation.

I have several question/thoughts but the most pressing is regarding tblAcWelderMeasures, tblDCWelderMeasures, and tblWeldNuggets and how they relate to tblWeldGUns and the entire WeldTest tree overall.

Something seems "not right" and yes now I am thinking about I am going to have to utilize this table and relationship design in the real world application.

Thoughts?

Thanks!

Tim

 

[plog]

I know nothing of your business, nor welding. So you are going to have to explain to me what real world process those tables are capturing. The simpler the terms the better. I know no welding jargon and you shouldn't use database jargon in explaining the real world process.

With that said, I would try to combine tblDCWelderMeasure and tblACWelderMeasures. They already share a lot of the same fields, and with a little finessing they could share more.