ACC2000: How to Duplicate a Record on a Data Access Page (266384)

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.

1. Start Access 2000, and then open the sample database Northwind.mdb or the sample project NorthwindCS.adp.
2. In the Database window, click the Orders table, and then on the Insert menu, click Page.
3. In the New Data Access Page dialog box, click AutoPage: Columnar, and then click OK. Save the page as CopyRecord.htm.
4. 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)
   					

5. On the Tools menu, point to Macro, and then click Microsoft Script Editor.
6. On the View menu, point to Other Windows, and then click Script Outline.
7. Expand Client Objects & Events.
8. Expand the cmdCopyADO object, and then double-click the onclick event.
9. 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 = "OrderID = " & OrderID.value
   
   'Custom collection of all the TEXTAREA tags on the page
   Set textboxes = document.all.tags("TEXTAREA")
   
   '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 <> "OrderID" 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
   					

10. Close and save the page.

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.

1. Follow steps 1 through 3 of Method 1.
2. On the View menu, click Design View.
3. On the Tools menu, point to Macro, and then click Microsoft Script Editor.
4. On the View menu, point to Other Windows, and then click Script Outline.
5. Expand Client Objects & Events.
6. Expand the cmdCopySQL object, and then double-click the onclick event.
7. Enter the following code between the SCRIPT tags:

   dim sSQL
   
   'Create a SQL statement with a WHERE clause to select the current record
   sSQL = "INSERT INTO Orders (CustomerID, OrderDate, EmployeeID) " & _
          "SELECT CustomerID, OrderDate, EmployeeID " & _
          "FROM Orders WHERE OrderID = " & 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
   						

8. Close and save the page.

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.