Add records to second of two 1-to-1 tables (1 Viewer)

Kronix

Registered User.
Local time
Today, 01:41
Joined
Nov 2, 2017
Messages
102
I have three product category tables -- Machines, Services, and Parts -- and a ProductIndex table that keeps track of items in all three tables. The ProductIndex table contains all of the products, with fields for the ProductIndex primary key, the type of product (i.e. which table it is stored in), and the foreign key corresponding to the primary key of that product in its table category (it is possible this foreign key can occur up to three times, once for a product in each table). Each category table also contains a foreign key pointing to the primary key in the ProductIndex table.

When I add an item (record) to any of the three category tables, I want a corresponding new record in the ProductIndex table. But to fill in the foreign key for one table, I need to know what its primary key will be in the other table. So, when adding a new item to the Parts table, how do I find what that item's primary key in the ProductIndex table will be? I will also need to know its primary key in the Parts table to add to the ProductIndex table.

I've tried using a subform for the ProductIndex within the subform for the Category table entry, but I can't get each of their foreign keys to automatically appear since I have to type something and tab away to get a primary key to pop up. Is there a function that can cause the new primary key to appear? SetFocus doesn't seem to work.
 

Minty

AWF VIP
Local time
Today, 00:41
Joined
Jul 26, 2013
Messages
10,355
This sounds as if you don't have the relationships / tables set up correctly, but I'm having a hard time visualising it, probably because it's Monday morning.

