create append query resulting in multiple new records

[8l2255]

I have a new transaction form. It has fields TransactionID (autonumber),
ProductID, EquipmentID, UnitsReceived and a few more. When I create a new
transaction record I would like the TransactionID, ProductID and EquipmentID
to append new records to the amount of UnitsReceived in my Stock table.

Is it possible for an append query to create multiple new records using
information in the new transaction record and is it possible for the number
of records to correspond with the value entered for UnitsReceived?

 

[TedMi]

It is possible by using an INSERT statement in a loop in the AfterInsert
event of the transaction form. The numer of iterations of the loop would be
the value of UnitsReceived. But why?
Do you really want to have a separate record for each received item?
Wouldn't it suffice to have a single record in the Stock Table, with a field
holding the OnHand quantity? Then, the AfteInsert event of the transaction
can merely update the appropriate record in the stock table:
UPDATE StockTable SET OnHandQ = OnHandQ+Me.UnitsReceived
WHERE ProductID=Me.ProductID AND EquipmentID=Me.EquipmentID

(assuming that the Stock table has fields ProductID and EquipmentID as
unique compound key)

-TedMi

 

[KARL DEWEY]

You can use a Cartesian effect append query.
Creacte a table named CountNumber with number field name CountNUM containing
0 (zero) through your maximum.
Create the append query, add the CountNumber table, drag the CountNUM field
to the FIELD row, add criteria --
Between 1 AND [Forms]![YourFormName]![UnitsReceived]

 

[8l2255]

@Tedmi
Each individual item has a transfer location which needs to be recorded. As
I am VERY new to access I am positive there is a better way but I am not sure
how.
@Karl Dewey
hmmm. I am still having trouble with this. am focussing on another problem.

thanks for your help.

You can use a Cartesian effect append query.
Creacte a table named CountNumber with number field name CountNUM containing
0 (zero) through your maximum.
Create the append query, add the CountNumber table, drag the CountNUM field
to the FIELD row, add criteria --
Between 1 AND [Forms]![YourFormName]![UnitsReceived]

--
Build a little, test a little.

I have a new transaction form. It has fields TransactionID (autonumber),
ProductID, EquipmentID, UnitsReceived and a few more. When I create a new
transaction record I would like the TransactionID, ProductID and EquipmentID
to append new records to the amount of UnitsReceived in my Stock table.

Is it possible for an append query to create multiple new records using
information in the new transaction record and is it possible for the number
of records to correspond with the value entered for UnitsReceived?

 

[8l2255]

I finally came back to this today,

I created a form with
CategoryID
ProductID
Date_Acquired
StatusID
and a qty box called txtN

When i click create_lot it appends txtN amount of item records with info as
filled out in the form to my itemtbl.


Private Sub Create_Lot_Click()

Dim db As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("itemtbl")
With rs
For a = 1 To TxtN
..AddNew
!ProductID = ProductID
!CategoryID = CategoryID
!StatusID = 1
!Date_Aquired = Date_Aquired
..Update
Next a
End With
rs.close

End Sub