Problems updating tables in one to one relationship? (1 Viewer)

[mr_fish]

I've split a table in which a number of fields weren't always relevant to enter on every occasion and linked them in a one to one relationship, the idea is to increase efficiency and help reporting. Both have the same name primary key linked with referential integrity and cascade update related field ticked.

I've used a control button on one form relevant to that table to open a form linked to the other when the user requires to add the extra info, but for some reason it doesn't automatically put in the key in the other form and therefore link the tables.

What I want to do is have the original form link to the additional info table and auto updates the key (link field) in that table if the user wishes to enter extra info.

Can anyone help, been stuck on this for a bit too long now. Thanks!

 

[Pat Hartman]

1. You actually need to specify cascade delete. Cascade update is only useful if you are not using an autonumber primary key.

2. The whole point of your 1-1 relationship is that the "child" side is sparse. That means that you don't create a record in the second table unless you actually have data to store.

3. To handle a sparse 1-1 relationship, use a left join. That will return rows from the "parent" table even when there is no matching row in the "child" table.

4. As long as the master/child links are properly set, Access will automatically add the pk of the child record when ANY field in the child table is populated in the form. That will cause rows to be added to both tables. If no field in the second table is populated, no record will be created in the child table.

 

[mr_fish]

Thanks for the reply Pat, your points 2 was my aim and I was already usinf a left join as you mentioned in point 3.

Am I right in assuming that you don't use cascade update because it will create a new record in the child table every time you enter something in the parent.

I think it is point 4 where my issues still lie, due to the layout of the form and to display more records in the master, my plan was to add a command button that opens the child form only when the user needs to enter that data. I used some vba code (see below) and I think this is where the issue is, I'm trying to update the child field in code because it is in a separate form.


Private Sub Command100_Click()
On Error GoTo Err_Command100_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim inCovID As Integer

If cmboCoverageType = "1" Then
MsgBox prompt:="You can't send a sample for News Coverage, enter a separate REVIEW field!", buttons:=vbOKOnly, Title:="Integrity ERROR"
Exit Sub

Else

inCovID = Me![txtCoverageID] ' passing the value of the master key into a variable
stLinkCriteria = "[SAMPLE_TRANSACTION_tbl_Coverage_ID]=" & Me![txtCoverageID] 'setting up a filter to open the child form only on that master key variable
stDocName = "SAMPLE_TRANSACTION"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Forms!SAMPLE_TRANSACTION![SAMPLE_TRANSACTION_tbl_Coverage_ID] = inCovID 'passing the master key variable to the child key on the form to link them

Exit_Command100_Click:
Exit Sub
End If
Err_Command100_Click:
MsgBox Err.Description
Resume Exit_Command100_Click

End Sub

 

[mr_fish]

Just realised I'm not using the traditional master/child forms that access supports, would this be the best solution, the only problem is there is no space for the child form to appear on the master form (where there are multiple entries in rows similar to a data sheet). Does the master/child have to be within the same form, or can you open a subform linked to the master form in a separate form.

That's why I'm trying to do it in code to open a child form and enter the key from the master table.

I'm probably making life far too complicated, but that's what happens when you don't know the answers

Again thanks to any who can help!

 

[Pat Hartman]

Am I right in assuming that you don't use cascade update because it will create a new record in the child table every time you enter something in the parent.

Cascade update doesn't add anything. The ONLY purpose of cascade update is to ensure that any change to the primary key in the parent table is propagated to related child records. So for example, if the value of the primary key were "xyz" and you changed it to "abc", Jet would find all the child records whose foreign key value was "xyz" and change it to "abc" so that the child records would still point to the correct parent record.

When using a popup "child" form, pass the inCovID as the OpenArgs argument. Then in the BeforeInsert event of the popup form add the following:

Me.[SAMPLE_TRANSACTION_tbl_Coverage_ID] = OpenArgs