Linked, Separate Subform

[Guest]

The real world forced me to remove a column from one of my tables and replace
it with a new many-to-one table table linked by primary key. When I replaced
the equivalent field in my form with a subform to provide multipke entries,
Access forced me to switch from Continuous Forms to Single Forms as the Main
Form view.

I'd like to keep Continuous Forms in the Main form. Can I make the subform
separate and still have it linked to the Main form? My guess is, Yes - but
what's the easiest way to do it?

Thanks,
Bill

PS Somewhere in Access Help was a suggestion to use Linked Forms, but I can
find no related topic.

 

[Marshall Barton]

The real world forced me to remove a column from one of my tables and replace
it with a new many-to-one table table linked by primary key. When I replaced
the equivalent field in my form with a subform to provide multipke entries,
Access forced me to switch from Continuous Forms to Single Forms as the Main
Form view.

I'd like to keep Continuous Forms in the Main form. Can I make the subform
separate and still have it linked to the Main form? My guess is, Yes - but
what's the easiest way to do it?

Put the continuous form as a subform on an ubound main form.
Add your new (continuous?) form as a second subform on the
main form.

The trick is to set up so the LinkMaster/Child properties of
the second subform control will take care of synchronizing
it with the current record in the first subform. This is
done by adding a hidden(?) text box (named txtLink) to the
main form's header section. Then use a line of code in the
first subform's Current event:
Parent.txtLink = Me.pkfield

 

[Guest]

Thanks Marsh -

Works great.

But I think I sometimes do things the wrong (or less efficient) way. I
couldn't make your suggestions work without also adding a Requery to the On
Enter event of my first subform and a criteria to the data query of my second
subform that looks at the txtLink field.

Am I missing something obvious?

Thanks again,
Bill

 

[Guest]

I tried to make this link but Access didn't show txtLink as a Link Master
option ("Can't build a link between unbound forms" was the message).

It sounds like you did not set the second subform control's
Link Master/Child Fields properties.
Link Master txtLink
Link Child pkfield

What you did will work, but it should be unnecessary.
Changing the subform's record source query is not especially
desireable, not only because of the extra code, but because
you to keep track of an extra query.
--
Marsh
MVP [MS Access]

Works great.

But I think I sometimes do things the wrong (or less efficient) way. I
couldn't make your suggestions work without also adding a Requery to the On
Enter event of my first subform and a criteria to the data query of my second
subform that looks at the txtLink field.

Am I missing something obvious?

 

[Marshall Barton]

It sounds like you did not set the second subform control's
Link Master/Child Fields properties.
Link Master txtLink
Link Child pkfield

What you did will work, but it should be unnecessary.
Changing the subform's record source query is not especially
desireable, not only because of the extra code, but because
you to keep track of an extra query.

 

[Marshall Barton]

That doesn't make sense. The main form doesn't need to be
bound and your subform is bound. There's something odd in
what you are doing here.

Are you sure you used the name of the foreign key field in
the link child property (in spite of me inadvertently
calling it pkfield)?

 

[Guest]

Marsh -

Thanks for sticking this out.

Here's what I have now:
- Parent form with one unbound text box: txtReviewItemId

- Subform1: contains the record data representing the one-side of the
data and includes the single-field PK of the ReviewChecklistItems table,
ReviewItemId. The Current event of this subform says only:
Parent.txtReviewItemId = Me.ReviewItemId
For debugging purposes, I display txtReviewItemId and it always seems
to contain the correct values.

- Subform2: contains the many-side and only has two fields - ReviewItemId
& RelatedStandardId, both of which constitute the PK of the ItemStandard table

When I click on Subform2, go to the Data tab, and click Link Child Fields, I
get the message, "Can't build a link between unbound forms".

Sorry for being so wordy, but I'm sure there's something simple I'm missing.

Thanks again.

That doesn't make sense. The main form doesn't need to be
bound and your subform is bound. There's something odd in
what you are doing here.

Are you sure you used the name of the foreign key field in
the link child property (in spite of me inadvertently
calling it pkfield)?
--
Marsh
MVP [MS Access]

I tried to make this link but Access didn't show txtLink as a Link Master
option ("Can't build a link between unbound forms" was the message).

 

[Marshall Barton]

I can't seem to get that message, no matter what I do
(A2003)

Taking the message as what it says, are you sure that
subform2's record source is ItemStandard and that the field
name is ReviewItemId?

Also, double check that you are setting the Link
Master/properties to:
Link Master txtReviewItemId
Link Child ReviewItemId

If that's what you already have, then I am at a loss for an
explanation and can only suggest that you try some shots in
the dark. First, make sure that the NameAutoCorrect feature
is turned OFF (Tools - Options on the General tab). If
that doesn't help, then try deleting the forms and
recreating them in a test copy of the mdb file.

 

[Guest]

Thanks again, Marsh -- I'll do as you suggested and post the results.

Bill

 

[Guest]

Marsh -

Red face on this end.

When populating Link ChiId Fields and Link Master Fields, I thought I could
select the fields after clicking the ... buttons to the right of these entry
boxes. This is what caused the error message you could not duplicate.

When I FINALLY got the brilliant idea to try typing the link names (since I
couldn't select them), everything worked as you described in the first note.
While I'm not sure why Access doesn't show me the fields to select, I'm sure
there's a practical reason. And I'll try to remember this next time.

Thanks again for all your time and patience.

Bill

 

[Marshall Barton]

It's good to hear that the issue is resolved. Thanks for
the explanation.