Pass Mainform ID value to Subform FK field

[Zydeceltico]

Hi All -

I have 2 tables: tblInspectionReport and tblMillInspect. They are linked by fields InspectionEvent_ID and InspectionEvent_FK respectively.

Report rptInspectionReport and qryInspectionEvent work exactly as expected and return exactly what I am looking for.

I have frmInspectionEvent based on the two tables - which again - are linked by fields InspectionEvent_ID and InspectionEvent_FK respectively. Subform sfrmMillInspect is embedded in frmInspectionEvent.

InspectionEvent_ID in tblInspectionEvent is autonumber.

What I don't know how to handle is the relationship between InspectionEvent_ID and InspectionEvent_FK from the main form to the subform.

When I open frmInspectionEvent and fill in the data in the main section of the form - after I have entered a value in the last available field the field bound to InspectionEvent_ID autopopulates in the main form but the corresponding field (InspectionEvent_FK) in the subform does not - which throws me an error message.

I can get around this if I manually enter the autonumbered value from InspectionEvent_ID in the main form into InspectionEvent_FK in the subform. But it seems inelegant. I'm sure there is a better way.

All fields are saved in both tables when I click the "Save Record" button in the main form - fwiw.

Thanks for the help!

Tim

 

[plog]

I have frmInspectionEvent based on the two tables - which again - are linked by fields InspectionEvent_ID and InspectionEvent_FK respectively.

That's not what I am seeing.

1st and leastmost--frmInspectionEvent is based on 1 table--tblInspectionEvent.

2nd and to your issue--your subform is incorrectly linked to your main form.

Link Master Fields: InspectionEvent_ID
Link Child Fields: Millinspect_ID

The child field should be the foreign key in tblMillinspect (InsepectionEvent_FK)

 

[Zydeceltico]

That's not what I am seeing.

1st and leastmost--frmInspectionEvent is based on 1 table--tblInspectionEvent.

2nd and to your issue--your subform is incorrectly linked to your main form.

Link Master Fields: InspectionEvent_ID
Link Child Fields: Millinspect_ID

The child field should be the foreign key in tblMillinspect (InsepectionEvent_FK)

Thanks Plog. I really thought I had the child/master links linked the way you mentioned. I just changed it and it works the way I had hoped. Thank You.

 

[Zydeceltico]

So now that I have that correct - - -


Is there a way I can "postpone" the InspectionEvent_FK from being populated from the main form until I actual tab or click in a field in the subform?

 

[June7]

Not with linked forms. Should just hide the ID controls. Or not even create them on the form. Users do not need to see these autonumber keys.

 

[Zydeceltico]

Not with linked forms. Should just hide the ID controls. Or not even create them on the form. Users do not need to see these autonumber keys.

Hi June -

I only left them on these form/subforms for my own reference. They won't be there later.

I asked this particular question for a different reason.

Thanks!

 

[Zydeceltico]

So now that I have that correct - - -


Is there a way I can "postpone" the InspectionEvent_FK from being populated from the main form until I actual tab or click in a field in the subform?

Please see the attached DB. The reason I wanted to "postpone" the InspectionEvent_FK from populating is because I plan on having several tabs that each are home to a different type of inspection and I am thinking it would be a good safeguard from the user accidently beginning an inspection record on the incorrect tab. It likely won't be an issue for a variety of reasons - but I was just wondering if someone sees a "more sure" way of doing this.

By the way - I've checked the results of my union query in rptOilCanningAll and it is exactly what I have been hoping for! Thanks everyone.

Tim

 

[plog]

When you have 2 tables with the exact same structure, you are doing it wrong. When you do that you are storing data in the table's name. Instead that data should go into a field of the table.

In the database you posted you have 2 types of inspections (weld and mill). You are differentiating the types via table name. Instead you need to do it via a field ([InspectionType]). So, throw away one of those tables, rename the one you keep to tblInspect, add an [InspectionType] field to it and allow the values "Mill" and "Weld" for it. Then you have 1 table that can store all your inspection data no matter the type.

Formwise, you just add that control to the form and let the user choose the type of inspection via that instead of hunting for the correct tab and making hard to correct input errors.

 

[Zydeceltico]

When you have 2 tables with the exact same structure, you are doing it wrong. When you do that you are storing data in the table's name. Instead that data should go into a field of the table.

Then you have 1 table that can store all your inspection data no matter the type.

Hi plog -

The only thing is that a weld inspection and a mill inspection have vastly different fields. They are not the "exact same structure." They are in the example db I attached but that is simply for clarity while asking assistance. You've seen my former design with hundreds of fields. A very small number of fields are identical. The db I've presented in this thread is the very early beginning of me taking your former advice to redesign.

However, if I make one table for all inspections it will be enormous and 70% of the fields will be empty for all of the records depending on the type of inspection being undertaken. You obviously have far, far more experience than I do but I can't help but wonder if an "all-inclusive" table is good design.

