Connecting A Sub Form

[cyber0ne]

Probably another easy question...

I have two data tables. One holds the main data, the other holds
additional data on a many-to-one relationship with the main data.
Using the forms wizard, my goal was to create a form where you enter
the main record, then a button brings up the sub-form where you enter
as many of the additional info records as needed, all linking back to
the main record.

However, the forms don't seem linked in that way. When the sub-form
comes up, the record being entered in no way refers back to the record
on the main form. So, of course, when I try to enter records into the
sub-form I get errors about the referential integrity.

I imagine that one way to solve this would be to, on the button that
shows the sub-form, first post the parent form's record and then pass
its primary key to the sub-form to use in its foreign key. How would
these two steps be accomplished in the code?

Or is there an easier/better way to do this?
-cyber0ne

[Rick Brandt]

cyber0ne wrote:

Probably another easy question...

I have two data tables. One holds the main data, the other holds
additional data on a many-to-one relationship with the main data.
Using the forms wizard, my goal was to create a form where you enter
the main record, then a button brings up the sub-form where you enter
as many of the additional info records as needed, all linking back to
the main record.

However, the forms don't seem linked in that way. When the sub-form
comes up, the record being entered in no way refers back to the record
on the main form. So, of course, when I try to enter records into the
sub-form I get errors about the referential integrity.

I imagine that one way to solve this would be to, on the button that
shows the sub-form, first post the parent form's record and then pass
its primary key to the sub-form to use in its foreign key. How would
these two steps be accomplished in the code?

Or is there an easier/better way to do this?
-cyber0ne

What you have is not a subform. A subform is one that is embedded inside another
using a subform control. What you have is simply a separate form opened
pre-filtered to show records related to the record on the first form. As you
have seen that filter does nothing to provide linking on NEW records you might
enter. All it does is filter on existing records.

Using an actual subform would give you the behavior you want. Otherwise you
have to build into the forms you have additional stuff to provide the linking
for new records. If they are related on a single field then you can set the
default value property for the TextBox on the second form so that it references
the first form's linking field. That entry would look like...

=Forms!NameOfFo rm!NameOfField

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[cyber0ne]

What you have is not a subform. A subform is one that is embedded inside another

using a subform control. What you have is simply a separate form opened
pre-filtered to show records related to the record on the first form. As you
have seen that filter does nothing to provide linking on NEW records you might
enter. All it does is filter on existing records.

Using an actual subform would give you the behavior you want. Otherwise you
have to build into the forms you have additional stuff to provide the linking
for new records. If they are related on a single field then you can set the
default value property for the TextBox on the second form so that it references
the first form's linking field. That entry would look like...

I got my terminologies crossed :) You are correct, though. Using the
sub-form option in the wizard gives me exactly the behavior I want.
Now I can customize it to my needs.

What I was trying to use was linked forms. Given my design, I'd prefer
the linked forms, though the sub-form model will do just fine if need
be. Is there a way to have a linked forms behave as such?
-cyber0ne

[Rick Brandt]

cyber0ne wrote:

What you have is not a subform. A subform is one that is embedded
inside another using a subform control. What you have is simply a
separate form opened pre-filtered to show records related to the
record on the first form. As you have seen that filter does
nothing to provide linking on NEW records you might enter. All it
does is filter on existing records.

Using an actual subform would give you the behavior you want.
Otherwise you have to build into the forms you have additional
stuff to provide the linking for new records. If they are related
on a single field then you can set the default value property for
the TextBox on the second form so that it references the first
form's linking field. That entry would look like...

I got my terminologies crossed :) You are correct, though. Using the
sub-form option in the wizard gives me exactly the behavior I want.
Now I can customize it to my needs.

What I was trying to use was linked forms. Given my design, I'd
prefer the linked forms, though the sub-form model will do just fine
if need be. Is there a way to have a linked forms behave as such?
-cyber0ne

Yes, in the second paragraph of my first post I explained how to do that. If
you want the "linked" form to automatically synch with the first form when you
change records on the first form then you would have to use code in the first
form's Current event.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com