Main Form/Subform/Command Button to Open Another Form

G

Guest

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?
 
G

Guest

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?
 
G

Guest

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 said:
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 said:
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?
Click to expand...
Click to expand...
 
G

Guest

Thankyou. I will try this.

Lucille said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Autonumber synchronization between subform & main form 6
main form/subform/command button for another subform 11
Button to open sub-form and follow main 7
Form/Subform/Command Button to Open Form - Type Mismatch Error 2
Display Information in subform based on main form selection 2
Pop-Up Subform not linking to Main Form 2
Command Button to open form to specific record 2
Open Subform equal to Main Form Record in Access Project - SQL bac 4

Top