I have a main form and a subform1.  The subform1 has a command button that 
opens Form2.

Main Form
MainID(PK)
SubForm1ID (FK)
MainFormInfo
txtInstructionsID - Control Source:  =[Subform1].[Form]![Subform1ID]

Subform1
Subform1ID (PK)
MainID (FK)
Form2ID (FK)
Subform1Info

Form2
Form2ID (PK)
Subfrom1ID (FK)
Form2Info



This is the code behind the command button on Subform1:

Private Sub cmdbtnOpenForm2_Click()
On Error GoTo Err_cmdbtnOpenForm2_Click

    Dim strLinkCriteria As String
    Dim strDocName As String
   
    strDocName = "Form2"
        ' If Subform1ID control is blank, display a message.
    If IsNull(Me![Subform1ID]) Then
        Me![Form2].SetFocus
    Else
        strDocName = "Form2"
        strLinkCriteria = "[Subform1ID] = " & Me![Subform1ID]
            DoCmd.OpenForm strDocName, , , strLinkCriteria, acFormAdd
    End If
  
Exit_cmdbtnOpenForm2_Click:
    Exit Sub

Err_cmdbtnOpenForm2_Click:
    MsgBox Err.Description
    Resume Exit_cmdbtnOpenForm2_Click
    
End Sub

On the main form, I have an unbound text field (txtInstructionsID) with the 
control source:   =[Subfrm1].[Form]![Subform1ID]

Subform1
Link Child Field:  Subform1ID
Link Master Field:  Subform1ID

Form2
Link Child Field:Suform1ID
Link Master Field:  txtInstructionsID


I have FINALLY been able to open Form2 to show the pertinent record based on 
the Subform1ID.  But, when I go to add a new record to Form2, it doesn't let 
me add anything.  What am I doing wrong?

Hi,

I am having trouble doing what i think you have done. How did you link the 
subform upto the record on the main form? I have tried using the links in the 
wizard when you create the button to no avail. What did you do?

"Lucille" wrote:

> I have a main form and a subform1.  The subform1 has a command button that 
> opens Form2.
> 
> Main Form
> MainID(PK)
> SubForm1ID (FK)
> MainFormInfo
> txtInstructionsID - Control Source:  =[Subform1].[Form]![Subform1ID]
> 
> Subform1
> Subform1ID (PK)
> MainID (FK)
> Form2ID (FK)
> Subform1Info
> 
> Form2
> Form2ID (PK)
> Subfrom1ID (FK)
> Form2Info
> 
> 
> 
> This is the code behind the command button on Subform1:
> 
> Private Sub cmdbtnOpenForm2_Click()
> On Error GoTo Err_cmdbtnOpenForm2_Click
> 
>     Dim strLinkCriteria As String
>     Dim strDocName As String
>    
>     strDocName = "Form2"
>         ' If Subform1ID control is blank, display a message.
>     If IsNull(Me![Subform1ID]) Then
>         Me![Form2].SetFocus
>     Else
>         strDocName = "Form2"
>         strLinkCriteria = "[Subform1ID] = " & Me![Subform1ID]
>             DoCmd.OpenForm strDocName, , , strLinkCriteria, acFormAdd
>     End If
>   
> Exit_cmdbtnOpenForm2_Click:
>     Exit Sub
> 
> Err_cmdbtnOpenForm2_Click:
>     MsgBox Err.Description
>     Resume Exit_cmdbtnOpenForm2_Click
>     
> End Sub
> 
> On the main form, I have an unbound text field (txtInstructionsID) with the 
> control source:   =[Subfrm1].[Form]![Subform1ID]
> 
> Subform1
> Link Child Field:  Subform1ID
> Link Master Field:  Subform1ID
> 
> Form2
> Link Child Field:Suform1ID
> Link Master Field:  txtInstructionsID
> 
> 
> I have FINALLY been able to open Form2 to show the pertinent record based on 
> the Subform1ID.  But, when I go to add a new record to Form2, it doesn't let 
> me add anything.  What am I doing wrong?
> 

