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

  • Thread starter Thread starter Grahammer
  • Start date Start date
G

Grahammer

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.
 
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 said:
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 said:
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?
Click to expand...
Click to expand...
 
If you set the LinkMaster and Linkchild properties for the subcontrol control on
the main form then this all happens automatically - no code required!
 
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 said:
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 said:
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 said:
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
Click to expand...
to
the
parent form. When I add new records using the child form I need to
Click to expand...
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
Click to expand...
the
key
after I show the form.

How can I pass the parent's key to the child form so it will be
Click to expand...
Click to expand...
applied
to
Click to expand...
Click to expand...
 
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 said:
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 said:
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?
Click to expand...
Click to expand...
 
OK!

--
Reggie

----------
Grahammer said:
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 said:
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 said:
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
Click to expand...
to
the
parent form. When I add new records using the child form I need to
Click to expand...
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
Click to expand...
the
key
after I show the form.

How can I pass the parent's key to the child form so it will be
Click to expand...
Click to expand...
applied
to
Click to expand...
Click to expand...
 
Back
Top