Recordset Clone?

[Jeff71]

I am building a custom quoting system.

1 Quote can have multiple Revisions. 1 Revision can have multiple items.

Example:

Quote 12345 Rev A
Item1 - $1.00
Item2 - $2.00
Item3 - $1.50

then, some changes can (does) occur, new items can be inserted, and price can change, therefore a Rev B is created:

Quote 12345 Rev B
Item1 - $1.75
Item2 - $2.00
Item3 - $1.50
Item4 - $13.00

My question is:

How can all the items from Rev A automatically be passed to Rev B when created? (at times there are 100’s of items, and we dont want to re-type them all)

What would be the correct method to pass the latest Rev items into the new one?

 

[namliam]

Presuming your quote + revision are the Key to your quotes...

The Items should be in a seperate table, thus an append query would do your trick.

 

[Jeff71]

Namliam,

Thanks for the reply,

Your approach is logic,

However, attached is the structure of the relationships that I intend to use.

I need a command done with in VBA to capture the last recordset and paste it into the new (empty) recordset.

In this case, the tbl_type1 contains all the items.

Therefore, I need to have the ability to copy the last recordset of items in tbl_type1 and paste them into a new recordset.

Preferably, this could be achieved through coding,

 

[Jeff71]

Attached is a picture of what the form looks like.

- FormA – The Quote
- SubformX (inside FormA) - Quote Revisions Letter
- SubformY (inside SubformX) - The details

When creating a new record in SubformX (going to a new rev letter) I would like to copy/paste the details of SubformY into the details of the new SubformY.

How can this be achieved?

 

[namliam]

Running a query from VBA is not only faster, but more efficient, more structured and more maintainable.

First store your old rev_history.id in a variable (var1)
Now create a new record in the rev_history, store its ID in another variable (var2).

Run a query on tbl_Type1 extract all where project_quote_reference = var1 and append it to the same table tbl_type1 replacing the quote_reference by var2

I hope you understand what I am trying to say....

P.S. Welcome to (one of) the best Access forums on the net.

 

[Jeff71]

Thanks for the info,

I will try your method,

I've known this forum for years, but i forgot my username and password, it had been too long.

It is indeed one of the best for smart access answers...

Thanks,

 

[Jeff71]

I have tried your method, but can’t seem to get it to work,

Attached is a 2 sample databases (temp2002-03 and temp2007).zip

On the form, there is a button to create a new quote revision.

What is the vba way to clone all the detail records into a new revision detail records?

The reason is because each revision will have 100s of detail entries, and each revision will only modify a few products, so we don’t want to have to type each product details all over every time there is a small revision.

 

[namliam]

What have you tried??

The solution is to run queries....

You know how to fetch data from your form, so get the last quote letter and quote number from your form.
Now create an Append query (just in normal design view) and make this query run against your table adding new data to your table, changing data (quote letter) where needed.

 

[Jeff71]

Thanks it worked!!!

I passed the query (qry_append_lastRev) Criteria like this: [Forms]![frm_quote]![frm_quote_revs].[Form]![txtRevID]

Where [txtRevID] is pulling only the details needed.

Then Append it in vba like this: DoCmd.OpenQuery "qry_append_lastRev", acViewNormal, acAdd

The new rev worked, but the details don’t show up until the subform is requeried:

Forms!frm_quote!frm_quote_revs!frm_quote_details.Requery

Your input is greatly appreciated,

 

[namliam]

Instead of the DoCmd.Openquery use
Currentdb.Execute "qry_append_lastRev"

Open your table and make sure the data was added before you go searching for problems. Also you have to make sure to switch your form(s) to the right revision version, otherwize you can requery all you want but if the right version is not selected, it will never show up.

 

[DanG]

This sounds like something I want to do for my current project, but did not understand ANY of it.

I too have a form with a couple subforms and I would like to duplicate the entire chosen form and subform contents into a new record. And in some cases I would want to duplicate one record many times, but I seems this method is capable of that.

Is there a sample database to work from that demondstrates this technique?

 

[hjeff71]

Actually, I do not have a “sample database” to demonstrate the action I was looking for. Some of these forms that I needed this process for, are rather very complex with lots of vba and sql programming, interactions and rules etc,. However, with what niamliam has pointed out, I was able to integrate the concept.

Usually your subform would be “linked” to your main form. Therefore, if your main form’ ID is 5, and you have multiple records in your subform, they should also contain the value 5 (Linked_ID) (for as many record as you may have in the linked sf).

Therefore,

1) Build a query, that uses the criteria ie: [Forms]![main_form]![subform].[Form]![Linked_ID] – this will pull only the records with 5.
2) Create an “Append Query”, to paste the data gathered in the first query into the desired subform or table.

I believe there are better ways of achieving the result you are looking for, but this simple process is working beautifully for me.