Fields in Main Form to Populate Records in Child Form

I am working with a local non-profit that works with Seniors and am trying to
revamp their database. I have created several tabs for each type of event
the volunteers or receipients may particiapte in (Education, Travel,
Donations, Committees, Teacher, etc.). I want to be able to create the input
in the main form where they can input there, click ADD and have it populate
the child form that is in a datasheet view. I want to make the child form
read-only so the volunteers that input the data can't accidentally overwrite
any existing data.

My thought was to create a "holding" table that would contain the fields in
the child form/table and use it to run an append query to populate the linked
table/form. Then, I would have the child form refresh to show the new data
and clear to holding table. Not sure if I am heading in the right direction
with this. I would have to create 10-12 of these for each tab for each event
that is tracked.

Any help would be greatly appreciated! Thanks!

You are starting your discussion by describing the interface rather than the
underlying structure. It all starts with the tables and relationships.
Generally speaking you would probably need a table for People, a table of
Events, and a junction table to connect the two (one person:many events; one
event:many persons).

tblPeople
PersonID
LastName
FirstName
etc.

tblEvent
EventID
Description
etc.

tblPeopleEvent
PeopleEventID
PeopleID
EventID
etc.

The point is that each table contains information about an entity, with the
attributes of that entity. LastName is an attribute of a person, but events
in which a person participates are not. Copying data from one table to
another is almost certainly not the best way to proceed.

Perhaps if you describe the needs in greater detail it would be possible to
come up with some specific suggestions. In particular, when you mention tabs
are you thinking of a tab for each possible Event, or tabs only for the
events in which a person participates, or what exactly? I realize this is an
interface question rather than a structure question, but the answer may help
illustrate your needs.

KBFour wrote:
I am working with a local non-profit that works with Seniors and am trying to
revamp their database. I have created several tabs for each type of event
the volunteers or receipients may particiapte in (Education, Travel,
Donations, Committees, Teacher, etc.). I want to be able to create the input
in the main form where they can input there, click ADD and have it populate
the child form that is in a datasheet view. I want to make the child form
read-only so the volunteers that input the data can't accidentally overwrite
any existing data.

My thought was to create a "holding" table that would contain the fields in
the child form/table and use it to run an append query to populate the linked
table/form. Then, I would have the child form refresh to show the new data
and clear to holding table. Not sure if I am heading in the right direction
with this. I would have to create 10-12 of these for each tab for each event
that is tracked.

Any help would be greatly appreciated! Thanks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201001/1

Yes, I have all the table structure. As an example of the one tab I am
working on now (Committees), tables and relationships are as follows:

tblClient (main table with basic client info)
ClientID
Client Name, Address, etc.
tblCommittee (one to many with tblClient with client committee membership
data)
ClientID
CommitteeName
YearStarted
YearEnded

tblCommitteeID (includes committee name with 1 to many to tblCommittee)
CommitteeID

CommitteeID links to Committee links to Client. The client info is in the
main form with a tab for each event the client may participate in with that
event information in a child form in a datasheet view. Committee is the one
I am working with now, but they will include giving, education, travel, etc.
Each will be unique information linked back to the Client table in a similar
fashion as outlined above for committee.

What I am trying to do is create unbounded text boxes in the tab as part of
the main form that correspond to each field value in the child form (in this
case Committee Name, Date Started, Date Ended) so that when the data is added
to the unbounded boxes and an "Add Data" button is selected, I can run so
kind of append update to add the data to tblCommittee and clear the text
boxes that held the entered data.

This probably has to be done in SQL, but I am pretty much a newbis in that
area. I tried to run it using an append query, but it would do nothing.

Thanks!

"BruceM via AccessMonster.com" wrote:

You are starting your discussion by describing the interface rather than the
underlying structure. It all starts with the tables and relationships.
Generally speaking you would probably need a table for People, a table of
Events, and a junction table to connect the two (one person:many events; one
event:many persons).

tblPeople
PersonID
LastName
FirstName
etc.

tblEvent
EventID
Description
etc.

tblPeopleEvent
PeopleEventID
PeopleID
EventID
etc.

The point is that each table contains information about an entity, with the
attributes of that entity. LastName is an attribute of a person, but events
in which a person participates are not. Copying data from one table to
another is almost certainly not the best way to proceed.

