ACC2000: New MS Excel Data Does Not Appear in MS Access Form

ID: Q197232

The information in this article applies to:
Moderate: Requires basic macro, coding, and interoperability skills.


SYMPTOMS

When you create a Microsoft Access form in Microsoft Excel that is based on data in a spreadsheet, and you then add additional information to that spreadsheet, the data that you added is not updated in the Microsoft Access form, even though it is highlighted in the Microsoft Excel spreadsheet.


CAUSE

The information that is sent to Microsoft Access is defined by a named range in the spreadsheet. The named range is not updated when you add additional rows of information to the spreadsheet.


RESOLUTION

You must redefine the named range in the spreadsheet. To redefine a range of cells in Microsoft Excel, follow these steps:

  1. On the Insert menu, point to Name, and then click Define.


  2. In the Define Name box, click the named range for the Microsoft Access form in the Names In Workbook box. The name for the range will probably be similar to the following: 


    3. 
   Book1_Sheet1_List
  3. Select the range in the Refers To box and modify the range to include any newly added rows. You can do so by typing the new range in the box.

    NOTE: The Refers To box displays the reference, formula, or constant the name represents.


  4. After you have modified the range, click OK.


  5. Click View MS Access Form on the spreadsheet. The newly added information should be available in the Microsoft Access form.



MORE INFORMATION

Steps to Reproduce Behavior

  1. Start Microsoft Excel and on Sheet1 of a new workbook enter the following in cells A1:C3:


    2. 
   A1: First   B1: Last    C1: Middle.
   A2: Adam    B2: Smith   C2: A.
   A3: Bob     B3: Jones   C3: B.
  2. Save the workbook as Book1.xls.


  3. Click cell A1 on Sheet1.


  4. On the Data menu, click MS Access Form and in the Create Microsoft Access Form box, click New Database, and then click OK. If you do not see MS Access Form on the Data menu, load the Access Links add-in.


  5. In the Form Wizard, add all the fields in the Available Fields box to the Selected Fields box, and then click Next.


  6. On the What layout would you like for your form page, click Tabular, and then click Finish.


  7. When the wizard has created the new form, note the records in the form. Close the form, and then close Microsoft Access.


  8. Switch to Microsoft Excel and, in Sheet1 of Book1.xls in cells A4:C4, type the following: 


    9. 
   A4: Bill  B4: Williams  C4: C.
  9. Click View MS Access Form. Note that the newly added record has not been added to the form even though the information is selected in the Microsoft Excel spreadsheet.



REFERENCES

For more information about the Access Links feature, click Microsoft Excel Help on the Help menu, type "Create a Microsoft Access form for a Microsoft Excel list" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about defining ranges in Microsoft Excel, click Microsoft Excel Help on the Help menu, type "name cells in a workbook" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words: 


Keywords          : kbdta IntpOff IntpOlea 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: July 15, 1999