Also - in the former design you've looked at the majority of all those tables are lookup tables. I've seen that some would aggregate all of those disparate lookup tables into a single huge lookup table but I find that cumbersome and I don't see how that helps with data integrity.

Do you really think I should put all of the inspection "types" into one inspection table even though they share only a few fields that are the same?

As always - Thank You!

Tim

 

[plog]

Did you ever land on a final table structure? Can you post that database?

 

[Zydeceltico]

Did you ever land on a final table structure? Can you post that database?

I'm still working on it - - taking my time and making sure I really pay attention to normalization wherever and whenever appropriate.

But I can tell you that per your former advice it is headed in a much better direction. I can see it. I'm checking every modification by running reports as you suggested because I do know what I want to be able to draw from the db when it is done.

I'll probably make a lot of progress this weekend and post an initial draft sometime next week.

By the way - is there a naming convention for lookup tables?

Thanks again,

Tim

 

[plog]

I'm against lookup tables. Tables with only 1 real field (autonumbers don't count) shouldn't exist. Instead, in the Table Design view you should click on the Lookup tab of the field, change Display Control to Combo Box, Row Source Type to t you should Value List and then input the valid values on the Row Source line.

 

[Zydeceltico]

I'm against lookup tables. Tables with only 1 real field (autonumbers don't count) shouldn't exist. Instead, in the Table Design view you should click on the Lookup tab of the field, change Display Control to Combo Box, Row Source Type to t you should Value List and then input the valid values on the Row Source line.

I've done that before but was told that would be a lookup field which I thought are frowned on.

I know it is much more straight ahead and less cluttered for me to do Value Lists the way you're describing. Especially since many of my lookup tables don't have more than a few records.

Does that in any way impede anything later? Like queries? I don't see why it would but I just got a lot of flack about using lookup tables instead of lookups in the table itself.

What do you think?

 

[isladogs]

I agree with the consensus that lookup fields at table level are very bad idea.
This webpage explains the issues very well. http://access.mvps.org/access/lookupfields.htm

I do agree that a table with an autonumber and one other field rarely has any real value.

 

[Zydeceltico]

I do agree that a table with an autonumber and one other field rarely has any real value.

One of the instances where this is the case has to do with our material suppliers and one of the main goals of this database project.

We only buy material from five suppliers.

We strongly suspect that an ongoing process defect is the result of an inherent defect in the material before we process it and that our processing of the material exacerbates an already intrinsic defect in the material itself.

So one of the goals of the db is to look at the defect (oil canning) across all suppliers over time and product profiles. And - because we strongly suspect an issue with a supplier, we are constantly looking for more suppliers which means my list of suppliers is going to change.

This is one example of why I have been using so many Lookup tables.

Same thing with tblOperators. These are the guys that run the mills and welders. That list is also "in flux" over time.

Etc. etc.

This db does not interface with the company's main ERP system and it shouldn't which means that my tblSuppliers and tblOperators are basically single-field tables that I need to add or subtract to somewhat frequently.

Thoughts?

 

[isladogs]

It is rarely a good idea to delete records. At some point you may decide to resume using an ex-supplier again.
In the case of your suppliers table, I would at the very least add a Boolean field Active.
Personally, I would also add Supplier Address, phone, Email, contact name etc

 

[Deleted member 147267]

I do agree that a table with an autonumber and one other field rarely has any real value.

A table with only one field is how you control option selections for combo boxes for things like "inspection type" "locations" or any similar value that you want to prevent users from adding mis-spelled variations of the same thing. Not sure what else you'd do - a table with a field for each type of lookup value? I wouldn't. I realize that some lookup tables would have multiple fields, not counting autonumber id's, such as State (abbreviation, full spelling) but surely this isn't about the number of fields in a lookup table? I wouldn't hesitate to put any number of single field lookup tables in a db if I thought control over user input was an important factor. What would be the issue?

However, I'm on the same page as you with respect to lookup fields in tables - not a good practice.

 

[isladogs]

I don't think we are disagreeing though if the lookup table values are only needed for one form combo, I might well use a value list for the form combo in that situation.

There are of course occasions when tables with an id and one other field are perfectly valid. However I was thinking about cases of 'over normalisation' with multiple tables of one or two fields only. In some cases, these may be linked with a 1:1 join

 

[Deleted member 147267]

I might well use a value list

My bent would be to add a table record, not open the form to design view to add more list elements. I guess it's personal preference, so which is best for each of us is the bestest!

 

[isladogs]

My bent would be to add a table record, not open the form to design view to add more list elements. I guess it's personal preference, so which is best for each of us is the bestest!

My preference isn't cast in stone.
It really would depend on the circumstances as far as I'm concerned.

If the item list is only going to be used in one place and is NEVER going to change, then using a value list in the form combo would be fine.
However, if the list is likely to be edited at any time and/or used in more than one place, I would definitely use a lookup table.

However, as already stated, I NEVER use lookup fields at table level.

As far as I'm concerned they belong in Room 101 along with multi-value, attachment and calculated fields