How to sometimes open form to new record?

I have a parent form which is then linked to a child form. I want to
have two buttons on the parent form, one to open the child form and
display all the records with the corresponding foreign key, and one
button to open the child form to a new record with the corresponding
foreign key already inserted. I have figured out how to open the child
form to a new record, but I cannot figure out how to insert the parent
ID into the child foreign key. Help! Thanks.

Megan,
You don't have to enter the ParentID in the Child subfrom records...

Establish a one many relationship between the Parent table and the Child
table (via the ParentID).
Set up the form for a Parent/Child relationship between the main form
and the subform (via ParentID also).

Because of those realtionships, any new record added to the subform will
have it's ParentID set to the ParentID value on the main form.
That's the beauty of a relational database...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

wrote in message
ups.com...
I have a parent form which is then linked to a child form. I want to
have two buttons on the parent form, one to open the child form and
display all the records with the corresponding foreign key, and one
button to open the child form to a new record with the corresponding
foreign key already inserted. I have figured out how to open the child
form to a new record, but I cannot figure out how to insert the parent
ID into the child foreign key. Help! Thanks.

On Sep 20, 12:36 am, "Al Campagna" wrote:
Megan,
You don't have to enter the ParentID in the Child subfrom records...

Establish a one many relationship between the Parent table and the Child
table (via the ParentID).
Set up the form for a Parent/Child relationship between the main form
and the subform (via ParentID also).

Because of those realtionships, any new record added to the subform will
have it's ParentID set to the ParentID value on the main form.
That's the beauty of a relational database...
--
hth
Al Campagna
Microsoft Access MVPhttp://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

wrote in message

ups.com...

I have a parent form which is then linked to a child form. I want to
have two buttons on the parent form, one to open the child form and
display all the records with the corresponding foreign key, and one
button to open the child form to a new record with the corresponding
foreign key already inserted. I have figured out how to open the child
form to a new record, but I cannot figure out how to insert the parent
ID into the child foreign key. Help! Thanks.

Thanks Al. The childform isn't actually a subform, it's an entirely
separate form. i did this because the detail goes down to 6 levels,
and to have that many subforms would be quite cumbersome. Perhaps I've
used the wrong terminology. So is there a way to program a button to
open a form to a new record and insert a field from the old record
into the new, while at the same time allowing that form to be opened
with a different button to the first record? Thanks.

I'll use some generic names here, but you should be able to adapt to your
situation:

"frmMain" - PK is named MainPK

"frmOther" - has a field linked to a foreign key named OtherFK

Add this code to frmOther
Option Compare Database
Option Explicit

Dim frmMainHandle As Form

Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(frmMainHandle.MainPK) Then
MsgBox "No current MAIN record", vbOKOnly + vbCritical, "Error"
Cancel = True
Exit Sub
End If
Me.OtherFK = frmMainHandle.MainPK
End Sub

Private Sub Form_Close()
Set frmMainHandle = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
If Not CurrentProject.AllForms("frmMain").IsLoaded Then
MsgBox "This form should not be opened without frmMain being open.",
vbOKOnly, "Error"
Cancel = True
Exit Sub
End If
Set frmMainHandle = Forms![frmMain].Form
End Sub

On Sep 20, 7:52 am, NKTower wrote:
I'll use some generic names here, but you should be able to adapt to your
situation:

"frmMain" - PK is named MainPK

"frmOther" - has a field linked to a foreign key named OtherFK

Add this code to frmOther
Option Compare Database
Option Explicit

Dim frmMainHandle As Form

Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(frmMainHandle.MainPK) Then
MsgBox "No current MAIN record", vbOKOnly + vbCritical, "Error"
Cancel = True
Exit Sub
End If
Me.OtherFK = frmMainHandle.MainPK
End Sub

Private Sub Form_Close()
Set frmMainHandle = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
If Not CurrentProject.AllForms("frmMain").IsLoaded Then
MsgBox "This form should not be opened without frmMain being open.",
vbOKOnly, "Error"
Cancel = True
Exit Sub
End If
Set frmMainHandle = Forms![frmMain].Form
End Sub

OK this is the code for the second form (frmLevel2Goals):
Option Compare Database
Option Explicit

Dim frmMainGoalsHandle As Form

Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(frmMainGoalsHandle.GoalID) Then
MsgBox "No current MAIN record", vbOKOnly + vbCritical, "Error"
Cancel = True
Exit Sub
End If
Me.MainGoalID = frmMainGoalsHandle.GoalID
End Sub

