ACC1x: Two Ways to Edit Forms Without Adding New Records

• Microsoft Access versions 1.0, 1.1

SUMMARY

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.

MORE INFORMATION

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.

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" (without quotation marks) 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" (without quotation marks) 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.