Subform based on query

[Ivor Williams]

This is a tough one for me, sorry if I get long winded.

I have a table tblProjects with key field ProjID. I have another table
tblEquip with key field EquipID. A third table tblPoints has a key field
PointID. A fourth table tblEquipPoints has key field EquipPointID and
includes lookup fields from tblEquip and tblPoints, so I can choose a piece
of equipment, then choose a point to associate with that piece of equipment.
I've created a form frmEquip with a subform sfrEquipPoints. The form and
subform are linked using EquipID. On the form I choose a piece of equipment,
and all the points for the chosen piece of equipment are displayed in the
subform as expected.

What I want to do is add a "top layer" to the form hierarchy so that I have
a form with a subform, and a subform within the subform. In the main form, I
would choose a project, in the subform I would choose a piece of equipment,
in the subform within the subform, all the points associated with the chosen
piece of equipment would be displayed.

I can make all this work fine, the problem I'm having is this. When all the
points for a piece of equipment are displayed, of course they are coming
from the tblPoints table which has no provision for entering a quantity, nor
should it have. On this subform, I want to enter a quantity next to the
point and have it saved with this piece of equipment on this project. I
would rather not have to choose each point individually, the best would be
if they are all displayed as they are now so none get missed. Not only that,
data entry would be less time consuming.

There are too many levels here, and I'm getting confused. Can someone point
me in the right direction, please.

Ivor

 

[Guest]

What is the QTY associate with? Is it the number of point for the equipment
item?

 

[Ivor Williams]

Qty can be associated with either pieces of equipment or points. Best would
be points.

 

[Guest]

Your answer did not help me understand your situation.

If the QTY is the number of point that a piece of equipment has then use A
TOTALS query to determine the QTY for each equipment.

Left join this query with the equipment table in your query that is the
record source for the equipment subform.

 

[Ivor Williams]

Sorry for not being clear.
A piece of equipment has many points, but not necessarily always the same
points. When I choose a piece of equipment in the main subform, I want all
the possible points for that type of equipment to be displayed in the
secondary subform. In the secondary subform I want to have a field to enter
(or edit) the quantity of any point in the list.
So, to summarize, this is what I'm trying to achieve:
On the main form, use a combo box to choose a project. (no problem)
On the main subform which is a subform of the main form, use a combo
box to choose an equipment type. (no problem)
Once the equipment type on the main subform is chosen, display in
the secondary subform all the possible points for the type of equipment
chosen in the main subform, with a field in the secondary subform for
entering or editing a quantity next to each point on the list. The quantity
field is where I have a problem. I can make all this work, except when I
base the secondary subform on a query, the quantity field is not editable,
nor can I enter a value if it is blank. If I run the query on which the
secondary subform is based, adding a new record is not possible. Hope this
helps

Ivor

 

[Amy Blankenship]

What you want to do needs some extra tables to relate equipment and points
to project. Even then, you'll need some pretty scary queries + code to make
it work. Unfortunately, Access does not deal very well with this type of
scenario natively.