Passing value to modal child form in MS Access using VBA?

I'm creating some data entry forms for an MS Access database.

Many of my forms have a child form to create records that are related to the
parent form. When I add new records using the child form I need to apply the
key from the data on the parent form to link them. I currently click a
button on my parent form which opens my child form filtered on matches to
the parent... My child form is bound to an SQL query that includes the
parents key. This works well, EXCEPT when there are no matching child
records... then there are no fields that match and I never get the parents
key. Adding a new record to the child fails since the relationship is not
valid. The code below is what I use.

**Parent Form**
Private Sub cmdServiceArea_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ServiceAreas"
stLinkCriteria = "[BranchKeyLink] = " & Me.[BranchKey]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , vbModal
End Sub

**Child Form**
Private Sub Form_Activate()
txtBranchDesc = BranchDesc
txtBranchKey = BranchKey
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
BranchKeyLink = txtBranchKey
End Sub

What I would like to do is pass the key from the parent form to the child
form, but I don't see how...

I can't put the value onto a hiddle field of the child form, since it hasn't
been shown yet and isn't loaded. The form is modal, so I can't apply the key
after I show the form.

How can I pass the parent's key to the child form so it will be applied to
all the new records?

Grahammer, Open your parent form in design view. Click on the edge of your
child(subform). Click on view/properties from the main menu. Set the Link
Child Fields and Link Master Fields to the Key field of your main
form(Master) to the Foreign Key(child) of the subform. Now whatever record
you are on in the main form, data for the related records will be displayed
in the subform. When you add new record on your main form and move to the
subform to add related records, Access will automatically update the linked
field. No extra work/coding needed on your part. Hope this helps.

--
Reggie

----------
"Grahammer" wrote in message
...
I'm creating some data entry forms for an MS Access database.

Many of my forms have a child form to create records that are related to
the
parent form. When I add new records using the child form I need to apply
the
key from the data on the parent form to link them. I currently click a
button on my parent form which opens my child form filtered on matches to
the parent... My child form is bound to an SQL query that includes the
parents key. This works well, EXCEPT when there are no matching child
records... then there are no fields that match and I never get the parents
key. Adding a new record to the child fails since the relationship is not
valid. The code below is what I use.

**Parent Form**
Private Sub cmdServiceArea_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ServiceAreas"
stLinkCriteria = "[BranchKeyLink] = " & Me.[BranchKey]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , vbModal
End Sub

**Child Form**
Private Sub Form_Activate()
txtBranchDesc = BranchDesc
txtBranchKey = BranchKey
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
BranchKeyLink = txtBranchKey
End Sub

What I would like to do is pass the key from the parent form to the child
form, but I don't see how...

I can't put the value onto a hiddle field of the child form, since it
hasn't
been shown yet and isn't loaded. The form is modal, so I can't apply the
key
after I show the form.

How can I pass the parent's key to the child form so it will be applied to
all the new records?

Thanks... but the child form is not a subform (it's in its own window, not
part of the parent form).

I don't see anyplace to set up this link between two forms.

I do have the relationships between the tables defined, but that doesn't
seem to be enough to get the parents key to migrate to the child table. I do
have "enforce referential integrity" enabled, but not the "cascade update
related fields" or "cascade delete related records".


"Reggie" wrote in message
...
Grahammer, Open your parent form in design view. Click on the edge of your
child(subform). Click on view/properties from the main menu. Set the Link
Child Fields and Link Master Fields to the Key field of your main
form(Master) to the Foreign Key(child) of the subform. Now whatever
record
you are on in the main form, data for the related records will be
displayed
in the subform. When you add new record on your main form and move to the
subform to add related records, Access will automatically update the
linked
field. No extra work/coding needed on your part. Hope this helps.

--
Reggie

----------
"Grahammer" wrote in message
...
I'm creating some data entry forms for an MS Access database.

Many of my forms have a child form to create records that are related to
the
parent form. When I add new records using the child form I need to apply
the
key from the data on the parent form to link them. I currently click a
button on my parent form which opens my child form filtered on matches
to
the parent... My child form is bound to an SQL query that includes the
parents key. This works well, EXCEPT when there are no matching child
records... then there are no fields that match and I never get the
parents
key. Adding a new record to the child fails since the relationship is
not
valid. The code below is what I use.

