Subform Problem

[PraizesLiberato]

Hi,

I have a subform(OtherFac - 2nd Table) inside a form (FLAM - 1st Table) but now i want to make another subform from another [Oblig - 3rd table].
But when i try to create a relationship between the 2nd and the 3rd table its gives me an indeterminate relationhip not a one to many which is what i need.

The way i want to link the 3rd table is by using a button to link it. But when i run this table 3 doesnt doesn't get the primary key of table two. an thus the record is created but the link isnt there and field is showing PK is 0 for every record.

[FLAM] -1toMany-> [OtherFac] -Indetermiante-> [Oblig]

any help out there.

Also in viewing this data im still yet to test whether its will view in a report.

 

[PNGBill]

Your mainform is a single form. Is your subform also a single form?

and the subsubform is continuous ? - correct?

 

[PraizesLiberato]

The 2nd form and the 3rd for is both continuous.

 

[PraizesLiberato]

The 2nd form and the 3rd for is both continuous.

the main form FLAM is Single.

 

[PNGBill]

If your Main form is Single, then you can have SubForms off this.

If a form or subform is Continuous, then I understand you can not have a subform off that.

You can a have a Form opened by a Command Button on the Record of your Continuous form/subform that will open the new form and you can link same so just the data you want displayed will appear.
This can be done in the vba of the on click event of the command button.

If the continuous subform has 20 records then you will see 20 buttons.

 

[PraizesLiberato]

Private Sub ToggleLink_Click()
On Error GoTo ToggleLink_Click_Err
If ChildFormIsOpen() Then
CloseChildForm
Else
OpenChildForm
FilterChildForm
End If
ToggleLink_Click_Exit:
Exit Sub
ToggleLink_Click_Err:
MsgBox Error$
Resume ToggleLink_Click_Exit
End Sub

Private Function ChildFormIsOpen()
ChildFormIsOpen = (SysCmd(acSysCmdGetObjectState, acForm, "Obligors_Sub") And acObjStateOpen) <> False
End Function

Private Sub CloseChildForm()
DoCmd.Close acForm, "Obligors_Sub"
If Me![ToggleLink] Then Me![ToggleLink] = False
End Sub

Private Sub OpenChildForm()
DoCmd.OpenForm "Obligors_Sub"
If Not Me.[ToggleLink] Then Me![ToggleLink] = True
End Sub

Private Sub FilterChildForm()
If Me.NewRecord Then
Forms![Obligors_Sub].DataEntry = True
Else
Forms![Obligors_Sub].Filter = "[OtherFacid] = " & Me.OtherFacid
Forms![Obligors_Sub].FilterOn = True
End If
End Sub


But still its not saving the obligors uner the OtherFAC Primary key creating the link.

 

[PraizesLiberato]

Anyone have any ideas.

 

[vbaInet]

First of all, go to the Relationships and Enforce Referential Integrity between [OtherFac] and [Oblig].

 

[PraizesLiberato]

First of all, go to the Relationships and Enforce Referential Integrity between [OtherFac] and [Oblig].

that doesnt work. it gives me an error

 

[stopher]

To create a proper one to many, the "one" table must have a PK and the FK in the "many" must be linked to the PK in the "one". Then you can set ref.integ.

Chris

 

[vbaInet]

Sort out the records in the table (Obligators) before enforcing referential integrity. The field [OtherFacility] must match in both tables. If it doesn't exists in MOF_OtherFacilities then it cannot exists in Obligators.

 

[stopher]

If you want to show two continuous "linked" subforms, then add the first subform as normal. Then add the second but don't attempt to set the parent/child links. Instead, use the code behind the button on your first continuous subform to filter the second subform.

You'll have to give more thought if you're adding records to your second subform because the foreign key will have to be looked up from the selection made from the first subform.

hth
Chris

 

[PraizesLiberato]

Problem Solved with the realtionship.

Yes (Duplicates OK) was set in the Indexed property of my First tables PK.

Therefore even if it is an autonumber field it allowed duplicates. I think thats why Enforce Referential Integrity didnt work. Now im getting a one to many relationship.

But when i add the value in the form and expect it to have gotten linked to the previous form; that still doesnt work, but is giving me an error message.

 

[vbaInet]

Therefore even if it is an autonumber field it allowed duplicates. I think thats why Enforce Referential Integrity didnt work. Now im getting a one to many relationship.

You shouldn't set Allow Duplicates for Autonumber fields. It's rather meaningless. Is your Autonumber field set as the PK?

But when i add the value in the form and expect it to have gotten linked to the previous form; that still doesnt work, but is giving me an error message.

This is this the second subform right?

 

[PraizesLiberato]

This is this the second subform right?

Yes it is.

 

[vbaInet]

There are two options:

1. Put that subform inside the first subform and Link Master Fields using the ID on that subform. This would work if the first subform is not set as a Datasheet.

OR

2. Use code or a query to link this subform to the other subform.

Which do you prefer?