Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/103256

From BetaArchive Wiki

ACC1x: Two Ways to Edit Forms Without Adding New Records


The information in this article applies to:

  • Microsoft Access versions 1.0, 1.1


This article demonstrates two methods for creating a form to edit data without adding new records.

The first method uses a macro to move the insertion point to the last record when you try to go to a new record. The second method uses a query that makes it impossible to add a new record and also prevents a blank new record.


Method 1: Macro Method

  1. Create a new macro called EditOnlyMacro:

          Condition:               Action
          [Product ID] Is Null     DoMenuItem
                                      Menu Bar: Form
                                      Menu Name: Records
                                      Command: GoTo
                                      Subcommand: Last 

  2. Open the Products form in Design view. In the OnCurrent property, add the name of the macro that you created above.

Method 2: Query Method

Editing the Products Table Without Adding New Records:

This example makes it possible to edit records in the Products table without adding any new records. The Categories table is the "one" side (also called the "parent") and the Products table is the "many" side of this one-to-many relationship. The linked field, Category ID, is from the Categories table.

If the table in which you want to edit records is not on the "many" side of the one-to-many relationship, one method you can use to create a parent table is shown below:

  1. Create a new query called EditOnlyQuery:

          Type: Select Query
          Join: Categories.[Category ID] <-> Products.[Category ID]
          Field: Category ID
             Table: Categories
          Field: Product ID
             Table: Products
          Field: Supplier ID
             Table: Products
          Field: Product Name
             Table: Products
          Drag the rest of the fields down from the Products table 

    NOTE: The Category ID field is from the Categories table, not the Products table.

  2. Open the Products form in Design view. Change the RecordSource property of the Products form from Products to EditOnlyQuery.

Editing the Categories Table Without Adding New Records:

The above method depends on using the ID field of the parent table in a one- to-many relationship. If the table you want to edit does not have a parent table, you can create one. For instance, if you want to edit data in the Categories table, but not add new records, do the following:

  1. Create a new table called NewParent as follows with one primary key field:

          FieldName: ID
             DataType: Number
             FieldSize: Byte 

  2. Save the table, then switch to Datasheet view.
  3. Type 1 in the first record, then close the table.
  4. Open the table that you want to edit in Design view, and add the following field:

          FieldName: EditOnlyJoin
             DataType: Number
             FieldSize: Byte 

  5. Now type 1 in every record. Hint: Use an update query to do this.
  6. Establish a relationship between the NewParent table and the Categories table. The NewParent table is the "one" side and the Categories table is the "many" side of the one-to-many relationship. (In the Database window, choose Edit Relationships from the Edit menu to establish the relationship.)
  7. Add the NewParent and Categories tables in a query (see Method 2 above). Be sure that this new query has the ID field from the NewParent table and does not have the EditOnlyJoin field from the Categories table.

Additional query words: Forms

Keywords : kbusage
Issue type : kbinfo
Technology :

Last Reviewed: November 4, 2000
© 2001 Microsoft Corporation. All rights reserved. Terms of Use.