PLEASE HLP - SubForm

[dee]

Hi,

I'm new and having tons of problems. The latest:

I managed to create a form and sub form that shows FamilyID and FamilyName
in the Main form and ContactLast, ContactFirst, Tel, Cell, etc. in the Sub
form.

The form is based on a query that includes these fields from their
respective tables: Family table and Contact table, that are joined by
FamilyID PK autonumber to FamilyID FK number.

It worked fine and showed only the contact rows (in datasheet) in the sub
form for the currently displayed family, plus I added an unbound control for
the user to select another family quickly.

The problem is: It doesn't show the families who have no contact info as of
yet. This is of no use, as I need to see ALL families to enter initial
contact data or edit existing. I changed the join type to number 2 in the
query so that it displays all families, even those without current contact
info, but that caused two more problems:

1)The datasheet shows ALL contacts, not just those for the family displayed
in the main form. 2) When I try to add new initial contact information for
a family, it won't allow it and I get an error message.

HELP!!!!!!!!!!

 

[Sandra Daigle]

Dee,

The main form should be based strictly on the Family table. There is no need
for a join in this query since the related data is shown in the subform.

The subform should be based strictly on the contacts table. Then the linking
fields of the subform control should be FamilyId.

 

[dee]

Hi Sandra and thanks for your help.

I am trying what you suggested, but at the risk of sounding more ignorant
than I already do, I have no idea how to make the linking field of the
subfields the FamilyID. Do you mean I go into the control's properties and
set it?

I appreciate your patience with a newbie.
--
Thanks!

Dee

Dee,

The main form should be based strictly on the Family table. There is no need
for a join in this query since the related data is shown in the subform.

The subform should be based strictly on the contacts table. Then the linking
fields of the subform control should be FamilyId.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi,

I'm new and having tons of problems. The latest:

I managed to create a form and sub form that shows FamilyID and
FamilyName in the Main form and ContactLast, ContactFirst, Tel, Cell,
etc. in the Sub form.

The form is based on a query that includes these fields from their
respective tables: Family table and Contact table, that are joined by
FamilyID PK autonumber to FamilyID FK number.

It worked fine and showed only the contact rows (in datasheet) in the
sub form for the currently displayed family, plus I added an unbound
control for the user to select another family quickly.

The problem is: It doesn't show the families who have no contact
info as of yet. This is of no use, as I need to see ALL families to
enter initial contact data or edit existing. I changed the join type
to number 2 in the query so that it displays all families, even those
without current contact info, but that caused two more problems:

1)The datasheet shows ALL contacts, not just those for the family
displayed in the main form. 2) When I try to add new initial
contact information for a family, it won't allow it and I get an
error message.

HELP!!!!!!!!!!

 

[Sandra Daigle]

No problem!

Select the subform control - do this by click once on the subform - you
should see "handles" around the subform and the title of the property sheet
should be prefixed with "Subform/SubReport".

Now look under the 'Data' tab of the property sheet. There you will see two
properties: "Link Child Fields" and "Link Master Fields". You should select
FamilyId in both of these properties.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi Sandra and thanks for your help.

I am trying what you suggested, but at the risk of sounding more
ignorant than I already do, I have no idea how to make the linking
field of the subfields the FamilyID. Do you mean I go into the
control's properties and set it?

I appreciate your patience with a newbie.

Dee,

The main form should be based strictly on the Family table. There is
no need for a join in this query since the related data is shown in
the subform.

The subform should be based strictly on the contacts table. Then the
linking fields of the subform control should be FamilyId.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi,

I'm new and having tons of problems. The latest:

I managed to create a form and sub form that shows FamilyID and
FamilyName in the Main form and ContactLast, ContactFirst, Tel,
Cell, etc. in the Sub form.

The form is based on a query that includes these fields from their
respective tables: Family table and Contact table, that are joined
by FamilyID PK autonumber to FamilyID FK number.

It worked fine and showed only the contact rows (in datasheet) in
the sub form for the currently displayed family, plus I added an
unbound control for the user to select another family quickly.

The problem is: It doesn't show the families who have no contact
info as of yet. This is of no use, as I need to see ALL families to
enter initial contact data or edit existing. I changed the join
type to number 2 in the query so that it displays all families,
even those without current contact info, but that caused two more
problems:

