Automatically filling linking fields for new rows in a related table?

[theSeekerr]

OK, so I have a database with four tables (Well, more than that, but these are the relevant ones). It's to be used for recording the results of site inspections.




"Tbl_Typicals" is a list of products. We'll call its key "Typical_ID".
"Tbl_Actions" is a list of tests performed on each product. A given product may have many tests, but each test applies to only one product. Its key is "Action_ID". Each row contains a Typical_ID to link on.
"Tbl_PlantComponents" records which products are installed on which site. Its key is "Component_ID". Each row contains a Typical_ID to link on.


The fourth table ("Tbl_Results") contains the results of each test. As a result of the relationships above, each row is specific to a single Action which applies to a particular PlantComponent, which is to say, each row has both a Component_ID and an Action_ID to link on.


So, what I need is a query that pulls all of these together, such that I can use these details as the line items of a subform.


The main form displays the details of the PlantComponent, which is a simple query to relate line items in Tbl_PlantComponents with the data about that particular product in Tbl_Typicals. So far, so easy.


The subform shows the details of each test applicable to that product. It then has toggle buttons and a comment field to indicate the results of the test, the results of which should be stored as a line item in Tbl_Results.


This all seems simple enough, but implementation is giving me a headache.


The "easy" way is to use an append query to generate Tbl_Results in advance. This works, but it raises a variety of new issues.


The nice way would be to use a normal SELECT query and have Access fill in the necessary linking fields (the Action_ID and Component_ID) on each row automatically. Now for trivial examples, this is very easy - my main form query manages just that: I created a link between Tbl_PlantComponents and Tbl_Comments (which stores general comments about each PlantComponent which aren't related to a specific test) based on the Component_ID and that works fine - when I edit the Comments field, the row is automatically created and the linked ID field filled in for me.


However, when I need to do it with 2 links, it all falls apart. I've tried everything I can think of, including generating a single-column unique ID to use for the link, but Access just won't autofill for me. It just makes those fields on the form (or in the datasheet view of the query) non-editable because there's no associated row in Tbl_Results. If I create a matching row in Tbl_Results the query works fine, but that's not the point.

Implementation of the query is non-trivial because it requires two outer joins involving 3 tables - All from Tbl_Actions to matching in Tbl_Results, and All from Tbl_PlantComponents to matching in Tbl_Actions. This necessiates splitting the query into two - the first relates Tbl_PlantComponents, Tbl_Typicals and Tbl_Actions (returning one row for each Action for every Component), and the second performs a single outer join (using an AND) between the first query and Tbl_Results.

Anyone got any ideas?

[Rod]

It would be much easier for us if you published a relationship diagram rather than a description. I have followed your description and, unless I have missed something or misunderstood, I end up with the following relationship diagram.



Is this correct?

[theSeekerr]

My apologies - I composed my message on the train, meant to update it with diagrams when I got to work. Your diagram is correct.

[Rod]

I keep looking at that relationship diagram and get an uncomfortable feeling - but I can't put my finger on what may be wrong. Maybe it's because I don't know enough about your requirement. Anyway ...

1. Your main form will show data about one plant component.
2. You need a subform to add/amend/delete test results for that plant component (and by implication that product).

Basically your subform lists the existing results for that plant component and allows for add/amend/delete. Additional, complementary data is retrieved from the typicals and actions tables. (Note: subform should be a continuous form rather than a datasheet.)

Now the complication arises from the fact that there may be multiple actions (tests) for each plant component - typical combination, and presumably repetitions of each of those actions over a period. This suggests to me that the column for action on the subform has to be some kind of select control, most likely a combo box. Anyway worry about that a little later; let's get the SQL up and running for the subform. Here's a sample design grid.



Now you need to design a continuous form bound to this query.

When you install the continuous form as a subform, Access will probably link on ConponentID. I suggest you add a link on TypicalID as well.



Disable (or hide) the columns for ComponentID and TypicalID on the subform.

On the subform change the control for ActionID from a text box to a combo box.

To complete the synchronisation you need to code a one line statement in the main form's Current event.

Private Sub Form_Current()
    Me.frmSub.Form.ActionID.RowSource = "SELECT ActionID FROM tblActions WHERE TypicalID = " & Me.TypicalID
End Sub

Use your own names where necessary.

Once the basic synchronisation mechanism is working we can apply some cosmetics.

[theSeekerr]

Perhaps I should make clear that I'm not a noob at this. I build databases every day. I just can't nut this one out, and I've been trying since Monday...

To clear up the requirements a bit:

For every typical (product/model - we call them "typicals", I don't know why either) there is a standard set of tests ("Actions"). The main form allows the user to navigate to the checklist for a specific "component" (that is, a particular instance of a typical, with a location/tag nb/etc) and record the result of each test - ie. the subform needs to show all the actions associated with the typical of which that component is an instance, regardless of whether there is yet a result for it. When a button is clicked to set a result for that test, the appropriate row needs to be added to the results table, complete with the ComponentID and ActionID that identify it.

So the relationship you show is back-to-front for my use case, unfortunately - what you've done is really easy. What I need to do doesn't seem to be, which is why I'm here!

[Rod]

The subform cannot show rows unless they exist in the database or in a result set of a query. Thus to show

all the actions associated with the typical of which that component is an instance, regardless of whether there is yet a result for it

you need to generate a result set (query) or a work table (not a member of the permanently related db tables). The result set or work table needs to be refreshed for each component/typical combination on the main form (suggest the main form's Current event for this). The result set or table needs to be examined every time the main form changes to a new component/typical combination (or is closed) and any add/amend/deletes posted to the permanent db tables. I suggest the On Exit event of the subform container control for this. If you want it more 'real time' then use the After Update event of the subform - but this has performance penalties.

The alternative is to generate empty records in the db each time you add/amend/delete component/typical combinations or actions - messy!

To build a work table I would use VBA. To build a result set, remember that if you do not join two tables in a query then the result set contains a row for every combination of the individual rows from each table. Thus if one side is a one-row table/result set for the component/typical combination and the other side is a filtered list of actions pertaining to that component/typical combination then the result is all possible combinations. (Without prototyping my suggestion I don't know whether it's necessary to have a 'joinless' query or whether the desired result may be achieved through nested queries.) Now you need to use that result in a left join to the actions table to retrieve records that already exist. I suggest you include an extra column for tagging each row as to whether it has been added, amended or deleted.

There is no way I know to make Access update the db automatically (unless you use my alternate 'messy' solution above). You must code the updates. I would use VBA for this. There may be a way to do it with SQL but remember that Access SQL is a classic form of SQL, principally designed for data retrieval with some rudimentary table manipulation features; it is not Transact SQL; it is not the PL1 equivalent.