Microsoft KB Archive/266384

= ACC2000: How to Duplicate a Record on a Data Access Page =

Article ID: 266384

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q266384



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SUMMARY
To reduce data entry, you may want to copy data from an existing record to a new record. This article provides two methods to accomplish this on a data access page.



MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Method 1
This method uses an ADO recordset and a collection of text boxes on the page to copy all the values from one record to a new record in the same table.  Start Access 2000, and then open the sample database Northwind.mdb or the sample project NorthwindCS.adp. In the Database window, click the Orders table, and then on the Insert menu, click Page. In the New Data Access Page dialog box, click AutoPage: Columnar, and then click OK. Save the page as CopyRecord.htm.  Add the following controls to the new page, and then set the properties of the controls as indicated:   Command Button Id: cmdCopyADO Value: Copy Record (ADO) Command Button Id: cmdCopySQL Value: Copy Record (SQL)  On the Tools menu, point to Macro, and then click Microsoft Script Editor. On the View menu, point to Other Windows, and then click Script Outline.</li> Expand Client Objects & Events.</li> Expand the cmdCopyADO object, and then double-click the onclick event.</li>  Enter the following code between the SCRIPT tags: '--- ' This procedure clones and filters the default recordset for the ' current record. Then, it moves to a new record and sets the ' corresponding control to the value from the filtered recordset '--- Dim cm, rs, rsNewOrderID 'Variables used to loop through the textarea tags on the page Dim el, textboxes 'Clone the defaultrecordset and filter the current order Set rs = MSODSC.DefaultRecordset.Clone rs.Filter = &quot;OrderID = &quot; & OrderID.value

'Custom collection of all the TEXTAREA tags on the page Set textboxes = document.all.tags(&quot;TEXTAREA&quot;)

'Save the current record and move to a new record MSODSC.CurrentSection.DataPage.Save MSODSC.CurrentSection.DataPage.NewRecord

'Set the values for each field except the primary key field 'in this case, since OrderID is an autonumber, saving the record will 'assign the value of the primary key For Each el In textboxes If el.id <> &quot;OrderID&quot; Then el.value = rs.fields(el.id).value End If Next 'Cleanup rs.Close Set rs = nothing 'Save the new record (optional) MSODSC.CurrentSection.DataPage.Save </li> Close and save the page.</li></ol>

To test this page, open it in Microsoft Internet Explorer 5 or later. Move to any record on the page, and then click Copy Record (ADO). Note that the page moves to a new record with a new primary key value and duplicate values for all other fields.

Method 2
This method uses an SQL statement to insert a record into the underlying table for the page. It then requeries the page and moves to the last record in the underlying recordset. <ol> Follow steps 1 through 3 of Method 1.</li> On the View menu, click Design View.</li> On the Tools menu, point to Macro, and then click Microsoft Script Editor.</li> On the View menu, point to Other Windows, and then click Script Outline.</li> Expand Client Objects & Events.</li> Expand the cmdCopySQL object, and then double-click the onclick event.</li>  Enter the following code between the SCRIPT tags: dim sSQL

'Create a SQL statement with a WHERE clause to select the current record sSQL = &quot;INSERT INTO Orders (CustomerID, OrderDate, EmployeeID) &quot; & _ &quot;SELECT CustomerID, OrderDate, EmployeeID &quot; & _ &quot;FROM Orders WHERE OrderID = &quot; & OrderID.value

'Run the SQL statement created above MSODSC.Connection.Execute sSQL

'Requery the source for the page MSODSC.CurrentSection.DataPage.Requery

'The MoveLast method will move to the last record 'in the recordset for the page. Depending upon the primary 'key for the underlying record source, this may not be the 'last record added. MSODSC.CurrentSection.DataPage.MoveLast </li> Close and save the page.</li></ol>

To test this page, open it in Internet Explorer 5 or later. Move to any record on the page, and then click Copy Record (SQL). Note that the page moves to the last record with a new primary key value and duplicate values for the CustomerID, OrderDate, and EmployeeID fields.

<div class="references_section">