If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
create append query resulting in multiple new records
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? |
#2
|
|||
|
|||
create append query resulting in multiple new records
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 "8l2255" wrote in message ... 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? |
#3
|
|||
|
|||
create append query resulting in multiple new records
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. "8l2255" wrote: 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? |
#4
|
|||
|
|||
create append query resulting in multiple new records
@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. "KARL DEWEY" wrote: 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. "8l2255" wrote: 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? |
#5
|
|||
|
|||
create append query resulting in multiple new records
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 "TedMi" wrote: 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 "8l2255" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|