1)The datasheet shows ALL contacts, not just those for the family
displayed in the main form. 2) When I try to add new initial
contact information for a family, it won't allow it and I get an
error message.

HELP!!!!!!!!!!

 

[dee]

Thanks so much. I'd been hunting high and low. I tried what you said.
Having a query that contains ContactID, ContactLast, ContactFirst, etc.

Then create a form with the FamilyName from the Family table, but get a
message saying that I am using fields from record sources that can't be
connect. You may have chosen fields from a table and from a query based on
that table.

Is it because I have included FamilyID as a FK in the Contact table from the
FamilyID PK in the Family table? I thought I had to to create a
relationship.

Help!!! And Thanks!!!

--
Thanks!

Dee

No problem!

Select the subform control - do this by click once on the subform - you
should see "handles" around the subform and the title of the property sheet
should be prefixed with "Subform/SubReport".

Now look under the 'Data' tab of the property sheet. There you will see two
properties: "Link Child Fields" and "Link Master Fields". You should select
FamilyId in both of these properties.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi Sandra and thanks for your help.

I am trying what you suggested, but at the risk of sounding more
ignorant than I already do, I have no idea how to make the linking
field of the subfields the FamilyID. Do you mean I go into the
control's properties and set it?

I appreciate your patience with a newbie.

Dee,

The main form should be based strictly on the Family table. There is
no need for a join in this query since the related data is shown in
the subform.

The subform should be based strictly on the contacts table. Then the
linking fields of the subform control should be FamilyId.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


dee wrote:
Hi,

I'm new and having tons of problems. The latest:

I managed to create a form and sub form that shows FamilyID and
FamilyName in the Main form and ContactLast, ContactFirst, Tel,
Cell, etc. in the Sub form.

The form is based on a query that includes these fields from their
respective tables: Family table and Contact table, that are joined
by FamilyID PK autonumber to FamilyID FK number.

It worked fine and showed only the contact rows (in datasheet) in
the sub form for the currently displayed family, plus I added an
unbound control for the user to select another family quickly.

The problem is: It doesn't show the families who have no contact
info as of yet. This is of no use, as I need to see ALL families to
enter initial contact data or edit existing. I changed the join
type to number 2 in the query so that it displays all families,
even those without current contact info, but that caused two more
problems:

1)The datasheet shows ALL contacts, not just those for the family
displayed in the main form. 2) When I try to add new initial
contact information for a family, it won't allow it and I get an
error message.

HELP!!!!!!!!!!

 

[Sandra Daigle]

Familyid as a FK field must be included in the recordsource for the
subform's form and FamilyId as a PK field must be included in the
recordsource for the main form.

In the tables, the fields must be the same datatype (normally long for an ID
field).

You can (and should) use relationships (Tools->Relationships in the database
window) to a establish permanant relationship between the two tables based
on the FamilyId field. If you do this before you create the form and add the
subform, Access will put in the linking fields for you.

Does this help?

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thanks so much. I'd been hunting high and low. I tried what you
said. Having a query that contains ContactID, ContactLast,
ContactFirst, etc.

Then create a form with the FamilyName from the Family table, but get
a message saying that I am using fields from record sources that
can't be connect. You may have chosen fields from a table and from a
query based on that table.

Is it because I have included FamilyID as a FK in the Contact table
from the FamilyID PK in the Family table? I thought I had to to
create a relationship.

Help!!! And Thanks!!!

No problem!

Select the subform control - do this by click once on the subform -
you should see "handles" around the subform and the title of the
property sheet should be prefixed with "Subform/SubReport".

Now look under the 'Data' tab of the property sheet. There you will
see two properties: "Link Child Fields" and "Link Master Fields".
You should select FamilyId in both of these properties.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi Sandra and thanks for your help.

I am trying what you suggested, but at the risk of sounding more
ignorant than I already do, I have no idea how to make the linking
field of the subfields the FamilyID. Do you mean I go into the
control's properties and set it?

I appreciate your patience with a newbie.

Dee,

The main form should be based strictly on the Family table. There
is no need for a join in this query since the related data is
shown in the subform.

The subform should be based strictly on the contacts table. Then
the linking fields of the subform control should be FamilyId.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


