By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Copy/Transfer records from main and related tables/forms

stonward
100+
P: 145
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
Aug 23 '12 #1
Share this Question
Share on Google+
4 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,158
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!
Aug 23 '12 #2

stonward
100+
P: 145
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
Aug 23 '12 #3

twinnyfo
Expert Mod 2.5K+
P: 3,158
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....
Aug 23 '12 #4

NeoPa
Expert Mod 15k+
P: 31,307
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).
Aug 23 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.