Grouping Records in a Form

[dancox via AccessMonster.com]

I created a form and subform to write to my Material Takeoff Table.

Fields are ISOID, MaterialID, POID, Quantity, etc. ISOID exists more than
once in the Material Takeoff table. but no more than 1 material ID per ISOID.

If I create a form based on the ISO table and sub form based on the table
MaterialTakeoff, the Material ID's group by ISO. However, to add a new ISO to
the Material Takeoff form, the main form is trying to write back to the ISO
table. (I understand that the Main Form control source is ISO table not the
Material Takeoff table for write purposes).

How can I group the Material ID's by ISO number and write to the Material
Takeoff Table? End result, I want the user to be able to click new, enter
ISO# once, and multiple Material ID's under that ISO.


Thank you,

Dan

 

[Jeanette Cunningham]

Hi Dan,
I just created 2 tables, 1 for ISO and 1 for Materials as you described.
I created an auto form using the ISO table and another auto form using the
Materials table.
I opened frmMaterials in design view and set its Default View property to
continuous forms (form's property sheet, format tab).
I closed frmMaterials and opened frmISO in design view.
I chose the subform tool from the toolbox and put a subform on frmISO using
the wizard.
I followed the prompts till finished.
When I opened frmISO and entered an ISO number, I could do multiple entries
in frmMaterials for that ISO number.

At the moment I don't know why yours is not working.
For a start - how do you have the relationships set up between all your
tables?
Would you like to post your tables with their name, primary key, foreign key
and how they are related.

Jeanette Cunningham

 

[dancox via AccessMonster.com]

I have 3 tables involved.
tblISONumbers tblMaterials tblMaterialTakeoff
PK IDISO PK IDMaterial PK IDTakeoff
IDMaterial
IDISO
MaterialID's from the Material table and ISOID's from the ISO Table are both
linked via 1 to many relationship and with referential integrity enforced to
a 3rd table called Material Takeoff. The record source is the Material
Takeoff table using values from ISO table and Material takeoff. (ISO is a
drawing and we have about 1,400 drawings).

When we estimate, we go ISO by ISO and each ISO has its own Bill of Materials.
So we may have 1 or more materials from the Material table on each ISO.
I appreciate the help.

Hi Dan,
I just created 2 tables, 1 for ISO and 1 for Materials as you described.
I created an auto form using the ISO table and another auto form using the
Materials table.
I opened frmMaterials in design view and set its Default View property to
continuous forms (form's property sheet, format tab).
I closed frmMaterials and opened frmISO in design view.
I chose the subform tool from the toolbox and put a subform on frmISO using
the wizard.
I followed the prompts till finished.
When I opened frmISO and entered an ISO number, I could do multiple entries
in frmMaterials for that ISO number.

At the moment I don't know why yours is not working.
For a start - how do you have the relationships set up between all your
tables?
Would you like to post your tables with their name, primary key, foreign key
and how they are related.

Jeanette Cunningham

I created a form and subform to write to my Material Takeoff Table.

[quoted text clipped - 19 lines]

Dan

 

[Jeanette Cunningham]

Dan,
Keep the main form based on tblISO

Use auto form to create a form based on tblMaterialTakeoff

Call it frmMaterialTakeoff



frmMaterialTakeoff

--continuous view

--a textbox for quantity

--a combobox called cboMaterial

--cboMaterail has rowsource

SELECT tblMaterial.MaterialID, tblMaterial.MaterialText FROM tblMaterial
ORDER BY tblMaterial.MaterialText;

MaterialText is a description of the material type



Back on formISO

--change the subform's source object to frmMaterialTakeoff

--set Link master and child fields to IDISO



Open frmISO

You can now enter a new ISO

Choose the materialID and enter the quantity.


Jeanette Cunningham