Forms made from multiple tables

[Travis]

I've noticed that when a form is assembled from multiple tables, the
resulting form is read-only.

Its easy enough to get the information in to the various tables via
subforms, but I had an example where I wanted information from three
tables at once.

I'm just curious how I could go about creating a form which would
enable me to create an *editable* form that would enable me to tie
together the following information;

There are "people" and "companies" and "trusts" who are members of
"groups" (family groups). People, companies and trusts are all
"entities" listed in a single table of entities, one field of which
links them in their groups.

The various entities in groups all own assets, which are in an "assets"
table. Ownership of assets is tracked with a "portfolios" table which
contains the IDs of entities matched with assets, and the amounts
owned.

Its dead simple creating a portfolio subform inside an entity form so
you can add all of an entity's assets, and that form works fine.

But this means assets must be added to the asset table before bringing
up the portfolios table.

If a form is created with fields from the asset table, entities table
and the portfolios table all together all the information I want can be
on the one screen...

John Citizen owns one $300,000 house.
Jane Citizen owns one $50,000 car

The form I wanted to set out would have a combo box of all the
"entities" in the group, enabling me to nominate who owns the asset,
then a few fields for entering the details of the asset and how much is
owned.

If the assets have already been entered into the assets table its easy
to do the rest for one entity at a time via an entity form and
portfolio subform, but how would I do it all on one form? Creating a
form which has all of these fields displays all the information just as
I want to see it, but its not editable.

Travis

 

[Van T. Dinh]

Basically, the RecordSource of your Form is a Query or SQL String.
Depending on how the Tables are related in the Query / SQL String, (the data
returned by) the Query can be updatable or not. In general, you need to
relate them by the PK - FK relationship for the Quer / SQL String to be
updatable.

Check Access Help topic "Updatable Queries" which gives some technique to
convert an unupdatable Queries to updatable.

 

[Albert D.Kallal]

Why not still use sub-forms?

I mean, obviously, the list of assets can be a sub-form.

And, another sub-form can list "people", and allow you to have a combo box
that lets you "pick" from that above list of assists to assign to particular
person.

(on the other hand, why not have a combo box in a continues sub-form that
lists all the assets, and one column is a family member. In fact, since only
one family member can own a asset, then you only have a one to one
relationship here).

You "only" need a sub-form for each person and their assets (that you
assign) IF more then one person can have ownership in a asset.

So, at the end of the day, you might even use a nice tab control, and behind
one tab would list all the assets, and let you assign each asset to a
person.

You could also have a tab where you view a particular person, and the above
list of assets assigned to them could be shown. If there is "particular"
information about the asset that belongs to the particular "person", then I
would go with a table that is a "list" of assets assigned to a particular
individual. As I mentioned above if only ONE family member can own a asset,
then just add a extra column to the assets table to hold the family person
"id" that owns this assed. However, building a table of assets that belong
to a individual would be more flexible in that you can have more then one
person have ownership of a asset (say, a % ownership) Further, you could
more easily add history details to this table such as when the asset was
assigned to that individual (and, perhaps later given to someone else). So,
building a table called

tblAssetsAssignedToAPerson

Likely is a good idea (history..and also multiple ownership would be
allowed).

I see no help in building a form that lets you "edit" the data pulled
together as a single query, as that will be only ONE record. So, even if you
do obtain your quest to make the query updateable...of which child record
will you be editing? That query that is a result of multiple tables joined
will ONLY allow a form to edit ONE row from that complex query. So, your
problem is little, if any solved by somehow making your query updatable, as
the form will only allow one record to be edited at a time. Y still need a
means to assign "many" assets to one family member, and edit/display this
data in a nice fashion. With a query that is a result of multiple tables you
only see one record at a time..and I just see where you are going with this
request at all (you are barking up the wrong tree by wasting time to build a
updateable query).

Simply put...for each "many" list that you have..use a sub-form. You can
have 2, 3 or 15 sub-forms on a screen.....