Could you post up either a sample database(you'll have to zip it) or possibly a picture of the tables in the relationship window?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:41
Joined
Feb 19, 2002
Messages
42,981
The Product table is the master table. It should include all the common fields as well as a code that identifies product type so you know which of the other three tables contains the non common details. This tables DOES NOT have a FK pointing to the other table.

In the three specific tables, their PK is a long integer and is in effect the FK back to the product table. Do NOT use an autonumber for the PK of the child table in a 1-1 relationship.

In the AfterInsert event of the Product table, you can append a record to the specific related table based on Product Type. The only column with data will be the PK which is also the FK to the Product table.
 

Kronix

Registered User.
Local time
Today, 01:41
Joined
Nov 2, 2017
Messages
102
I don't think I'll be uploading my database here, it's in a different language anyway. What I do post will be translated.

Thanks Pat, I didn't think to not use an Autonumber for the child tables. That actually makes a lot of sense and reduces the need for a foreign key that points the other way as you said.

I'm wondering, however, how I should I append the info to the child table? I've used SQL code run from Visual Basic before like the following. This is in the Form_BeforeUpdate of the MachineToOrder form for that child table. It's called MachineToOrder because it's a many to many table linking default Machine values with the Orders Table (which contains customer info). The purpose of the IsNull DLookup check is to check if the child table item already has an entry in the ProductIndex. This is because I don't know whether the record being updated in the MachineToOrder form is a new record or a modification of an existing record. If it exists already, the ProductIndex table and key does not need to be touched. Please remember ProductIndex is the parent table containing all entries that have been placed in orders, not all products available:

Oh and one thing just occurred to me. You didn't address the fact that I need to know which of the three tables the ProductIndex key is pointing to. This is why there is a ProductType field being inserted into the ProductIndex table. "MS" tells it to look in the machine table. The forms for the Services and Parts child tables would each use similar code with other abbreviations. I use a Select Case command when working with the ProductIndex table to pull up the details from the appropriate child table.

This code, however, does still have the foreign key (ProductTableID) from the ProductIndex table in it, since I haven't implemented the non-Autonumber key for the children yet.

Oh, and I'm keeping all the details -- including common ones -- in the child tables and using the ProductIndex for just that -- an index. Makes sense to simplify the data entry process, right?

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim SQL As String

    If IsNull(DLookup("ProductIndexID", "tblProductIndex", "ProductTableID = " & MachineToOrderID & " _
    AND ProductType = 'MS'")) Then
        
        SQL = "INSERT INTO tblProductIndex (ProductTableID, ProductType) VALUES (" & MachineToOrderID & "," _
        & "'MS')"

        DoCmd.SetWarnings False
        DoCmd.RunSQL SQL
        DoCmd.SetWarnings True
        
        ProduktIndexID = DLookup("ProductIndexID", "tblProductIndex", "ProductTableID = " & MachineToOrderID _
        & " AND ProductType = 'MS'")

    End If
End Sub
 
Last edited:

Kronix

Registered User.
Local time
Today, 01:41
Joined
Nov 2, 2017
Messages
102
Mods, I need my latest reply to be approved please. edit: ok there it is.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:41
Joined
Feb 19, 2002
Messages
42,981
You didn't address the fact that I need to know which of the three tables the ProductIndex key is pointing to.
I did. Please reread my post. I am now confused regarding your question. You were asking how to handle the 1-1. I explained that and told you what form event to use to insert the -1 side record.

Stop everything until you fix the 1-1 relationship correctly.

The code you posted makes no sense. You do not insert the child of the 1-1 first. You insert the parent.
 

Kronix

Registered User.
Local time
Today, 01:41
Joined
Nov 2, 2017
Messages
102
Hmm, you're right, you did say to include a code to identify the product type. I think on first read I thought you were referring to programming code. So you agree with the "MS" code I used?

You're saying to update the child after the parent, but the information is being entered into a form based on the child. How do I make the form update both tables? Do I need to disable the built-in record navigation mechanic (the arrows at the bottom in datasheet and form view) and do the inserts for both tables from scratch in VBA using a manual button control?

If I do the AfterInsert event on the parent table as you said, that would mean that they were navigating a form based on the ProductIndex table in the first place, but that's not feasable because (1) the ProductIndex records do not contain the information interesting to the user, and (2) the user will be navigating based on the grouping of the child tables, not all of the items at once.
 
Last edited:

Minty

AWF VIP
Local time
Today, 00:41
Joined
Jul 26, 2013
Messages
10,355
As Pat has said, the relationships don't sit correctly. Until that is sorted out you'll be attending kludge city reunion and be the main attraction ;)

Just because you are wanting to enter child information, you should already have the parent identified and selected at that point. Even if your have a parent / child subform arrangement to take care of the data entry , it doesn't have to "look" like its presented that way, if that's not what you want the users to see.

That's why I want to see your table layout, not worried about the data, although a brief translation would probably help.
 

Kronix

Registered User.
Local time
Today, 01:41
Joined
Nov 2, 2017
Messages
102
Well I just successfully changed the table key setup as advised. So the parent table now is the only Autonumber Key and the primary keys of the 3 child tables are non-autonumber long integers with a 1-to-1 relationship with the parent table.

To my amazement, I was able to put fields from both the parent and child tables into the same subform -- no need for embedded subforms -- and when adding a record it automatically updates BOTH tables. And it only shows the items for the current order because I am linking the order ID from the main form. And it again only shows the items for the current item category child table that's selected as the data source alongside the parent table for the current form tab (i have a tab for each of the 3 child table categories, each with the parent table and appropriate child table as record source). I am impressed that Access is so intelligent (even though it corrupted my database yesterday).

I have noticed that it won't let me add the record if the keys for the child and parent tables are different -- i added both fields as textcontrols to the subform just for debugging purposes. I thought that it would perhaps automatically update the non-autonumber child key since it has a 1to1 relationship with the parent table autonumber field that is automatically assigned (it is the only one that shows "(new)" when adding a fresh record), but alas it looks like i will have to force disable editing of the child's key textcontrol.

Let me know if this sounds right, I have some more experimenting to do in the meantime.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:41
Joined
Feb 19, 2002
Messages
42,981
To my amazement, I was able to put fields from both the parent and child tables into the same subform
Access is amazing when you get it right.
To my amazement, I was able to put fields from both the parent and child tables into the same subform
Now you're off the rails again. You keep getting your relationships backwards. The child points to the parent. You have the parent pointing to the child. One parent, many children. How can ONE parent point to MANY children if you put the child's ID in the parent record? The answer is - you can't. The parent's ID goes into the child record.
 

Users who are viewing this thread

Top Bottom