Duplicates sets of records on conditon

[rkalapura]

I have a quotation database running in our company network which we use to send quotes to customers. A quote may have one or more items in it and each item has different prices based on the quantity. Each item will have a record in the database. So, if a quote has five items, there will be five records with same quote number and if 30 items, 30 records with same quote number.



Many times, same enquiries are coming from different sources (customers) asking quotes specially Govt orders. Now we need to take every enquiry as separate and feed separately in the database. Consider the time for a quote which has 30 items in it and each items has 6 level of pricing. When this same enquiry is coming from 12 different customers, it takes a lot of time to feed it in.

Now my question: How can I copy all the records belongs to one "quote no" and add it to the same table with a different quote number and some changes to the other customer related fields? If I can do that, I just need to change the customer address and I am ready with the second, then third and so on. I want to copy all related records, change quote number and related fields and then append to database.

Also I need this when I revise a quote. The system needs to keep the old quote as well as the revised quote for future reference. If I can duplicate it in one command, I just need to do the changes, Revision Number, Revision Date etc. and the revision is ready to go. Now I use update querries to do changes to all related records during revision. Then I will have only the revised quote in my database and when I want to refer the old quote, I have to refer the hard copy.

I know there is a "Duplicate Record" command available in form level (through wizard), but that duplicates only one record, not a selection of records as I need it here. If this can do with macros, fine. I am not an expert in VB, but if I get the code, I will attach it to a command button and use.

Appreciate your comments and suggestion.

Thank you and God Bless You.

[rkalapura]

Thanks Guys. Find a solution like this:

Created a New Form"QuoteDuplicate" which takes new customer information and a new quote number as input. A command Button in Quote Form will open up this form. A command button on QuoteDuplicate will execute the following in sequence:
Make Table Qry - This will make a new table from the source table "Quote" with the criteria "Quote Number". All records in that purticular Quote will copy to a Temp table.
Upadate Qry - This will update temp table with new customer information from the new "QuoteDuplicate" form.
Append Qry - This will append all new updated records from Temp table to Quote table.
Delete Object - This will delete the Temp table from database.

Now I have a new quote ready with a new quote number and customer details, rest exactly same as the old quote.

The same procedure I used to revise a quote with a new revision number and revision can be made any number of time. Records of every revisions are available in the system.

Your expertise and valuable inputs if anything wrong in this procedure is highly appreciated.

Thank you and God Bless You.
Happy Holidays to everybody.