Maintaining one-to-many relationships between main forms and subforms

[tm_6187]

I want to open my subforms from my main forms by using command buttons. My
command buttons work well and successfully open the appropriate subforms,
however, the parent-child or one-to-many relationship is not maintained as it
is if I work off of a nested view where both parent and child are visible at
the same time. For example I want to use an autonumber as my primary key and
then use a number with the same field name as my foreign key in the subform.
Thus, when I click the command button to open the subform, I would like the
primary key's autonumber to populate automatically in the subform's foreign
key field. How do I accomplish this? please help...I am stuck.

 

[ruralguy via AccessMonster.com]

SubForms are displayed on MainForms with the use of a SubFormControl that has
LinkChild/MasterFields properties that does this work for you behind the
scenes. In order to accomplish the same effect with a separate form, you
will need to pass the ForeignKey to the other form and then use maybe the
Dirty Event to populate the FK field.

 

[Ron2006]

Another way:

1) Mainform 1 - the one you currently have with the buttons
A) as part of the code for the button put if me.dirty = true
then me.dirty = false
This will save the record.

2) Create a second "main Form" that has the same table/query as the
record source. The only field necessary is that recordID field

3) Place the subform that you already have designed on this second
mainform and have it use up all the space (make MainForm2 just large
enough to hold the subform)
4) establish the parent/child relationship with this mainform2 ID
information. This will initiate all of the automatic logic of parent
child forms.

5) Go back to the calling buttons and change the form calls to include
(using your field names) the criteria of
"[ID] = " & me.ID


Ron.

 

[tm_6187 via AccessMonster.com]

Ron,

Thanks for your help. Steps 1-4 were applied. Works great so far except
step 5. I do not know what you mean by "form calls". I don't know where to
enter that. Right now, parent-child relationship is maintained while working
on the subform, but when I added a second or new record in the main and then
went to the subform to enter records it did not populate the ID for the
second record of the new form.

EX... My mainform primary key is CaseID, which is the foreign key on my
subform. The primary key is autonumber and foreign key is number. Thus,
when I entered the first record on the main - the CaseID field assigned "1"
and when I clicked on the control button for the subform (steps 1-4 were
applied) CaseID on subform populated "1". When I entered a new record on the
mainform CaseID field assigned "2" but when I clicked the control button for
the subform CaseID remained "1" and did not change to "2". Probably because
I was not smart enough to accomplish your 5th step?

Another way:

1) Mainform 1 - the one you currently have with the buttons
A) as part of the code for the button put if me.dirty = true
then me.dirty = false
This will save the record.

2) Create a second "main Form" that has the same table/query as the
record source. The only field necessary is that recordID field

3) Place the subform that you already have designed on this second
mainform and have it use up all the space (make MainForm2 just large
enough to hold the subform)
4) establish the parent/child relationship with this mainform2 ID
information. This will initiate all of the automatic logic of parent
child forms.

5) Go back to the calling buttons and change the form calls to include
(using your field names) the criteria of
"[ID] = " & me.ID

Ron.

 

[Ron2006]

5) Go back to the calling buttons and change the form calls to
include
(using your field names) the criteria of
"[ID] = " & me.ID


In the ONClick event of the button to call the other form you will
find something like this

Private Sub Command21_Click()
On Error GoTo Err_Command21_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form2"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command21_Click:
Exit Sub

Err_Command21_Click:
MsgBox err.Description
Resume Exit_Command21_Click

End Sub
====================

What you want to do is to add the criteria..
so it will become something like this.

Private Sub Command21_Click()
On Error GoTo Err_Command21_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form2"

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

Exit_Command21_Click:
Exit Sub

Err_Command21_Click:
MsgBox err.Description
Resume Exit_Command21_Click

End Sub
============================

Without that extra little bit of criteria, you were simply looking at
ALL of the records in the subform.
With the criteria you will only get the records that belong to the
main form.

You almost had it. But you will NEVER forget it once you see it
working.......

Ron