Perhaps if you describe the needs in greater detail it would be possible to
come up with some specific suggestions. In particular, when you mention tabs
are you thinking of a tab for each possible Event, or tabs only for the
events in which a person participates, or what exactly? I realize this is an
interface question rather than a structure question, but the answer may help
illustrate your needs.

KBFour wrote:
I am working with a local non-profit that works with Seniors and am trying to
revamp their database. I have created several tabs for each type of event
the volunteers or receipients may particiapte in (Education, Travel,
Donations, Committees, Teacher, etc.). I want to be able to create the input
in the main form where they can input there, click ADD and have it populate
the child form that is in a datasheet view. I want to make the child form
read-only so the volunteers that input the data can't accidentally overwrite
any existing data.

My thought was to create a "holding" table that would contain the fields in
the child form/table and use it to run an append query to populate the linked
table/form. Then, I would have the child form refresh to show the new data
and clear to holding table. Not sure if I am heading in the right direction
with this. I would have to create 10-12 of these for each tab for each event
that is tracked.

Any help would be greatly appreciated! Thanks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201001/1

.

If one Client may be part of several committees, and each committee may
include several clients, you need something like this for the table structu


tblClient
ClientID (primary key, or PK)
FirstName
LastName
etc.

tblCommittee
CommitteeID (PK)
CommitteeName
etc.

tblClientCommittee
CC_ID (PK)
ClientID
CommitteeID
YearStarted
YearEnded

The PK fields are autonumber. They don't need to be autonumber, but they are
for purposes of this description. The rest of the ID fields are Number (Long
Integer). They are linking fields, sometimes knows as foreign keys. There
is a one-to-many relationship between the PK fields in the first two tables
and their like-named fields in tblClientCommittee.

Typically you would have a main form based on tblClient and a subform based
on tblClientCommittee. The subform would have a combo box bound to
CommitteeID. The Row Source for that combo box is a query based on
tblClientCommittee. The query has the CommitteeID field and the
CommitteeName field. The combo box has the following properties:
Bound Column - 1
Column Count - 2
Column Widths - 0";1.5" (or whatever you want for the second column)

The data in tblCommittee (Committee names) is presumably fairly static. That
is, committees are added infrequently after the intital list has been input.
If you need to add or change a committee you would do so by way of a separate
committee form.

You could use an unbound form for the subform, and update the recordset by
appending a record, but why not just bind the subform to the table?

In my original response I asked if you intend to have a tab for every
possible committee. In other words, if the person is on CommitteeA and
CommitteeB, you want two tabs, one for each committee. If the next person is
on Committees A, C, and D you want three tabs. Is that what you are going
for? If so, I'm afraid I don't know how to go about it. However, I do know
that you need a workable structure before you can work on the interface. I'm
not sure I understand your description of the structure, but it sounds as if
you have one table related to another, which is in turn related to a third.
Unless I completely misunderstand you, a structure such as I suggested in
both of my posts is probably what you need.

KBFour wrote:
Yes, I have all the table structure. As an example of the one tab I am
working on now (Committees), tables and relationships are as follows:

tblClient (main table with basic client info)
ClientID
Client Name, Address, etc.
tblCommittee (one to many with tblClient with client committee membership
data)
ClientID
CommitteeName
YearStarted
YearEnded

tblCommitteeID (includes committee name with 1 to many to tblCommittee)
CommitteeID

CommitteeID links to Committee links to Client. The client info is in the
main form with a tab for each event the client may participate in with that
event information in a child form in a datasheet view. Committee is the one
I am working with now, but they will include giving, education, travel, etc.
Each will be unique information linked back to the Client table in a similar
fashion as outlined above for committee.

What I am trying to do is create unbounded text boxes in the tab as part of
the main form that correspond to each field value in the child form (in this
case Committee Name, Date Started, Date Ended) so that when the data is added
to the unbounded boxes and an "Add Data" button is selected, I can run so
kind of append update to add the data to tblCommittee and clear the text
boxes that held the entered data.

This probably has to be done in SQL, but I am pretty much a newbis in that
area. I tried to run it using an append query, but it would do nothing.

Thanks!

You are starting your discussion by describing the interface rather than the
underlying structure. It all starts with the tables and relationships.
[quoted text clipped - 48 lines]

Any help would be greatly appreciated! Thanks!

--
Message posted via http://www.accessmonster.com