BennyDHill,

When you are in design view of the main form, click on the perimeter of the 
subform.  The Link Child Fields and the Link Master Fields contain the field 
that "links" the two forms together.  In my example, the fields are MainID, 
which is a primary key in the main form and a foreign key in the subform.  

The command button is on the Subform1 and when you click on it, it opens 
another form (not subform).  I placed an unbound field (txtInstructionsID) on 
the main form that has a control source that refers to the primary key of the 
Subform1,  Subform1ID (i.e., =[Subform1].[Form]![Subform1ID]).  

I have code on on Form2 in the Before Insert as shown below.

Private Sub Form_BeforeInsert(Cancel As Integer)
    With Forms![subfrmToDoProgressNotes]
    If Not IsNull(!ToDoInstructionsID) Then
    Me.ToDoInstructionsID = !ToDoInstructionsID
    End If
    End With
    
End Sub

I have researched and researched not only on this site but on every 
conceivable website to make this work.  The problem seems to be that there 
are a number of different ways that people do this (some of which I could get 
to work, others I was unable to make work).  So, much of it was trial and 
error. It was frustrating because i would fix one problem only to create 
another one.  This time, I think I have gotten the closest to being able to 
do what I want to do but it won't let me add any new records.  Anyway, I am 
stuck at this point!!!  


"BennyDHill" wrote:

> Hi,
> 
> I am having trouble doing what i think you have done. How did you link the 
> subform upto the record on the main form? I have tried using the links in the 
> wizard when you create the button to no avail. What did you do?
> 
> "Lucille" wrote:
> 
> > I have a main form and a subform1.  The subform1 has a command button that 
> > opens Form2.
> > 
> > Main Form
> > MainID(PK)
> > SubForm1ID (FK)
> > MainFormInfo
> > txtInstructionsID - Control Source:  =[Subform1].[Form]![Subform1ID]
> > 
> > Subform1
> > Subform1ID (PK)
> > MainID (FK)
> > Form2ID (FK)
> > Subform1Info
> > 
> > Form2
> > Form2ID (PK)
> > Subfrom1ID (FK)
> > Form2Info
> > 
> > 
> > 
> > This is the code behind the command button on Subform1:
> > 
> > Private Sub cmdbtnOpenForm2_Click()
> > On Error GoTo Err_cmdbtnOpenForm2_Click
> > 
> >     Dim strLinkCriteria As String
> >     Dim strDocName As String
> >    
> >     strDocName = "Form2"
> >         ' If Subform1ID control is blank, display a message.
> >     If IsNull(Me![Subform1ID]) Then
> >         Me![Form2].SetFocus
> >     Else
> >         strDocName = "Form2"
> >         strLinkCriteria = "[Subform1ID] = " & Me![Subform1ID]
> >             DoCmd.OpenForm strDocName, , , strLinkCriteria, acFormAdd
> >     End If
> >   
> > Exit_cmdbtnOpenForm2_Click:
> >     Exit Sub
> > 
> > Err_cmdbtnOpenForm2_Click:
> >     MsgBox Err.Description
> >     Resume Exit_cmdbtnOpenForm2_Click
> >     
> > End Sub
> > 
> > On the main form, I have an unbound text field (txtInstructionsID) with the 
> > control source:   =[Subfrm1].[Form]![Subform1ID]
> > 
> > Subform1
> > Link Child Field:  Subform1ID
> > Link Master Field:  Subform1ID
> > 
> > Form2
> > Link Child Field:Suform1ID
> > Link Master Field:  txtInstructionsID
> > 
> > 
> > I have FINALLY been able to open Form2 to show the pertinent record based on 
> > the Subform1ID.  But, when I go to add a new record to Form2, it doesn't let 
> > me add anything.  What am I doing wrong?
> > 

Thankyou. I will try this.

"Lucille" wrote:

