Microsoft KB Archive/103256

{|
 * width="100%"|

ACC1x: Two Ways to Edit Forms Without Adding New Records

 * }

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.

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:

<pre class="FIXEDTEXT">     FieldName: ID         DataType: Number FieldSize: Byte

</li> Save the table, then switch to Datasheet view.</li> Type 1 in the first record, then close the table.</li>  Open the table that you want to edit in Design view, and add the following field:

<pre class="FIXEDTEXT">     FieldName: EditOnlyJoin DataType: Number FieldSize: Byte

</li> Now type 1 in every record. Hint: Use an update query to do this.</li> 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.)</li> 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.</li></ol>

Additional query words: Forms

Keywords : kbusage

Issue type : kbinfo

Technology :