Copying an existing set of records

[canjfn]

Scenario

frmpurchaseorder, for entering the supplier’s details, name address etc
frmpurchaseordersub, for entering the items to be purchased

frmpurchaseorder, populates the tblpurchaseorder
frmpurchaseordersub, populates the tblpurchaseordersub

Obviously, when the form frmpurchaseorder is opened it is blank and ready for a new purchase order to be raised.

What I want to do is, when the form frmpurchaseorder is opened, drop down a combo box with a list of all previous orders (this is simple enough), but on selection of an existing order, populate the form (both frmpurchaseorder & frmpurchaseordersub) with the records from the old purchase order selected in the combo box.

I'm sure that this facility is required in most P/O systems; I cannot seem to find anything on it in the forums.

Would anyone point me in the right direction in solving this

Thankyou

 

[neileg]

Your forms need to be based on a query and the query needs a criterion that points to your combo box. If you requery the form using the After Update event of the combo, your form will be populated with the order the user has selected. Not difficult.

However, I think what you want is to offer the user is the chance to base a new order on the old one. If you use a form bound to your existing purchase orders, your user will be altering the record that holds the old purchase order. I'm sure you don't want this. The way I would do this is to bind the form to a temporary table (and subtable). Use queries as suggested above to populate the temporary tables (use an append query). Allow the user to modify the content of the temporary tables. When the user is finished, append the data in the temporary tables to your main tables, and then empty the temporary tables ready for the next order.

Others here will proably have more sophisticated methods for this process, but I hope you understand the principles, ie you want a copy of the data in the old order, not the old order itself.

 

[canjfn]

Cheers for your thoughts, you were right with what I am trying to do, although not completely as your suggestion (a little too complicated for me using temp tables), I have managed to be able to copy the purchase order line items into a new purchase order, by means of a mirror form of the purchase order, bound to a query

On the main purchase order form I have a command button to open the mirror form, then a command button with the following commands

Me.frmCopyTransmittalNoSub.SetFocus
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
DoCmd.Close acForm, "frmCopyTransmittalNo"



#########################################

With the existing form open and now having focus, the following commands attached to another command button

DoCmd.GoToControl "frmNewTransmittalSub"
DoCmd.RunCommand acCmdPasteAppend


Thanks for your responce

 

[neileg]

Yes, I see. Pleased it works. Couple of points for you to consider for future reference.

There's nothing complex about temporary tables. In fact, a table is a table, it's how you use them that makes them temporary.

Your method creates a new record and then allows the user to modify it. If you used a temporary table, it would make it easy to abandon the purchase order altogether.