Duplicate a Record

[AKQTS]

Here is what I am trying to accomplish: When a user clicks a button, the current record is duplicated in the table, but one the the columns within the record is now incremented by one (making it different then the previous record). (This deals with order/ batch processing). I.E. i have one order, but may batches. When the user opens a new order it automatically defaults the batch number to one, but when a button is pressed I would like the current record (row) to be duplicated so it has the same order number, but the batch number to be now 2 (n+1). I do not know where to start on this. I have it defaulting to one, but am unaware of how to make the button duplicate the record and increment the batch # by 1. Thanks

[jzwp11]

Do you have a table with the order information and a separate, but related table for the batches? With this structure, there is no need to duplicate the order information. You just need to create a new batch tied to the order. In terms of form, you would have a main form based on the orders table with a subform based on the batch table.

Structurally speaking the tables should look like this

tblOrders
-pkOrderID primary key, autonumber
-OrderNumber

tblOrderBatches
-pkOrderBatchID primary key, autonumber
-fkOrderID foreign key to tblOrders
-BatchNumber

Relative to incrementing the batch number, you would add some code to the before insert event of the new batch record. Check out this thread that had a similar question

[AKQTS]

I am trying to set-up how that would work, the only thing is when i get the order numbers assigned by a different program, so when this table is updated it has the new "random" numbers in it. The second table (tblorderbatches) still has to duplicate the order number to the next row when I assign a second, third,.... batch number. Unless I am not understanding teh foreign key concept.

[jzwp11]

Your order you get from another program would go in the OrderNumber field. When you create that record, the primary key field autoincrements and it is that value that you use to link to the batch table. Then you will need the code I suggest to increment the BatchNumber field in the related table. Since an order can have many batches, that describes a one(order)-to-many(batch) relationship which warrants the table stucture I proposed.

[AKQTS]

Sorry if I am still not clear on this, but yes one tbl for orders and the other for batches that uses the autonum fields in each as the linking field. the only thing is when i duplicate a batch record (increment n+1), I would have to duplicate my autonum field (within the same table) to link it back to the order table. I.E autonum 537 = batch 1 and (duplicated to) batch 2 in the batch table links to autonum 537 (order 30115) in the orders table. Which is why I was inquiring about duplicating my orders and incrementing the batch number each time.

[jzwp11]

If you link the main form (orders) to the subform (batches), the fkOrderID field will be automatically populated (duplicated) by Access, so all you have to worry about is incrementing your batchnumber (not the primary key field) value. I've attached a sample database. Just tab through the batch number control in the subform and finish the record. When the record is committed, the batchnumber should automatically increment.

[AKQTS]

Thanks for the file, I have been playing with this..

With some data filled in here is how my tables look:


Now what I am stuck on still is when a button is clicked, adding a new batch number to the table for that order number selected.

Here is what I have so far that does not work:
__________________________________________________ ___
Function New_Batch_No()
On Error GoTo New_Batch_No_Err
With CodeContextObject
DoCmd.Echo False, ""
If DCount("*", "tblOrders", "BatchNumber<>0") = 0 Then
tblOrders.BatchNumber = 1
Else
tblOrders.BatchNumber = DMax("BatchNumber", "tblOrders") + 1
End If

End With

New_Batch_No_Exit:
Exit Function
__________________________________________________ _____


Any help would be great, thanks.

[jzwp11]

The database I provided has a form frmOrders; in that form is a subform for the batches. There is code in the before update event of the subform that will automatically increment the batch number just prior to when the record is saved.

Your code will not work for several reasons. You cannot add a record to a table by assigning a value as you do in your code; you have to use an append query . Secondly, the batch number field is not in the order table but the tblOrderBatches. Third, you'll need to reference the current order number for which the batch number is applicable.

[AKQTS]

Thanks for all the help on this jzwp11. I am working on the sql for the append query and am hitting a roadblock.

When I run the query it has an error due to key violations, here is my code:

PARAMETERS [Forms]![frmOrders]![OrderNumber] Long;
INSERT INTO tblOrderBatches ( BatchNumber, pkOrderBatchID )
SELECT Max([tblOrderBatches]![BatchNumber])+1 AS Expr1, Max([pkOrderBatchID])+1 AS Expr2
FROM tblOrderBatches

Thanks for all the help.

[jzwp11]

The pkOrderBatchID should be an autonumber field, so there is no need to even have it in the append query. You do,however, have to have a foreign key field related back to the order number (fkOrderID). Additionally you have to find the last batch number for the current order otherwise you will just increment the batch number consecutively independent of the order number, so you will need a WHERE clause in the append query.

PARAMETERS [Forms]![frmOrders]![OrderNumber] Long;
INSERT INTO tblOrderBatches ( BatchNumber, fkOrderID )
SELECT Max([tblOrderBatches]![BatchNumber])+1 AS Expr1, [Forms]![frmOrders]![OrderNumber] AS Expr2
FROM tblOrderBatches
WHERE fkOrderID=[Forms]![frmOrders]![OrderNumber]

[AKQTS]

Thanks for all the help, minor modification to the code:

PARAMETERS [Forms]![frmOrders]![OrderNumber] Long;
INSERT INTO tblOrderBatches ( BatchNumber, fkOrderID )
SELECT Max([tblOrderBatches]![BatchNumber])+1 AS Expr1, [Forms]![frmOrders]![PkOrderID] AS Expr2
FROM tblOrderBatches
WHERE fkOrderID=[Forms]![frmOrders]![pkOrderID];

When I click a button it runs a macro containing this Append Query. Due to the where clause it increments the batch number for that order number and not the total batch number so Order 1 can have Batch 1,2,3 and Order 2 can have 1,2,3 not Order 2 having 4,5,6. Works great!

[jzwp11]

Glad you got it sorted out! Good luck with your project.