Appending multiline data from a form

[haywam59]

Hi,

I have a database containing products and then a table showing the bill of materials and then a table showing batch numbers. So for example I could have Product 1 that is made from Material A, Material B and Material C. My Batch table shows that I am making Batch 1234 of Product 1. I'd like to be able to create a form that when I enter Batch 1234 it shows me what the ingredients are required in a datasheet style and allow me to enter the qty used and batch details of the materials used and update a fourth table of MaterialTransfers.

Can anyone advise the best way of doing this? I'm pretty good with access but this is blowing my mind a little. I built a query to link together the tables but due to the one-to-many links it comes out as an un-updateable recordset.

Any help or suggestions would be great.



Thanks
Mark

[ranman256]

form /subforms. (this is much like an online shopping cart)

creating the batch, you add a record, (by picking Product 1)
the subform would show (via query thru subform) all the items in Product 1.
Then when you move to a different record, the sub form shows THAT list.


(this would later, add all the materials, to the tBatchMaterial table, when youd click a 'build' button to assemble all materials via queries.)

[haywam59]

Hi Ranman,

Thanks, I've got the form and subform to show me the Products and ingredients. Its the last 'build' bit that I'm struggling with. Because I need to add details that don't exist in the Materials table, like batch number of the material etc but copy the ones that I do have like Product ID and Material ID. So If it shelps my tables are like this:

tblProducts: Product ID, Product Name, Product Description etc
tblBOM: ID, ProductID, MaterialCode, Qty
tblMaterials: MaterialCode, Material Description etc
tblProductTransfer: ID, BatchNum, ProductID, MaterialCode, QtyUsed, MaterialBatch etc

I can build the queries to link tblProducts, tblBOM and tblMaterials together. I'm stuck on how I show a form that displays the Product and materials required and have space to add the QtyUSed and Materials Batch and then add all of that to tblProductTransfer

form /subforms. (this is much like an online shopping cart)

creating the batch, you add a record, (by picking Product 1)
the subform would show (via query thru subform) all the items in Product 1.
Then when you move to a different record, the sub form shows THAT list.


(this would later, add all the materials, to the tBatchMaterial table, when youd click a 'build' button to assemble all materials via queries.)