Tip of the Month (July 2002)

Manually Overriding an Autonumber Control

You can use an Autonumber Control to automatically generate a unique number every time a record is created. This is commonly used to generate unique id's for the records (for example, an employee id or an order id).

Normally, the user does not care what number is generated as long as it is unique. But, in some cases, you might want to let the user override the value the system selects.

To do this, create a second form over the same table. On that form, connect the column to an Edit Control rather than an Autonumber control. Put a Jump Button on the original form to jump to the new form (keyed on the column connected to the Autonumber Control) and put a Jump Button on the new form to jump back to the original form (keyed on the same column).

For example:

Say you have an ORDER.VCE form over the ORDER table. One of the columns of the ORDER table is ORDER_ID. ORDER.VCE has an Autonumber Control that generates a unique ORDER_ID every time a new ORDER record is created.

On your ORDER.VCE form, you can have a Jump Button labeled "Order-id Override". This Jump Button will jump to a new form (described below) keyed on ORDER_ID.

The new form is also built over the ORDER table. It would have two controls, an edit control over the ORDER_ID column and a Jump Button labeled "Return". This Jump Button will jump back to ORDER.VCE keyed on ORDER_ID.

From the standpoint of the user, it will look like this: the user will run ORDER.VCE and create a new record. If the user decides to override the ORDER_ID assigned, they will press the "Order-id Override" button. The second form will appear to allow the user to specify the new value for the ORDER_ID. When the user presses the "Return" button, the original form will appear with the new value for the ORDER_ID.