Problems updating tables in one to one relationship?

[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!

 

[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!