Subform Problem

[Nick CWT]

I'm a relative Newbie so bare with me please. I have created a subform that
does not stay "dedicated" to the Master. For example the subform is a
satisfaction survey specific to the customer (master). When I input data
into the Smith's satisfaction survey it comes up in all the other
satisfaction surveys; Jones, Q Public etc. I have a one to many relationship
for the tables and have customer name and customer # as my linked fields
form>subform. I appreciate any help. Thanks.

 

[Rick Brandt]

I'm a relative Newbie so bare with me please. I have created a
subform that does not stay "dedicated" to the Master. For example
the subform is a satisfaction survey specific to the customer
(master). When I input data into the Smith's satisfaction survey it
comes up in all the other satisfaction surveys; Jones, Q Public etc.
I have a one to many relationship for the tables and have customer
name and customer # as my linked fields form>subform. I appreciate
any help. Thanks.

This is an *embedded subform* right? Not a form you are opening separately,
right? If so, when you say "linked fields form>subform" are you talking about
the MasterLink and ChildLink properties of the subform control? Those are what
keep the forms synchronized.

 

[tina]

forget the forms for a minute. what is the primary key of tblCustomers? did
you add the primary key field(s) to tblSurveys as a foreign key, and then
link the two tables in the Relationships window - FROM tblCustomers TO
tblSurveys? did you enforce Referential Integrity in the join?

hth

 

[Nick CWT]

This is an *embedded subform* right? Not a form you are opening separately,
right? If so, when you say "linked fields form>subform" are you talking about
the MasterLink and ChildLink properties of the subform control? Those are what
keep the forms synchronized.

Thanks for your response.
The subform is embedded but I have it invisible and access it thru a command
button. Yes the linked fields are the Master and Child links. I'm not sure
if this is the appropriate way to approach the subform but it looks "cleaner"
going thru the command rather than having it visible. Thanks again.

 

[Nick CWT]

forget the forms for a minute. what is the primary key of tblCustomers? did
you add the primary key field(s) to tblSurveys as a foreign key, and then
link the two tables in the Relationships window - FROM tblCustomers TO
tblSurveys? did you enforce Referential Integrity in the join?

hth

Thanks for replying.
The primary key for Customer tbl is the ID. I have no primary key in the
tlbSurveys? I did not enforce Referential Integrity in the join.....could
that be it?

 

[Rick Brandt]

Thanks for your response.
The subform is embedded but I have it invisible and access it thru a
command button. Yes the linked fields are the Master and Child
links. I'm not sure if this is the appropriate way to approach the
subform but it looks "cleaner" going thru the command rather than
having it visible. Thanks again.

Just to review the "standard" way to set a form/subform.

Main form has as its RecordSource a single table or a query based on a single
table.

Subform has as its RecordSource a single table or a query based on a single
table.

The MasterLink property is set to the name of a field in the main form's
RecordSource or to the name of a Control on the main form.

The ChildLink property is set to the name of a field in the subform's
RecordSource.

Does your setup agree with all of this? By the way you should ONLY need to be
linking on your [Customer #] field. The [Customer Name] should not exist in the
child record so there should be no reason for that to be included in the link.
Also since you put spaces in the names (bad idea) they need to have the square
brackets around them in the MasterLink and ChildLink properties. Do you have
that?

 

[Nick CWT]

Thanks for your response.
The subform is embedded but I have it invisible and access it thru a
command button. Yes the linked fields are the Master and Child
links. I'm not sure if this is the appropriate way to approach the
subform but it looks "cleaner" going thru the command rather than
having it visible. Thanks again.

Just to review the "standard" way to set a form/subform.

Main form has as its RecordSource a single table or a query based on a single
table.

Subform has as its RecordSource a single table or a query based on a single
table.

The MasterLink property is set to the name of a field in the main form's
RecordSource or to the name of a Control on the main form.

The ChildLink property is set to the name of a field in the subform's
RecordSource.

Does your setup agree with all of this? By the way you should ONLY need to be
linking on your [Customer #] field. The [Customer Name] should not exist in the
child record so there should be no reason for that to be included in the link.
Also since you put spaces in the names (bad idea) they need to have the square
brackets around them in the MasterLink and ChildLink properties. Do you have
that?


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



Nick wrote:

It all agrees to that now; I adjusted the spaces in the field names. Still
getting the same problem; Subform opens with the appropriate linked fields
populated but when I put data in subform specific fields they also populate
other customers subforms?

 

[Rick Brandt]

It all agrees to that now; I adjusted the spaces in the field names.
Still getting the same problem; Subform opens with the appropriate
linked fields populated but when I put data in subform specific
fields they also populate other customers subforms?

Not sure I follow that second part. It sounds like you're saying that if you
open the main form and you are looking at the first customer record that you DO
see the correct data for that customer in the subform. Is that right? Now if
you simply navigate in the main form, does the subform continually change to
show the records for that customer? Or do you not have any subform data entered
yet?

If the above occurs then that suggests that you have the MasterLink and
ChildLink properties set correctly.

Are you sure you have your controls on the subform bound to fields in the child
table?

 

[tina]

The primary key for Customer tbl is the ID. I have no primary key in the

tlbSurveys? I did not enforce Referential Integrity in the join.....could
that be it?

i didn't ask about a primary key in tblSurveys, hon. (yes, i would add a
primary key field - probably Autonumber - to tblSurveys, but it doesn't
impact the link between the two tables).
did you add the ID field (tblCustomers' primary key field) to tblSurveys as
a *foreign key*?
(note that the foreign key field does *not* have to have the same name, but
it must be the same data type - or if ID is an Autonumber, then the foreign
key field must have a Long Integer data type.)
and did you then link the two tables on those matching key fields, FROM
tblCustomers TO tblSurveys?
if you did that correctly - then, yes, you should enforce referential
integrity in the join, in the Relationships window.

having done the above (and borrowing from Rick's advice):
make sure that the main form is bound to tblCustomers (or a query built from
same).
make sure that the subform is bound to tblSurveys (or a query built from
same).
in the main form design view, select the subform control and set the
LinkMasterFields property to ID, and the LinkChildFields property to the
name of the *foreign key* field in tblSurveys.

The subform is embedded but I have it invisible and access it thru a command
button. Yes the linked fields are the Master and Child links. I'm not sure
if this is the appropriate way to approach the subform but it looks "cleaner"
going thru the command rather than having it visible. Thanks again.

don't know what you mean by "cleaner". when the mainform/subform setup is
done correctly, the subform will *always* show only those records that are
related to the current mainform record. no coding or additional steps are
needed. suggest you review the command button code to make sure it's not
undermining the correct mainform/subform setup.

hth