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

• Microsoft Access 2000

SUMMARY

When you are entering new records in a data access page, you may want to speed the data entry process by having fields in the new record fill automatically with values from the current record. This article shows you how to create script that enables you to fill selected fields (or all fields) in a new record with values from the current record automatically.

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.

Additional query words:

Keywords          : kbdta AccDAP DAPScriptHowTo dtavbscript 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: July 8, 1999