Stock Transferring: Working through Subform

  • Thread starter Thread starter Will
  • Start date Start date
W

Will

I have a form and a subform based on the tables, tblStockTransfer (ID,
TransferNo, TransferDate, WarehouseFrom, WarehouseTo, TransferredBy,
Transferred) and tblStockTransferDetails (StockCode, Quantity, BatchNo) ,one
to many relationship. I have a transfer button on the main form which
actions the transfer but this only does the current selected record in
tblStockTransferDetails. How can I get it to loop through all the records
in the subform of tblStockTransferDetails? and is this the best way of
doing stock transfers?

thanks in advance
 
I usually use a Do Loop and move through a recordset until the end of the
file Something like (aircode):

Dim rst As DAO.Recordset
Dim rstTrans As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDB

Set rst = db.OpenRecordset("Select * From tblStockTransferDetails Where ID
=" & Me.txtID, dbOpenSnapshot)
Set rstTrans = db.OpenRecordset("The table being transferred to",
dbOpenDynaset)

rst.MoveFirst

Do Until rst.EOF

With rstTrans
.AddNew
.Field1 = rstField1
.Field2 = rst.Field2
' etc.
.Update
End With

rst.MoveNext
Loop

Close the recordsets and set everything to Nothing, add some error handling
and as the Aussies's say: "Bob's your uncle"
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top