dee wrote:
Hi,

I'm new and having tons of problems. The latest:

I managed to create a form and sub form that shows FamilyID and
FamilyName in the Main form and ContactLast, ContactFirst, Tel,
Cell, etc. in the Sub form.

The form is based on a query that includes these fields from their
respective tables: Family table and Contact table, that are joined
by FamilyID PK autonumber to FamilyID FK number.

It worked fine and showed only the contact rows (in datasheet) in
the sub form for the currently displayed family, plus I added an
unbound control for the user to select another family quickly.

The problem is: It doesn't show the families who have no contact
info as of yet. This is of no use, as I need to see ALL families
to enter initial contact data or edit existing. I changed the
join type to number 2 in the query so that it displays all
families, even those without current contact info, but that
caused two more problems:

1)The datasheet shows ALL contacts, not just those for the family
displayed in the main form. 2) When I try to add new initial
contact information for a family, it won't allow it and I get an
error message.

HELP!!!!!!!!!!

 

[dee]

Hi Sandra,

It's working!!! Thank you so much for your help. I actually had my tables
set up perfectly, just as you had suggested. I think my tables and their
relationships are just fine, it was the form creation that was stumping me.
I needed the family name sorted alphabetically in the main form, for the find
combo list control. So, I think I ended up adding the information to the
Contact query for the sub form, which is one place I went wrong.

I followed your instructions about the parent/child fields (I had seen this
in posts but hadn't been able to figure out where to find the property!),
basing the form on two separate queries; one for FamilyID and FamilyName and
one for the Contact info.

Thanks so very, very much. Now I will try to put the same ideas to use in
the creation of my other forms.


--
Thanks!

Dee

Familyid as a FK field must be included in the recordsource for the
subform's form and FamilyId as a PK field must be included in the
recordsource for the main form.

In the tables, the fields must be the same datatype (normally long for an ID
field).

You can (and should) use relationships (Tools->Relationships in the database
window) to a establish permanant relationship between the two tables based
on the FamilyId field. If you do this before you create the form and add the
subform, Access will put in the linking fields for you.

Does this help?

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thanks so much. I'd been hunting high and low. I tried what you
said. Having a query that contains ContactID, ContactLast,
ContactFirst, etc.

Then create a form with the FamilyName from the Family table, but get
a message saying that I am using fields from record sources that
can't be connect. You may have chosen fields from a table and from a
query based on that table.

Is it because I have included FamilyID as a FK in the Contact table
from the FamilyID PK in the Family table? I thought I had to to
create a relationship.

Help!!! And Thanks!!!

No problem!

Select the subform control - do this by click once on the subform -
you should see "handles" around the subform and the title of the
property sheet should be prefixed with "Subform/SubReport".

Now look under the 'Data' tab of the property sheet. There you will
see two properties: "Link Child Fields" and "Link Master Fields".
You should select FamilyId in both of these properties.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


dee wrote:
Hi Sandra and thanks for your help.

I am trying what you suggested, but at the risk of sounding more
ignorant than I already do, I have no idea how to make the linking
field of the subfields the FamilyID. Do you mean I go into the
control's properties and set it?

I appreciate your patience with a newbie.

Dee,

The main form should be based strictly on the Family table. There
is no need for a join in this query since the related data is
shown in the subform.

The subform should be based strictly on the contacts table. Then
the linking fields of the subform control should be FamilyId.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


dee wrote:
Hi,

I'm new and having tons of problems. The latest:

I managed to create a form and sub form that shows FamilyID and
FamilyName in the Main form and ContactLast, ContactFirst, Tel,
Cell, etc. in the Sub form.

The form is based on a query that includes these fields from their
respective tables: Family table and Contact table, that are joined
by FamilyID PK autonumber to FamilyID FK number.

It worked fine and showed only the contact rows (in datasheet) in
the sub form for the currently displayed family, plus I added an
unbound control for the user to select another family quickly.

The problem is: It doesn't show the families who have no contact
info as of yet. This is of no use, as I need to see ALL families
to enter initial contact data or edit existing. I changed the
join type to number 2 in the query so that it displays all
families, even those without current contact info, but that
caused two more problems:

1)The datasheet shows ALL contacts, not just those for the family
displayed in the main form. 2) When I try to add new initial
contact information for a family, it won't allow it and I get an
error message.

