Data copy

[Kurtb4 via AccessMonster.com]

Hello,
I need help transferring data into an tblinventory table from a tblorder
table.

In the table 'tblorder' I have an order pending of quantity '50' of
description "Abc" and partNo "123". After the parts are manufactured I would
like to transfer them into a table 'tblInventory' and assign serial numbers.

So far I have created a form and a command button with the underlying code:

Dim lSerial As Long
For lSerial = me.txtFirstSerial To Me.txtLastSerial
CurrentDB.Execute "INSERT INTO tblInventory (SerialNumber) VALUES (" &
lSerial &")"
Next

My problem is that I cannot figure out how to also transfer the description
and the part number as well. Perhaps there is a way that I did not think
about. Any help will be appreciated.
Regards,
Kurt Boettcher

 

[John W. Vinson]

Hello,
I need help transferring data into an tblinventory table from a tblorder
table.

In the table 'tblorder' I have an order pending of quantity '50' of
description "Abc" and partNo "123". After the parts are manufactured I would
like to transfer them into a table 'tblInventory' and assign serial numbers.

So far I have created a form and a command button with the underlying code:

Dim lSerial As Long
For lSerial = me.txtFirstSerial To Me.txtLastSerial
CurrentDB.Execute "INSERT INTO tblInventory (SerialNumber) VALUES (" &
lSerial &")"
Next

My problem is that I cannot figure out how to also transfer the description
and the part number as well. Perhaps there is a way that I did not think
about. Any help will be appreciated.
Regards,
Kurt Boettcher

An APPEND query based on tblOrder will work. I presume you want to create
fifty records? Are txtFirstSerial and txtLastSerial manually entered? Do you
have some precautions (such as a unique Index) to ensure that you don't get
duplicates?

You may want to take advantage of a handy little auxiliary table. Create a
table named Num with one field, N, with values from 0 through 10000 or so (use
more than you will ever need for quantity). Assuming that you have the OrderID
displayed on your form in a textbox txtOrderID, you should be able to use
something like

INSERT INTO tblInventory (SerialNumber, Description, PartNo)
SELECT [Forms]![yourformname]![txtFirstSerial] + N
FROM tblOrders, Num
WHERE tblOrders.OrderID = Forms![yourformname]![txtOrderID]
AND Num.N < tblOrders.Quantity

You don't need the end serial number - this will generate [quantity] new
records with sequential serial numbers starting with the chosen FirstSerial.

John W. Vinson [MVP]