> BennyDHill,
> 
> When you are in design view of the main form, click on the perimeter of the 
> subform.  The Link Child Fields and the Link Master Fields contain the field 
> that "links" the two forms together.  In my example, the fields are MainID, 
> which is a primary key in the main form and a foreign key in the subform.  
> 
> The command button is on the Subform1 and when you click on it, it opens 
> another form (not subform).  I placed an unbound field (txtInstructionsID) on 
> the main form that has a control source that refers to the primary key of the 
> Subform1,  Subform1ID (i.e., =[Subform1].[Form]![Subform1ID]).  
> 
> I have code on on Form2 in the Before Insert as shown below.
> 
> Private Sub Form_BeforeInsert(Cancel As Integer)
>     With Forms![subfrmToDoProgressNotes]
>     If Not IsNull(!ToDoInstructionsID) Then
>     Me.ToDoInstructionsID = !ToDoInstructionsID
>     End If
>     End With
>     
> End Sub
> 
> I have researched and researched not only on this site but on every 
> conceivable website to make this work.  The problem seems to be that there 
> are a number of different ways that people do this (some of which I could get 
> to work, others I was unable to make work).  So, much of it was trial and 
> error. It was frustrating because i would fix one problem only to create 
> another one.  This time, I think I have gotten the closest to being able to 
> do what I want to do but it won't let me add any new records.  Anyway, I am 
> stuck at this point!!!  
> 
> 
> "BennyDHill" wrote:
> 
> > Hi,
> > 
> > I am having trouble doing what i think you have done. How did you link the 
> > subform upto the record on the main form? I have tried using the links in the 
> > wizard when you create the button to no avail. What did you do?
> > 
> > "Lucille" wrote:
> > 
> > > I have a main form and a subform1.  The subform1 has a command button that 
> > > opens Form2.
> > > 
> > > Main Form
> > > MainID(PK)
> > > SubForm1ID (FK)
> > > MainFormInfo
> > > txtInstructionsID - Control Source:  =[Subform1].[Form]![Subform1ID]
> > > 
> > > Subform1
> > > Subform1ID (PK)
> > > MainID (FK)
> > > Form2ID (FK)
> > > Subform1Info
> > > 
> > > Form2
> > > Form2ID (PK)
> > > Subfrom1ID (FK)
> > > Form2Info
> > > 
> > > 
> > > 
> > > This is the code behind the command button on Subform1:
> > > 
> > > Private Sub cmdbtnOpenForm2_Click()
> > > On Error GoTo Err_cmdbtnOpenForm2_Click
> > > 
> > >     Dim strLinkCriteria As String
> > >     Dim strDocName As String
> > >    
> > >     strDocName = "Form2"
> > >         ' If Subform1ID control is blank, display a message.
> > >     If IsNull(Me![Subform1ID]) Then
> > >         Me![Form2].SetFocus
> > >     Else
> > >         strDocName = "Form2"
> > >         strLinkCriteria = "[Subform1ID] = " & Me![Subform1ID]
> > >             DoCmd.OpenForm strDocName, , , strLinkCriteria, acFormAdd
> > >     End If
> > >   
> > > Exit_cmdbtnOpenForm2_Click:
> > >     Exit Sub
> > > 
> > > Err_cmdbtnOpenForm2_Click:
> > >     MsgBox Err.Description
> > >     Resume Exit_cmdbtnOpenForm2_Click
> > >     
> > > End Sub
> > > 
> > > On the main form, I have an unbound text field (txtInstructionsID) with the 
> > > control source:   =[Subfrm1].[Form]![Subform1ID]
> > > 
> > > Subform1
> > > Link Child Field:  Subform1ID
> > > Link Master Field:  Subform1ID
> > > 
> > > Form2
> > > Link Child Field:Suform1ID
> > > Link Master Field:  txtInstructionsID
> > > 
> > > 
> > > I have FINALLY been able to open Form2 to show the pertinent record based on 
> > > the Subform1ID.  But, when I go to add a new record to Form2, it doesn't let 
> > > me add anything.  What am I doing wrong?
> > >