ACC1x: Two Ways to Edit Forms Without Adding New Records

ID: Q103256

The information in this article applies to:

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

Create a new macro called EditOnlyMacro:


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

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:

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.

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:

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


      FieldName: ID
         DataType: Number
         FieldSize: Byte

Save the table, then switch to Datasheet view.

Type 1 in the first record, then close the table.

Open the table that you want to edit in Design view, and add the following field:


      FieldName: EditOnlyJoin
         DataType: Number
         FieldSize: Byte

Now type 1 in every record. Hint: Use an update query to do this.

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.)

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 GnlDe
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbinfo