Junction Table impact on Forms/Subforms

[sparrow76]

I am new to Junction tables, but am trying to work out the creation of a new database and I want good structure. Currently I have 3 tables:

tbl Clients
pk ClientID (auto #)
fk ClientCompID

tbl Complainants
pk CompID (auto #)
fk ClientCompID

tbl ClientComp
pk ClientCompID (auto #)
fk ClientID
fk CompID

I want my main form (details about the Client) to open a form/subform that shows the name of the Client and then allows for entry of Complainant information.

When I used the client's name (and had this field in both Clients and Complainants), I could pull up my subform, but then two clients by the same name were updated with the same info from the subform (even though different client IDs). So I'm trying to use the Junction table to avoid this, but now my code must be wrong, because it just says the action is cancelled. I'm not sure if my code is wrong or my tables are wrong, but I really want to understand this so I can have a good stable structure to build from!

Code:

Private Sub Complainant_Click()
On Error GoTo Err_Complainants_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Complainant"

stLinkCriteria = "[ClientCompID]=" & "'" & Me![ClientCompID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Complainants_Click:
Exit Sub
Err_Complainants_Click:
MsgBox Err.Description
Resume Exit_Complainants_Click

End Sub


Any ideas would be appreciated! Thank you!

 

[sparrow76]

I think I fixed my code... Does anyone see anything in either my earlier post of table structure or this code that might spell future problems? Thank you!!

Private Sub Complainant_Click()
On Error GoTo Err_Complainants_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Complainant"

stLinkCriteria = "[ClientID]=" & Me![ClientID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Complainants_Click:
Exit Sub
Err_Complainants_Click:
MsgBox Err.Description
Resume Exit_Complainants_Click

End Sub

 

[The_Doc_Man]

OK, when talking about form/subform cases, we are usually discussing TWO recordsets, two tables - and a relationship exists between them such that they COULD be joined, but the main form is driven by the parent table and the sub-form is driven (technically separately) by the child form. Even though the table has a parent/child linkage ability. Where you talk about JUNCTION tables, typically you form a single recordset based on the relationship between the two tables. ONE recordset, not two.

So perhaps your "action is canceled" was due to conflicts between parent and child tables joined through a junction table rather than letting the form sort things out? I can't make any other guess here without knowing more.