HELP!!!!!!!!!!

 

[dee]

Hi again Sandra,

I have another form problem and was wondering if you could help me with it.
I have another table called Meetings. It contains fields such as FamilyID FK
from the Family table, MeetingID PK autonumber, MeetingDate, Parent1,
Parent2, Student1, Student2, Student3, Student4 and Notes. Meetings are held
with the parents and up to four of their children.

The main form is based on the FamilyName and FamilyID query, the same one I
used in the form you helped me with. The sub-form is based on another query
that contains the FamilyID, and the fields from the Meetings table, as
described above.

What I want to do, though, is populate the fields in the Meetings table, via
the form, but looking up the parent name and student name choices from other
tables in a combo box. The student names are in StudentInfo table and the
parent names are in the Contacts table. When I create a combo box, it shows
the names of ALL parents and ALL students, not just those related to the
family.

Any suggestions? Thanks so much and thanks for your patience.

--
Thanks!

Dee

Familyid as a FK field must be included in the recordsource for the
subform's form and FamilyId as a PK field must be included in the
recordsource for the main form.

In the tables, the fields must be the same datatype (normally long for an ID
field).

You can (and should) use relationships (Tools->Relationships in the database
window) to a establish permanant relationship between the two tables based
on the FamilyId field. If you do this before you create the form and add the
subform, Access will put in the linking fields for you.

Does this help?

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thanks so much. I'd been hunting high and low. I tried what you
said. Having a query that contains ContactID, ContactLast,
ContactFirst, etc.

Then create a form with the FamilyName from the Family table, but get
a message saying that I am using fields from record sources that
can't be connect. You may have chosen fields from a table and from a
query based on that table.

Is it because I have included FamilyID as a FK in the Contact table
from the FamilyID PK in the Family table? I thought I had to to
create a relationship.

Help!!! And Thanks!!!

No problem!

Select the subform control - do this by click once on the subform -
you should see "handles" around the subform and the title of the
property sheet should be prefixed with "Subform/SubReport".

Now look under the 'Data' tab of the property sheet. There you will
see two properties: "Link Child Fields" and "Link Master Fields".
You should select FamilyId in both of these properties.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


dee wrote:
Hi Sandra and thanks for your help.

I am trying what you suggested, but at the risk of sounding more
ignorant than I already do, I have no idea how to make the linking
field of the subfields the FamilyID. Do you mean I go into the
control's properties and set it?

I appreciate your patience with a newbie.

Dee,

The main form should be based strictly on the Family table. There
is no need for a join in this query since the related data is
shown in the subform.

The subform should be based strictly on the contacts table. Then
the linking fields of the subform control should be FamilyId.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


dee wrote:
Hi,

I'm new and having tons of problems. The latest:

I managed to create a form and sub form that shows FamilyID and
FamilyName in the Main form and ContactLast, ContactFirst, Tel,
Cell, etc. in the Sub form.

The form is based on a query that includes these fields from their
respective tables: Family table and Contact table, that are joined
by FamilyID PK autonumber to FamilyID FK number.

It worked fine and showed only the contact rows (in datasheet) in
the sub form for the currently displayed family, plus I added an
unbound control for the user to select another family quickly.

The problem is: It doesn't show the families who have no contact
info as of yet. This is of no use, as I need to see ALL families
to enter initial contact data or edit existing. I changed the
join type to number 2 in the query so that it displays all
families, even those without current contact info, but that
caused two more problems:

1)The datasheet shows ALL contacts, not just those for the family
displayed in the main form. 2) When I try to add new initial
contact information for a family, it won't allow it and I get an
error message.

HELP!!!!!!!!!!

 

[Sandra Daigle]

You need to use a Where clause that filters the valuse to only those
matching FamilyId on the form. Use this on each of the combo rowsource
queries.

If your form is named frmFamily then in the query designer find the FamilyId
column and then add the following in the Criteria row under FamilyId:

=forms!frmFamily!familyId

Then you also need to create a Current event for the form which requeries
each of the combo boxes.

Now, one other thing that you need to consider is that your table Meetings
is not normalized. Anytime you have columns named Something1,
Something2...Something(n) you need create a new table - MeetingAttendees.