**Parent Form**
Private Sub cmdServiceArea_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ServiceAreas"
stLinkCriteria = "[BranchKeyLink] = " & Me.[BranchKey]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , vbModal
End Sub

**Child Form**
Private Sub Form_Activate()
txtBranchDesc = BranchDesc
txtBranchKey = BranchKey
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
BranchKeyLink = txtBranchKey
End Sub

What I would like to do is pass the key from the parent form to the
child
form, but I don't see how...

I can't put the value onto a hiddle field of the child form, since it
hasn't
been shown yet and isn't loaded. The form is modal, so I can't apply the
key
after I show the form.

How can I pass the parent's key to the child form so it will be applied
to
all the new records?

If you set the LinkMaster and Linkchild properties for the subcontrol control on
the main form then this all happens automatically - no code required!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications

www.pcdatasheet.com


"Grahammer" wrote in message
...
I'm creating some data entry forms for an MS Access database.

Many of my forms have a child form to create records that are related to the
parent form. When I add new records using the child form I need to apply the
key from the data on the parent form to link them. I currently click a
button on my parent form which opens my child form filtered on matches to
the parent... My child form is bound to an SQL query that includes the
parents key. This works well, EXCEPT when there are no matching child
records... then there are no fields that match and I never get the parents
key. Adding a new record to the child fails since the relationship is not
valid. The code below is what I use.

**Parent Form**
Private Sub cmdServiceArea_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ServiceAreas"
stLinkCriteria = "[BranchKeyLink] = " & Me.[BranchKey]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , vbModal
End Sub

**Child Form**
Private Sub Form_Activate()
txtBranchDesc = BranchDesc
txtBranchKey = BranchKey
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
BranchKeyLink = txtBranchKey
End Sub

What I would like to do is pass the key from the parent form to the child
form, but I don't see how...

I can't put the value onto a hiddle field of the child form, since it hasn't
been shown yet and isn't loaded. The form is modal, so I can't apply the key
after I show the form.

How can I pass the parent's key to the child form so it will be applied to
all the new records?

Lookup - OpenArgs in the help file. Also, have you tried setting the
default value of your child form to =[Forms]![YourMainForm]![PKField]. One
more thing, it's really not a child form in this way. Child form implies
you have embedded a form within a form. Hope this helps!

-
Reggie

----------
"Grahammer" wrote in message
...
Thanks... but the child form is not a subform (it's in its own window, not
part of the parent form).

I don't see anyplace to set up this link between two forms.

I do have the relationships between the tables defined, but that doesn't
seem to be enough to get the parents key to migrate to the child table. I
do
have "enforce referential integrity" enabled, but not the "cascade update
related fields" or "cascade delete related records".


"Reggie" wrote in message
...
Grahammer, Open your parent form in design view. Click on the edge of
your
child(subform). Click on view/properties from the main menu. Set the
Link
Child Fields and Link Master Fields to the Key field of your main
form(Master) to the Foreign Key(child) of the subform. Now whatever
record
you are on in the main form, data for the related records will be
displayed
in the subform. When you add new record on your main form and move to
the
subform to add related records, Access will automatically update the
linked
field. No extra work/coding needed on your part. Hope this helps.

--
Reggie

----------
"Grahammer" wrote in message
...
I'm creating some data entry forms for an MS Access database.

Many of my forms have a child form to create records that are related
to
the
parent form. When I add new records using the child form I need to
apply
the
key from the data on the parent form to link them. I currently click a
button on my parent form which opens my child form filtered on matches
to
the parent... My child form is bound to an SQL query that includes the
parents key. This works well, EXCEPT when there are no matching child
records... then there are no fields that match and I never get the
parents
key. Adding a new record to the child fails since the relationship is
not
valid. The code below is what I use.

**Parent Form**
Private Sub cmdServiceArea_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ServiceAreas"
stLinkCriteria = "[BranchKeyLink] = " & Me.[BranchKey]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , vbModal
End Sub

**Child Form**
Private Sub Form_Activate()
txtBranchDesc = BranchDesc
txtBranchKey = BranchKey
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
BranchKeyLink = txtBranchKey
End Sub

What I would like to do is pass the key from the parent form to the
child
form, but I don't see how...

