Question regarding sub forms

baadams

Board Regular
Joined
Mar 2, 2006
Messages
130
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!
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Jun 3, 2015
Messages
1,882
Office Version
365
Platform
Windows
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.
 

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
137
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?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,239
Messages
5,509,994
Members
408,768
Latest member
ndg4405

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top