Copy/Transfer records from main and related tables/forms

[stonward]

Hello again everyone,

I have a quotations form (main and subform) based upon two related tables. I want to be able to convert the quote to a sale and thus transfer the records to my Order and Order Details tables/forms.

Copying over the main table is straightforward enough, but I'm damned if i can get the related records copied. What is the best method of solving this?

Thanks in advance,

StonwardR

[twinnyfo]

Stonward,

My recommendation may throw you for a loop. I would not transfer the records! I would add a Yes/No field to your table that indicates whether the record is a quote or a finalized order. Then you don't have to transfer records, and you can use the same form/subform combination.

I do see one hitch with this, and that is that if you need to maintain historical records of your quotes vice your orders, it may be a problem.

To follow your method, my first question would be, what is the code you have tried and are you getting any errors? If both sets of tables have primary key indexes that are autonumbered, it may cause some problems with copying over your records, because the two tables would be looking for different indexes with which to relate themselves to (or they records could get mis-aligned. In this case, you would want to make sure that each quote has a distinct OrderNumber that YOU generate when the record is created, so that when you transfer the records, you search on that distinct field, rather than a primary key.

Hope this made sense..... Hoep even more that it helps!

[stonward]

Thanks Twinnyfo,

I've been thinking about your initial suggestion about merely adding a 'This is now a sale' option to the Quote forms....I'm not with my (database) baby at mo, but that sounds like an idea - and not one I'd have thought of. I've tried all sorts of loops and SQL insert and update statements, all to no avail (though one time the code added some 89,000 records on the detail side, all of the same product!

Will let you know asap. Thanks again,

StonwardR

[twinnyfo]

I would use a recordset in VBA. First ID the record from the main table, then create a recordset from the second table that only matches that record. Create a second recordset to add to the main table, add that one record. Then a third recordset to add the records from your first recordset. Sounds like a lot of work but it's actually quite simple.

Let mne know what you come up with....

[NeoPa]

Information provided is a bit skimpy, but I'll reply to what's there. Feel free to work with that, or post the relevant details and we can be more specific with the assistance.

First off, as much as possible, you want to ensure that all data changes are treated together as a single transaction. Transactions in database work are a very important concept if you don't want data to get out of step and essentially inconsistent.

You also need to take into consideration any table requisites for the data. EG. Tables are often set up such that a record on the one side of a One-to-Many relationship must exist before any matching records on the many side. Thus the following steps must be ordered appropriately if they are to work reliably.

As there is no reason not to, and it typically works more efficiently when you do so (as well as requiring fundamentally less code), SQL action queries are the best course for applying the updates you need.

1. Create copies of your [Quote] records (Typically only the one but can be more in some circumstances) in your [Order] table.
2. Create copies of your [QuoteDetail] records in your [OrderDetail] table.
3. Delete all records that were copied in step #2, from your [QuoteDetail] table.
4. Delete all records that were copied in step #1, from your [Quote] table.
5. If, and only if, all these steps have completed successfully then complete the transaction.
   Otherwise, take steps to leave the system as you found it and leave a message for the user that the process failed (and why).