Form/ subform issues - is this a bug?

[Guest]

Hi

I have an interseting problem with a form/subform. I am building a
competency database. For each given competency there are a range of grades so
that 'French' as a competency might have the choices 'basic,
intermediate,conversant,fluent,expert' and the competency 'Car licence' might
have 'learner, full'

the table schema is

tblCompetency
Name pk
Description
Type
etc...

tblCompetencyGrade fk pk
CompetencyName pk
GradeName
Description
etc...

so then I want to create a master/ sub form to present this information. I
firstly built the form manually then used the wizard and in both cases I get
the same perculiar error. the CompetencyName field on the subform gets
defaulted to the name of the master talbe... ie 'tblCompetency'. If I change
the name of the table the new name is defaulted into the field. This means I
can't use the form for adding new records as this field is hidden and is in
the primary key of tblCompetency. If I take out the hidden field I get the
same error.

Can anyone help with this

Thanks

Ianb

 

[Rick Brandt]

Hi

I have an interseting problem with a form/subform. I am building a
competency database. For each given competency there are a range of
grades so that 'French' as a competency might have the choices 'basic,
intermediate,conversant,fluent,expert' and the competency 'Car
licence' might have 'learner, full'

the table schema is

tblCompetency
Name pk
Description
Type
etc...

tblCompetencyGrade fk pk
CompetencyName pk
GradeName
Description
etc...

so then I want to create a master/ sub form to present this
information. I firstly built the form manually then used the wizard
and in both cases I get the same perculiar error. the CompetencyName
field on the subform gets defaulted to the name of the master
talbe... ie 'tblCompetency'. If I change the name of the table the
new name is defaulted into the field. This means I can't use the form
for adding new records as this field is hidden and is in the primary
key of tblCompetency. If I take out the hidden field I get the same
error.

Can anyone help with this

Sounds like it is working exactly as a form/subform is supposed to work. The
idea with a form/subform is that one or more fields in the subform are related
(and thus equal to) one or more fields in the parent form. This is controlled
by the MasterLink and ChildLink properties of the subform control.

If you do not want this behavior then why use a form/subform setup?

 

[Guest]

Thanks Rick for your early reply.
the problem is that the master *form name* is being defaulted into the
foreign key field on the subform. I need the related data in the primery key
field of the master table to be defaulted into the foreign key field on the
subform.

Oddly the correct data is present in the subform but when the user adds new
records they get a message that "You cannot add or change a record because a
related record is required in table tblCompetency". This is because the
foreign key field on hte subform contains (of all things) the master *form
name*

 

[Rick Brandt]

Thanks Rick for your early reply.
the problem is that the master *form name* is being defaulted into the
foreign key field on the subform. I need the related data in the
primery key field of the master table to be defaulted into the
foreign key field on the subform.

Oddly the correct data is present in the subform but when the user
adds new records they get a message that "You cannot add or change a
record because a related record is required in table tblCompetency".
This is because the foreign key field on hte subform contains (of all
things) the master *form name*

As stated, all of this behavior is controlled by the two properties MasterLink
and ChildLink of the subform control on the parent form. What are the entries
in those two properties?

 

[Guest]

Name: CompetencyGradeSubform
Source Object: CompetencyGradeSubform
Link Child Fields: CompetencyName
Link Masterfields: Name

 

[Rick Brandt]

Name: CompetencyGradeSubform
Source Object: CompetencyGradeSubform
Link Child Fields: CompetencyName

"Name" is a reserved word because all objects have a name property. That is why
you are getting the name of the parent form (because that is what you are asking
for).

Change the name of Name and all will be good.

 

[Guest]

Could it be that the child field is linking to the name *property* of the
master form rather than the name *field*?