Also, You've got a table for Contacts (parents) and another table for
Students. These two tables probably have a great deal in common and probably
could be combined into a single table named People (or something along those
lines) with an additional column that categorizes the individual as a
parent, student, teacher? Where this gets interesting is that you then also
need a way to describe relationships between individuals. I generally have
(yet another) table named Family and then one named FamilyMembers and each
Family member is assigned a role (parent, child).

Do some reading on Normalization for more information on why you need to
make these changes. The first one is critical - the second also important
but maybe not quite as critical. I will say this though, if the database is
going to be around for a long time, it's worth the effort to get it all
right up front.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi again Sandra,

I have another form problem and was wondering if you could help me
with it. I have another table called Meetings. It contains fields
such as FamilyID FK from the Family table, MeetingID PK autonumber,
MeetingDate, Parent1, Parent2, Student1, Student2, Student3, Student4
and Notes. Meetings are held with the parents and up to four of
their children.

The main form is based on the FamilyName and FamilyID query, the same
one I used in the form you helped me with. The sub-form is based on
another query that contains the FamilyID, and the fields from the
Meetings table, as described above.

What I want to do, though, is populate the fields in the Meetings
table, via the form, but looking up the parent name and student name
choices from other tables in a combo box. The student names are in
StudentInfo table and the parent names are in the Contacts table.
When I create a combo box, it shows the names of ALL parents and ALL
students, not just those related to the family.

Any suggestions? Thanks so much and thanks for your patience.

Familyid as a FK field must be included in the recordsource for the
subform's form and FamilyId as a PK field must be included in the
recordsource for the main form.

In the tables, the fields must be the same datatype (normally long
for an ID field).

You can (and should) use relationships (Tools->Relationships in the
database window) to a establish permanant relationship between the
two tables based on the FamilyId field. If you do this before you
create the form and add the subform, Access will put in the linking
fields for you.

Does this help?

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thanks so much. I'd been hunting high and low. I tried what you
said. Having a query that contains ContactID, ContactLast,
ContactFirst, etc.

Then create a form with the FamilyName from the Family table, but
get a message saying that I am using fields from record sources that
can't be connect. You may have chosen fields from a table and from
a query based on that table.

Is it because I have included FamilyID as a FK in the Contact table
from the FamilyID PK in the Family table? I thought I had to to
create a relationship.

Help!!! And Thanks!!!


No problem!

Select the subform control - do this by click once on the subform
- you should see "handles" around the subform and the title of the
property sheet should be prefixed with "Subform/SubReport".

Now look under the 'Data' tab of the property sheet. There you will
see two properties: "Link Child Fields" and "Link Master Fields".
You should select FamilyId in both of these properties.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


dee wrote:
Hi Sandra and thanks for your help.

I am trying what you suggested, but at the risk of sounding more
ignorant than I already do, I have no idea how to make the linking
field of the subfields the FamilyID. Do you mean I go into the
control's properties and set it?

I appreciate your patience with a newbie.

Dee,

The main form should be based strictly on the Family table. There
is no need for a join in this query since the related data is
shown in the subform.

The subform should be based strictly on the contacts table. Then
the linking fields of the subform control should be FamilyId.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


dee wrote:
Hi,

I'm new and having tons of problems. The latest:

I managed to create a form and sub form that shows FamilyID and
FamilyName in the Main form and ContactLast, ContactFirst, Tel,
Cell, etc. in the Sub form.

The form is based on a query that includes these fields from
their respective tables: Family table and Contact table, that
are joined by FamilyID PK autonumber to FamilyID FK number.

It worked fine and showed only the contact rows (in datasheet)
in the sub form for the currently displayed family, plus I
added an unbound control for the user to select another family
quickly.

The problem is: It doesn't show the families who have no
contact info as of yet. This is of no use, as I need to see
ALL families to enter initial contact data or edit existing. I
changed the join type to number 2 in the query so that it
displays all families, even those without current contact info,
but that caused two more problems:

1)The datasheet shows ALL contacts, not just those for the
family displayed in the main form. 2) When I try to add new
initial contact information for a family, it won't allow it and
I get an error message.

HELP!!!!!!!!!!