How do I duplicate a record in VBA?

Question

I have a MS Access database in which I track instruments that we order. In this database we typically only order 1 instrument at a time. However, there are times in which the quantity of the instrument may change to 2 or 3 or maybe more. What I want to do is when I close my input form, have MS Access check the
quantity ordered. If the quantity is greater than say 3, I would like to have Access automatically duplicate the record, in this case 3 or more. I know there
must be an easy way to do this? Like the statement @If(QTY >3) then duplicate? or something like this? I am not that good at VBA, but if you can offer any input
on an easy way to do this, then I would appreciate it. I thought that I would just create an append query to re-add the last record in the table, but I am unsure on
how to do the quantity?

The purpose of the duplicate record is sometimes the factory may only ship 1 of the instruments and the remaining are on backorder. I want to duplicate
the records so I can track lead time.

Any help is appreciated. Thank you in advance.

Answer 1

I agree with Scott that a Received table would be the best way to handle this.  However, computing the items currently on back order would need a little more than a simple JOIN as you'd need to subtract the SUM of each item received per order from the quantity ordered.  This can be done by including a subquery in an outer query, e.g.

SELECT *, Quantity -

    (SELECT SUM(QuantityReceived)

      FROM Received

      WHERE QuantityReceived.OrderID = Orders.OrderID)

AS BackOrderQuantity

FROM Orders

WHERE Quantity -

    (SELECT SUM(QuantityReceived)

      FROM Received

      WHERE QuantityReceived.OrderID = Orders.OrderID) > 0;

Answer 2

The purpose of the duplicate record is sometimes the factory may only ship 1 of the instruments and the remaining are on backorder. I want to duplicate
the records so I can track lead time.

Any help is appreciated. Thank you in advance.

Hi TXMMOORE,

Don't make it too complex: You have ONE order, so use ONE record for that. Financial department is only interested in that one record.

You ordered THREE instruments, so you have THREE Instrument records. In the Instrument record you can follow the individual Instrument, with a field for Delivery, but also with a FK field for the Order_id.

Now it becomes easy to follow lead times, back orders, and much more.

Imb.

Answer 3

I agree with George that your solution of duplicating records is not optimal for relational databases.

The way I would handle this is with a Received Table.

tblReceived

ReceivedID (PK Autonumber)

OrderID (FK)

DateReceived

QuantityReceived

You can then track partially received orders with a query, joining the orders table with the Received table. Subtracting the quantity received from the quantity ordered and then filtering for a result greater than 0.

Answer 4

There is a great template that does a terrific job of demonstrating inventory management. You'll find Northwind Developer Edition here.

You probably are chasing the wrong solution by duplicating records. The order detail should simply have a quantity field in which you enter the number ordered.

NW Developer does not actually address backordered purchase orders, so you'll need to account for that in the way you indicate stock received on a purchase order. However, we really don't want to let multiple line items in the purchase order detail table for the same product on a single order slip into a relational database application.