Private Sub Form_Close()
Set frmMainGoalsHandle = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
If Not CurrentProject.AllForms("frmMainGoals").IsLoaded Then
MsgBox "This form should not be opened without frmMain being
open.",
vbOKOnly , "Error"
Cancel = True
Exit Sub
End If
Set frmMainGoalsHandle = Forms![frmMainGoals.Form]
End Sub

Private Sub Level3Btn_Click()
On Error GoTo Err_Level3Btn_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLevel3Goals"

stLinkCriteria = "[Level2ID]=" & Me![Level2ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Level3Btn_Click:
Exit Sub

Err_Level3Btn_Click:
MsgBox Err.Description
Resume Exit_Level3Btn_Click

End Sub
Private Sub NewLevel3Btn_Click()
On Error GoTo Err_NewLevel3Btn_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLevel3Goals"

stLinkCriteria = "[Level2ID]=" & Me![Level2ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , "GoToNew"


Exit_NewLevel3Btn_Click:
Exit Sub

Err_NewLevel3Btn_Click:
MsgBox Err.Description
Resume Exit_NewLevel3Btn_Click

End Sub
Private Sub BacktoMainGoals_Click()
On Error GoTo Err_BacktoMainGoals_Click


DoCmd.Close

Exit_BacktoMainGoals_Click:
Exit Sub

Err_BacktoMainGoals_Click:
MsgBox Err.Description
Resume Exit_BacktoMainGoals_Click

End Sub

I am getting an error when I try to open it with the button from the
main form (frmMainGoals) that is saying there's a syntax error in the
Private Sub Form_Open (Cancel as integer) line.

On Sep 20, 7:52 am, NKTower wrote:
I'll use some generic names here, but you should be able to adapt to your
situation:

"frmMain" - PK is named MainPK

"frmOther" - has a field linked to a foreign key named OtherFK

Add this code to frmOther
Option Compare Database
Option Explicit

Dim frmMainHandle As Form

Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(frmMainHandle.MainPK) Then
MsgBox "No current MAIN record", vbOKOnly + vbCritical, "Error"
Cancel = True
Exit Sub
End If
Me.OtherFK = frmMainHandle.MainPK
End Sub

Private Sub Form_Close()
Set frmMainHandle = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
If Not CurrentProject.AllForms("frmMain").IsLoaded Then
MsgBox "This form should not be opened without frmMain being open.",
vbOKOnly, "Error"
Cancel = True
Exit Sub
End If
Set frmMainHandle = Forms![frmMain].Form
End Sub

Here's the code for the frmOther (called frmLevel2Goals):
Option Compare Database
Option Explicit

Dim frmMainGoalsHandle As Form

Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(frmMainGoalsHandle.GoalID) Then
MsgBox "No current MAIN record", vbOKOnly + vbCritical, "Error"
Cancel = True
Exit Sub
End If
Me.MainGoalID = frmMainGoalsHandle.GoalID
End Sub

Private Sub Form_Close()
Set frmMainGoalsHandle = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
If Not CurrentProject.AllForms("frmMainGoals").IsLoaded Then
MsgBox "This form should not be opened without frmMain being
open.",
vbOKOnly , "Error"
Cancel = True
Exit Sub
End If
Set frmMainGoalsHandle = Forms![frmMainGoals.Form]
End Sub

Private Sub Level3Btn_Click()
On Error GoTo Err_Level3Btn_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLevel3Goals"

stLinkCriteria = "[Level2ID]=" & Me![Level2ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Level3Btn_Click:
Exit Sub

Err_Level3Btn_Click:
MsgBox Err.Description
Resume Exit_Level3Btn_Click

End Sub
Private Sub NewLevel3Btn_Click()
On Error GoTo Err_NewLevel3Btn_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLevel3Goals"

stLinkCriteria = "[Level2ID]=" & Me![Level2ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , "GoToNew"


Exit_NewLevel3Btn_Click:
Exit Sub

Err_NewLevel3Btn_Click:
MsgBox Err.Description
Resume Exit_NewLevel3Btn_Click

End Sub
Private Sub BacktoMainGoals_Click()
On Error GoTo Err_BacktoMainGoals_Click


DoCmd.Close

Exit_BacktoMainGoals_Click:
Exit Sub

Err_BacktoMainGoals_Click:
MsgBox Err.Description
Resume Exit_BacktoMainGoals_Click

End Sub

I'm getting a syntax error on the Private Sum Form_Open (Cancel as
Integer) line when I try to open the form. And will this make it so
that one button on the main form (frmMainGoals) opens frmLevel2Goals
to a new record while another button opens it to the beginning record?
Thanks.

Syntax error - I suspect that the MsgBox line "wrapped" when it
shouldn't. The line starts with MsgBox and ends with "Error" - all on one
line. That could be what's giving you the syntax error.

Which records - the previous response only guarantees that a new record
in frmLevel2Goals will have the foreign key value that matches the PK of
frmMainGoals.

To make frmLevel2Goals displayed records stay in synch with the current
position of frmMainGoals when you move to a different record you need to
have the main form cause a requery of the "sub' form (although it isn't
really a subform in Access terminology). You do it via the On Current event,
something like this. ( I've had to use pseudo code as I don't have your
table and column names handy.):

in frmMainGoals

Private Sub Form_Current()
Dim frm_Level2GoalsHandle As Form

' check to see if the Level2Goals form is loaded as per other example
If CurrentProject .AllForms("frmLevel2Goals").IsLoaded Then
Dim SQL As String

SQL = "SELECT * FROM tblLevel2GoalsSourceTable WHERE (linkToMainFK = " &
Me.PKfield & ")"

Set frm_Level2GoalsHandle = Forms![frmLevel2Goals].Form
frm_Level2GoalsHandle.Recordsource = SQL
frm_Level2GoalsHaneld.Requery
Set frm_Level2GoalsHandle = Nothing
End If

Button on MAIN form to go to new record

Private Sub btn_Level2GoalsNewRecord_Click()
Dim frm_Level2GoalsHandle As Form

If CurrentProject .AllForms("frmLevel2Goals").IsLoaded Then
Set frm_Level2GoalsHandle = Forms![frmLevel2Goas].Form
frm_Lvel2GoalsHandle.Filter = "([PK] = -1 )"
' the above is impossible, so you will only get the new record
frm_Level2GoalsHandle.FilterOn = True
set frm_Level2GoalsHandle = Nothing
End If
End Sub

Private Sub btn_Level2GoalsAllRecords_Click()
Dim frm_Level2GoalsHandle As Form

If CurrentProject .AllForms("frmLevel2Goals").IsLoaded Then
Set frm_Level2GoalsHandle = Forms![frmLevel2Goas].Form
frm_Level2GoalsHandle.FilterOn = False
End If
End Sub




" wrote:


I'm getting a syntax error on the Private Sum Form_Open (Cancel as
Integer) line when I try to open the form. And will this make it so
that one button on the main form (frmMainGoals) opens frmLevel2Goals
to a new record while another button opens it to the beginning record?
Thanks.

On Sep 20, 9:04 am, NKTower wrote:
Syntax error - I suspect that the MsgBox line "wrapped" when it
shouldn't. The line starts with MsgBox and ends with "Error" - all on one
line. That could be what's giving you the syntax error.

Which records - the previous response only guarantees that a new record
in frmLevel2Goals will have the foreign key value that matches the PK of
frmMainGoals.

To make frmLevel2Goals displayed records stay in synch with the current
position of frmMainGoals when you move to a different record you need to
have the main form cause a requery of the "sub' form (although it isn't
really a subform in Access terminology). You do it via the On Current event,
something like this. ( I've had to use pseudo code as I don't have your
table and column names handy.):

