database subforms

[Simon]

I will try and explain what i want to do.
I am building a datbase for manufactuing hot tubs

i have the following tables
tblHotTubs Containers - HotTubID, and ProductName and price
tblProducts Contains the products need to build tub - ProductID,

Product name, Price
tblParts Contains all the parts for each hot tubs -
HotTubID , ProductID, Quantity
tblManufacture Contins the date it was made and HotTubID -
ManufactuerID, Date, HotTubID
tblPartsUsed Contain all the parts used in builidn hot tub -
ManufactureID, ProductID, Quantity, Serials Number


I have a have a form (frmManufactuerTub) where i have a autonumber for
maufactuer ID, i also have a datasheet subform frmPArtsUsed)


what i would like to do is on the main form when i select the hot tub
code what is made it puts all the parts for that hot tub which are
found in tblParts into the subform frmPArtsUsed (tblPArtsUsed)


then once all the intem are in the suform i can add the serial number
of any of the products and can all increase and decreas the products
uses if the hot tub needs to be speceted up or down.


Hope this is clean, if you need any other info just ask, i am just so
struck on how to go about this


Regrads


Simon

 

[Steve Schapel]

Simon,

You could make a query, which includes the tblParts and tblManufacture
tables, joined on the HotTubID field, such that this returns the Parts
data for the hot tub entered in the current frmManufactuerTub record
(use criteria to refer to this combobox, using syntax such as
[Forms]![frmManufactuerTub]![HotTubID]).
THen make this into an Append Query, and nominate tblPartsUsed as the
table to be appended to. Then you can run this query on the Click event
of your command button, or the After Update event of your combobox, and
I think this will do what you asked. In your code, you will probably
need to Requery the subform toget the newly appended parts records to be
displayed.

 

[Jesse]

If I understand you correctly, what you want is that after you select
your HotTubID, the subform gets populated with all the possible parts
needed to build it. You can do it by using code. After you select the
HotTubID, code will obtain a recordset with the needed parts. You can
then walk the recordset to programatically add the necessary rows to
your subform.