I can't put the value onto a hiddle field of the child form, since it
hasn't
been shown yet and isn't loaded. The form is modal, so I can't apply
the
key
after I show the form.

How can I pass the parent's key to the child form so it will be
applied
to
all the new records?

Actually, your post was very helpful...

I thought about what you said and my current interface and reworked it into
a single form with a tabControl to flip between the various pages. Works
very slick and linked up well.

....of course now I have another question which I'll post shortly.

"Reggie" wrote in message
...
Grahammer, Open your parent form in design view. Click on the edge of your
child(subform). Click on view/properties from the main menu. Set the Link
Child Fields and Link Master Fields to the Key field of your main
form(Master) to the Foreign Key(child) of the subform. Now whatever
record
you are on in the main form, data for the related records will be
displayed
in the subform. When you add new record on your main form and move to the
subform to add related records, Access will automatically update the
linked
field. No extra work/coding needed on your part. Hope this helps.

--
Reggie

----------
"Grahammer" wrote in message
...
I'm creating some data entry forms for an MS Access database.

Many of my forms have a child form to create records that are related to
the
parent form. When I add new records using the child form I need to apply
the
key from the data on the parent form to link them. I currently click a
button on my parent form which opens my child form filtered on matches
to
the parent... My child form is bound to an SQL query that includes the
parents key. This works well, EXCEPT when there are no matching child
records... then there are no fields that match and I never get the
parents
key. Adding a new record to the child fails since the relationship is
not
valid. The code below is what I use.

**Parent Form**
Private Sub cmdServiceArea_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ServiceAreas"
stLinkCriteria = "[BranchKeyLink] = " & Me.[BranchKey]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , vbModal
End Sub

**Child Form**
Private Sub Form_Activate()
txtBranchDesc = BranchDesc
txtBranchKey = BranchKey
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
BranchKeyLink = txtBranchKey
End Sub

What I would like to do is pass the key from the parent form to the
child
form, but I don't see how...

I can't put the value onto a hiddle field of the child form, since it
hasn't
been shown yet and isn't loaded. The form is modal, so I can't apply the
key
after I show the form.

How can I pass the parent's key to the child form so it will be applied
to
all the new records?

OK!

--
Reggie

----------
"Grahammer" wrote in message
...
Actually, your post was very helpful...

I thought about what you said and my current interface and reworked it
into
a single form with a tabControl to flip between the various pages. Works
very slick and linked up well.

...of course now I have another question which I'll post shortly.

"Reggie" wrote in message
...
Grahammer, Open your parent form in design view. Click on the edge of
your
child(subform). Click on view/properties from the main menu. Set the
Link
Child Fields and Link Master Fields to the Key field of your main
form(Master) to the Foreign Key(child) of the subform. Now whatever
record
you are on in the main form, data for the related records will be
displayed
in the subform. When you add new record on your main form and move to
the
subform to add related records, Access will automatically update the
linked
field. No extra work/coding needed on your part. Hope this helps.

--
Reggie

----------
"Grahammer" wrote in message
...
I'm creating some data entry forms for an MS Access database.

Many of my forms have a child form to create records that are related
to
the
parent form. When I add new records using the child form I need to
apply
the
key from the data on the parent form to link them. I currently click a
button on my parent form which opens my child form filtered on matches
to
the parent... My child form is bound to an SQL query that includes the
parents key. This works well, EXCEPT when there are no matching child
records... then there are no fields that match and I never get the
parents
key. Adding a new record to the child fails since the relationship is
not
valid. The code below is what I use.

**Parent Form**
Private Sub cmdServiceArea_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ServiceAreas"
stLinkCriteria = "[BranchKeyLink] = " & Me.[BranchKey]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , vbModal
End Sub

**Child Form**
Private Sub Form_Activate()
txtBranchDesc = BranchDesc
txtBranchKey = BranchKey
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
BranchKeyLink = txtBranchKey
End Sub

What I would like to do is pass the key from the parent form to the
child
form, but I don't see how...

I can't put the value onto a hiddle field of the child form, since it
hasn't
been shown yet and isn't loaded. The form is modal, so I can't apply
the
key
after I show the form.

How can I pass the parent's key to the child form so it will be
applied
to
all the new records?