in frmMainGoals

Private Sub Form_Current()
Dim frm_Level2GoalsHandle As Form

' check to see if the Level2Goals form is loaded as per other example
If CurrentProject .AllForms("frmLevel2Goals").IsLoaded Then
Dim SQL As String

SQL = "SELECT * FROM tblLevel2GoalsSourceTable WHERE (linkToMainFK = " &
Me.PKfield & ")"

Set frm_Level2GoalsHandle = Forms![frmLevel2Goals].Form
frm_Level2GoalsHandle.Recordsource = SQL
frm_Level2GoalsHaneld.Requery
Set frm_Level2GoalsHandle = Nothing
End If

Button on MAIN form to go to new record

Private Sub btn_Level2GoalsNewRecord_Click()
Dim frm_Level2GoalsHandle As Form

If CurrentProject .AllForms("frmLevel2Goals").IsLoaded Then
Set frm_Level2GoalsHandle = Forms![frmLevel2Goas].Form
frm_Lvel2GoalsHandle.Filter = "([PK] = -1 )"
' the above is impossible, so you will only get the new record
frm_Level2GoalsHandle.FilterOn = True
set frm_Level2GoalsHandle = Nothing
End If
End Sub

Private Sub btn_Level2GoalsAllRecords_Click()
Dim frm_Level2GoalsHandle As Form

If CurrentProject .AllForms("frmLevel2Goals").IsLoaded Then
Set frm_Level2GoalsHandle = Forms![frmLevel2Goas].Form
frm_Level2GoalsHandle.FilterOn = False
End If
End Sub

" wrote:
I'm getting a syntax error on the Private Sum Form_Open (Cancel as
Integer) line when I try to open the form. And will this make it so
that one button on the main form (frmMainGoals) opens frmLevel2Goals
to a new record while another button opens it to the beginning record?
Thanks.

