Question regarding sub forms

[baadams]

Quickly I'll describe my system to provide some insight. We use Access primarily for reporting purposes. Daily the tables are refreshed from our current shop floor control system. This system has a couple shortcomings we've resolved using Access, primarily it won't allow us to provide ECD (estimated complete dates) for jobs or tags. This system is list of open jobs with their constraints. Constraints consist of parts and tags. Our part data is managed by another group via SAP. The tags are maintained within the shop floor control system. The issue is we'd like to start providing and tracking ECD's for tags. The current system has no GUI to input this data so we plan to store it within Access.

Each job has multiple (or 0) tags and part shortages. Currently the main form has a tag sub form showing each tag associated with the job. I now want a sub form to the tag sub form to allow the user to input a ECD for each tag. When I create the sub form to the tag sub form I link the Tag Number fields but when the ECD is updated the Tag Number value is blank in the new tag_notes table. Normally the value in the linked field is populated within the sub form, but it's not being updated in this case. The ECD and notes are stored within the table but there is no tag number so when you change records and go back the link to the parent form is gone.

The tag notes table (tbl_TAG_NOTES) has 4 fields. ID, Tag Number, TAG ECD and TAG NOTES. This is all we want store, the additional tag data resides in another table that is refreshed daily. I don't know why linking the two doesn't work, would it be easier to just write VBA to update the tbl_TAG_NOTES? My tag subform works fine, not sure why the sub-sub form doesn't? Or would you suggest I go a different route all together?

I hope this explains it well enough. Thanks!

 

[xenou]

I think you have to make sure your sub-sub form has a proper record source and is linked on all the primary key and foreign key fields to it's parent form. Also it should not be a ready only (snapshot) type of recordset or recordsource. Its very difficult to describe these things - my experience is that after fiddling around and trying different things you can probably get something like this to work though. On the other hand it may be quite a few years since I tried a sub sub form that was editable so I can specifically recall what the pitfalls are. Is it possible to input the ECD on the tag subform and go without a sub-sub form? This may be along the lines of your last idea anyway...

 

[Micron]

Sub subforms aren't my thing either. Would you not have a master/child link between the sub and its subform, and then a link between the main and its sub? I can see where the sub sub records will have a relationship between fields on it and its parent, but would not expect that there will be a link between the sub sub and the main. If there was, you'd have data redundancy where a field would be in both the main and the sub sub (in their respective tables). IIRC, you can't upload a db in this forum, which is too bad as it would give us something to work with. The whole thing is complicated by the need to enforce that related fields have data, which can be problematic when adding records. Users often attempt to navigate to a subform, which usually attempts to update the parent form and often raises errors when a record save is attempted when a related field has no data. Might be easier to split this into 2 main/sub form setups and relate all 3 in a report if it's not necessary to see all 3 in a form view.

 

[jackd]

I haven't used subsubforms in a long time. Here is a youtube video by Dr. Verschurren that may help. He has several good (although a little older) video tutorials.

 

[rondeondo]

when it comes to sub sub forms I'd always recommend building from the inside. Build the sub-sub form dataset then add it to the sub form, that way you can test each piece in isolation. For the problem you describe it sounds like the foreign key in the sub-sub form is not picking up the tag ID from the sub form, its main form, if you've been successful in having the tag sub form work with the main form, duplicate the logic between sub and sub-sub
A design question, is there a possibility for any tag to have multiple ECDs? if not is adding ECD to the tag table an option?