ACC2000: Fill Record with Data from Selected Record on a Data Access Page (234217)

MORE INFORMATION

You can create your own Add New Record button on a page to fill all the fields in a new record using data from the previous record.

To create a page that automatically fills the new record, follow these steps:

1. Open the sample database Northwind.mdb or NorthwindCS.adp.
2. In the Database window, click Pages under Objects, and then click New.
3. In the New Data Access Page box, click AutoPage: Columnar, click Order Details in the Choose the table or query/view where the object's data comes from box, and then click OK.
4. On the View menu, click Design View.
5. Set the Height property of the Header: Order Details section to 2in.
6. In the toolbox, click the Command Button, and then click somewhere below the Discount field. If the Command Button Wizard appears, click Cancel.
7. Set the following properties for the command button:

      ID: cmdAddNewRec
      InnerText: Add New Record
   					

8. On the Tools menu, point to Macro, and click Microsoft Script Editor.
9. Type the following into the Script Editor between the HEAD tags:

   <SCRIPT language=vbscript>
   <!--
   Option Explicit
   
   Dim OrID
   Dim ProdID
   Dim UPrice
   Dim Quant
   Dim Disc
   -->
   </SCRIPT>
   					

10. In the Script Outline window, expand the Client Objects and Events folder. Expand cmdAddNewRec, and then double-click onclick. Type the following script.
    
    NOTE: If the Script Outline window is not displayed, point to Other Windows on the View menu, and then click Script Outline.

    <SCRIPT event=onclick for=cmdAddNewRec language=vbscript>
    <!--
    On Error Resume Next
    
    Dim strMsg
    
    OrID=document.all.item("OrderID").value
    ProdID=document.all.item("ProductID").value
    UPrice=document.all.item("UnitPrice").value
    Quant=document.all.item("Quantity").value
    Disc=document.all.item("Discount").value
    
    MSODSC.CurrentSection.DataPage.NewRecord()
    
    If Err.number <> 0 Then
       strMsg = "Error #" & Err.number & vbCrLf
       strMsg = strMsg & Err.description & vbCrLf
       strMsg = strMsg & "Source: " & Err.source
       MsgBox strMsg,vbOKOnly + vbCritical,"Error"
    Else	
       document.all.item("OrderID").value=OrID
       document.all.item("ProductID").value=ProdID
       document.all.item("UnitPrice").value=UPrice
       document.all.item("Quantity").value=Quant
       document.all.item("Discount").value=Disc
    End If
    -->
    </SCRIPT>
    					

11. On the File menu, click Save All. In the File name box, type dapAutoFill, and then click Save.
12. On the File menu, click Exit.
13. On the View menu, click Page View. Note that the page shows that there is a total of 2155 records.
    
    
14. Browse to a record, for example, Order Details 7 of 2155. Note the values of the fields for record #7.
    
    
15. Click the Add New Record button that you created in step 6. Note that you are now on record 2156 of 2156, and this new record has the same field values that record #7 had.
    
    
16. NOTE: You need to change the ProductID to a different value, 33 for example, before you can save the new record. Otherwise, you are trying to create a duplicate record.

This functionality cannot be applied directly to the navigation bar that was created with the page. For this reason, you need to create your own Add New Record button. It is recommended that you also add you own First Record, Previous Record, Next Record, Last Record buttons, and so on, and that you then remove the navigation bar from the page.