#Name? on subform where text should be

[mcbaker]

I have two tables:
Master is Projects with ProjectID as the primary key
Subform is Communications with NoteID as the primary key
Each table has a Project ID Field and NoteID field. They are linked as
one-to-one in the relationships table.

I wish to display in the communications subform the Project Name, which is a
field in the Projects Table. I have two problems in getting the display to
be correct

1. The Communications table does not automatically filter the records so
that only those associated with the Project that is active in the projects
table display.

2. I get #Name? message in the field where I call for the project title.

Any ideas what I am doing wrong and how I can correct it?

Thanks.

 

[Jeff Boyce]

Hmmm? Why does the Projects table contain a NoteID field? If it is the
"master", then the Communications table would need to know which "parent",
but the parent (Projects) shouldn't contain (all of) the "children" IDs.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Sheridan]

Judy:

To show a value from a parent form in a subform add an unbound text box to
the latter and reference the former by means of the Parent property. So in
your case the ControlSource property of the text box would be along theses
lines:

=[Parent].[ProjectName]

Quite why you want the project name in the subform is unclear though, as you
can show in it in the parent form with a simple bound control.

However, as Jeff has pointed out, your table designs are a bit messed up.
You should have a one-to-many relationship from Projects to Communications,
which you achieve by having a foreign key column in the latter referencing
the primary key of the former, so the tables would be like this:

Projects
----ProjectID (primary key)
----ProjectTitle
and so on

Communications
----NoteID (primary key)
----ProjectID (foreign key)
----Note
and so on

Note that while you define a primary key column in the table design, you do
not do so with a foreign key column. Its the relationship, not the table
definition per se, which makes it a foreign key. Make sure that its indexed
non-uniquely (duplicates allowed) in the Communications table's definition
however.

You can now relate the tables on ProjectID. Note that while this column can
for convenience be an autonumber column in Projects, in Communications it
must be a straightforward long integer number data type, not an autonumber.

Enforce referential integrity when you create the relationship; this
prevents any rows being entered in Communications unless a matching project
record already exists. It also prevents a project record being deleted if
one or more matching communications records exist; unless, that is, you've
also enforced cascade deletes when setting up the relationship, something you
should consider carefully, and only do if you really want to be able to
delete a project record and all its matching communications records in one
step.

If the ProjectID primary key column in projects is not an autonumber you
should also enforce cascade updates; this causes any matching ProjectID
values in Communications to be automatically updated if you change the value
of a ProjectID in projects.

You can create a form, in single form view, based on Projects and within it
a subform, in single form, continuous form or datasheet view as you prefer,
based on Communications. The LinkMasterFields and LinkChildFields properties
of the subform control will both be ProjectID. You don't need to show the
projected in a control in the subform. Its value will be inserted
automatically into the underlying Communications table by virtue of the link.

Ken Sheridan
Stafford, England

 

[mcbaker]

After your message and the one following, I see that perhaps I have been
going about the table design incorrectly. Let me try to correct my errors,
and see if that doesn't help. I'll repost if I need more help.

Thanks!