OK thanks. here's the code for the main form, frmMainGoals. The PK is
GoalID. The second form is frmLevel2Goals, with a PK of Level2ID and
the FK of MainGoalID. When I click on Level2Btn or Level2BtnNew,
nothing happens. I would like it to open frmLevel2Goals with only the
corresponding records, or to a new record with the corresponding
MainGoal PK inserted as the MainGoal FK, if that makes sense. I am
able now to open frmLevel2Goals without getting the syntax error (it
was, as you suspected, that the line had gotten cut off). Thanks.

Private Sub Form_Current()
Dim frmLevel2GoalsHandle As Form

' check to see if the Level2Goals form is loaded as per other
example
If CurrentProject.AllForms("frmLevel2Goals").IsLoaded Then
Dim SQL As String

SQL = "SELECT * FROM tblLevel2GoalsSourceTable WHERE
(linkToMainGoalID = " & Me.GoalID & ")"

Set frm_Level2GoalsHandle = Forms![frmLevel2Goals].Form
frmLevel2GoalsHandle.RecordSource = SQL
frmLevel2GoalsHandle.Requery
Set frmLevel2GoalsHandle = Nothing
End If
End Sub

Private Sub Level2BtnNew_Click()
Dim frmLevel2GoalsHandle As Form

If CurrentProject.AllForms("frmLevel2Goals").IsLoaded Then
Set frmLevel2GoalsHandle = Forms![frmLevel2Goasl].Form
frmLvel2GoalsHandle.Filter = "([Level2ID] = -1 )"
' the above is impossible, so you will only get the new record
frmLevel2GoalsHandle.FilterOn = True
Set frmLevel2GoalsHandle = Nothing
End If
End Sub

Private Sub Level2Btn_Click()
Dim frmLevel2GoalsHandle As Form

If CurrentProject.AllForms("frmLevel2Goals").IsLoaded Then
Set frmLevel2GoalsHandle = Forms![frmLevel2Goals].Form
frmLevel2GoalsHandle.FilterOn = False
End If
End Sub

I had assumed that the Level2Goals form was already opened. Let's change the
button code as follows:


Private Sub Level2BtnNew_Click()
Dim frmLevel2GoalsHandle As Form

If NOT CurrentProject.AllForms("frmLevel2Goals").IsLoaded Then
DoCmd.OpenForm "frmLvel2Goals",acNormal
End If
DoEvents ' let it have some time to open
Set frmLevel2GoalsHandle = Forms![frmLevel2Goasl].Form
frmLvel2GoalsHandle.Filter = "([Level2ID] = -1 )"
' the above is impossible, so you will only get the new record
frmLevel2GoalsHandle.FilterOn = True
Set frmLevel2GoalsHandle = Nothing
End Sub

Private Sub Level2Btn_Click()
Dim frmLevel2GoalsHandle As Form

If Not CurrentProject.AllForms("frmLevel2Goals").IsLoaded Then
DoCmd.OpenForm "frmLevel2Goals", acNormal
End If
DoEvents
Set frmLevel2GoalsHandle = Forms![frmLevel2Goals].Form
frmLevel2GoalsHandle.FilterOn = False
End Sub

On Sep 20, 10:38 am, NKTower
wrote:
I had assumed that the Level2Goals form was already opened. Let's change the
button code as follows:

Private Sub Level2BtnNew_Click()
Dim frmLevel2GoalsHandle As Form

If NOT CurrentProject.AllForms("frmLevel2Goals").IsLoaded Then
DoCmd.OpenForm "frmLvel2Goals",acNormal
End If
DoEvents ' let it have some time to open
Set frmLevel2GoalsHandle = Forms![frmLevel2Goasl].Form
frmLvel2GoalsHandle.Filter = "([Level2ID] = -1 )"
' the above is impossible, so you will only get the new record
frmLevel2GoalsHandle.FilterOn = True
Set frmLevel2GoalsHandle = Nothing
End Sub

Private Sub Level2Btn_Click()
Dim frmLevel2GoalsHandle As Form

If Not CurrentProject.AllForms("frmLevel2Goals").IsLoaded Then
DoCmd.OpenForm "frmLevel2Goals", acNormal
End If
DoEvents
Set frmLevel2GoalsHandle = Forms![frmLevel2Goals].Form
frmLevel2GoalsHandle.FilterOn = False
End Sub

Oh that's wonderful! The only problem is that there needs to be a
filter on the Level2Btn so that frmLevel2Goals only opens the records
where the FK matches the MainGoals PK. Thanks so much!