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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200801/1

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" <u40267@uwe> wrote in message 
news:7db124e365328@uwe...
>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
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200801/1
> 

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.

Jeanette Cunningham wrote:
>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

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

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

"dancox via AccessMonster.com" <u40267@uwe> wrote in message 
news:7db62316ec162@uwe...
>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.