Relationship Problem

[G deady via AccessMonster.com]

I thought about posting this in the thread on copying data from fields but
decided it was a different issue though similar. I have two forms eached
linked to a different table. The tables are linked in a one to many
relationship.(PK to FK)When I save a new record in Tbl1 and open new record
in Tbl2 I want the data in PK of Tbl1 to populate the FK field in tbl2. This
should be no problem since they are linked but I can't seem to get it. This
is my code:

Private Sub Label136_DblClick(Cancel As Integer)
Me.Refresh
DoCmd.OpenForm "frm_StateCivilCaseInfo", , , "SCVClientID = " & Me!ClientID
The code opens the new form but doesn't populate the "SCVClientID" field.
Can someone tell me what I am doing wrong?

 

[John Vinson]

I thought about posting this in the thread on copying data from fields but
decided it was a different issue though similar. I have two forms eached
linked to a different table. The tables are linked in a one to many
relationship.(PK to FK)When I save a new record in Tbl1 and open new record
in Tbl2 I want the data in PK of Tbl1 to populate the FK field in tbl2. This
should be no problem since they are linked but I can't seem to get it. This
is my code:

Private Sub Label136_DblClick(Cancel As Integer)
Me.Refresh
DoCmd.OpenForm "frm_StateCivilCaseInfo", , , "SCVClientID = " & Me!ClientID
The code opens the new form but doesn't populate the "SCVClientID" field.
Can someone tell me what I am doing wrong?

Assuming that passing a WhereCondition argument will automagically
populate another table. It won't; all that argument does is LIMIT the
records displayed on the second form to those which *already* have
that value of SCVClientID. A Relationship does not cause new records
to be created - quite the opposite, it *prevents* the user from adding
invalid records.

The *typical* way to accomplish this goal is to make the second form a
Subform of the first, with ClientID as the Master Link Field and
SCVClientID as the Child Link Field. If you need the screen real
estate you can display the Subform on a tab page. Have you chosen to
reject the Subform approach? If you really need a (user-hostile,
often, IMHO) popup form, you can do it - post back if so.

John W. Vinson[MVP]

 

[G deady via AccessMonster.com]

John, Its a serious Real Estate problem. I only used the first two firms for
my question. There are several more. Say a client comes in with new
autoaccident. The clientform fills in clientdata. then caseform general
casedata, then autaform, autoaccident details, insurance, defendants,
injuries, medical treatment, Doctors, and Medical Bills Forms-All bound to
seperate tables. Thats just the initial forms. As the case progresses there
are forms for pleadings, witnesses, expert witnesses, discovery, etc. So
there are just too many forms to use a subform format. I considered tabs but
rejected them. First, I'm not sure there aren't too many forms even for tabs.
More importantly, many of the forms are linked to more than one case form.
For example, injuries, doctors, medicaltreatment, medicalbills, pleadings and
discovery are also linked to a slip & fall case caseform, Product Liability
caseform and medical malpractice caseform. That makes it difficult to use a
subform format.
When the case form is opened a caseid is assigned and combined with client id
to become the PK of that table. This combined Key links all future tables.
So with the right code I should not have a problem even though I am not using
them as true subforms.

 

[John Vinson]

When the case form is opened a caseid is assigned and combined with client id
to become the PK of that table. This combined Key links all future tables.
So with the right code I should not have a problem even though I am not using
them as true subforms.

Thanks for the explanation; yes, that makes sense.

What you'll need to do, then, is pass the ClientID in the OpenArgs
argument of the OpenForm method, and then put code in each child
form's Open event to set the ClientID control's DefaultValue property
to the passed value. Something like

DoCmd.OpenForm "formname", OpenArgs:=Me!txtClientID

in the button code; and

Private Sub Form_Open(Cancel as Integer)
Me!txtForeignKeyControl.DefaultValue = Chr(34) & Me.OpenArgs & Chr(34)
End Sub

John W. Vinson[MVP]

 

[G deady via AccessMonster.com]

John, thank you. I have 4 follow up questions. I apologize for the length
of this post.
1. When I talk about the caseid and clientid being combined to create new PK
which then links all future tables Im not talking concantation. I mean
basing a PK on 2 fields-ie-ClientID Field AND CaseID Field = PK. So I need
to modify your code to add an arguement to pase the caseid as well. How do I
add the second Arguement?

2.Some of these tables need to allow duplicates. For examle, casetbl to
mainaccident is 1 to 1 as is mainaccident to accidentdetails but mainaccident
to pleadings is 1 to many. I assume changing the indexes on the client &
caseids to allow or not allow duplicates in each table will not effect your
code?

3. The Casetable is an artificial table to get around the Access limitation
on the number of relationships to one table. In an ideal world, the
relationship should be directly from client to auto, then auto to everything
else, client to slip and fall, slip and fall to everthing else, client to
divorce, etc. When I did this I ran up again Access's limitation on
relationships on my client table. to solve it I created it by creating 4
tables-State civil, statecriminal, federalcivil, federalcriminal. They really
serve no purpose other than to divide up my relationships. My question is,
while each of these 4 tables assigns a caseid and creates the new primary key
the relationship actionship actually flows from the specific maincase table.
Example: clientdata is filled in. There are 4 buttons on client form:
StateCivil,StateCriminal,FederalCriminal & FederalCriminal. Say its an
AutoAccident. The statecivil button is used to open statecivilform. A
caseid is assigned . The clientID AND caseID are the combined PK. There is a
combo box bound to a lookup table from which the user selects the specic type
of statecivil case-ie autoaccident. I use the following code to open the
chosen case form (I've only posted a few possible selections. There are many
more. The code is identical. Only the name of the form is the same.

Private Sub Combo12_AfterUpdate()
Select Case Me.Combo12
'Case statement is choice made by user in combobox
'The DoCmd.OpenForm Statement opens the Form chosen by user
'The DoCmd.Closes the StateCivilCase Form
Case "Administrative Law"
DoCmd.OpenForm "frmAdministrativeLaw"
DoCmd.Close
Case "Auto Accidents"
DoCmd.Save
DoCmd.Close
DoCmd.OpenForm "frm_AutoAccidents"
Case "Bad Faith"
DoCmd.Close
DoCmd.OpenForm "frmBadFaith"


The code then continues with an identical case statement for each choice in
the combobox.

My question is even though it is the statecivilcaseform that creates the
combined client and caseid PK,(in this example. It could be the the
statecriminalcaseform in another example or eother of thercaseforms) the
relationship really flows from the table the form selected here is bound to-
in this example auto accidents. Accidentdetails, injuries, Pleadings,
Medicalbills, etc are all bound to the autoaccident form selected here and
not the statecivilcase form which created the combined client and caseID key.
Is this a problem?

4. Lastly, is it to possible to modify the code you gave me to add it to my
case statements for each form?
Again, I apologize for the length of this post.