The information in this article applies to:
- Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SUMMARY
Novice: Requires knowledge of the user interface on single-user computers.
This article describes a formula that you can use to set the default value
of a bound Text field in a form to the next higher numeric value (default
value = default value + 1).
The sample formula does the following:
- It finds the highest numeric value used in a table.
- It strips any leading text characters.
- It adds 1 to the value found in step 1.
- It reconnects the leading text characters.
NOTE: This example assumes that the number of leading text characters is
known at the time the form is designed.
MORE INFORMATION
Use the following sample procedure to increase the default value of a
bound Text field on a form:
- Open an existing database or create a new database.
- Create a new table in Design view.
- Add a field called Book ID with a Text data type. On the Edit
menu, click Primary Key.
NOTE: In versions 1.x and 2.0, click Set Primary Key on the Edit Menu.
- Add a second field of any data type to the table.
- Save the table as Increment.
- Switch to Datasheet view and enter the following records:
Book ID Second Field
--------------------------
BO-110
BO-111
BO-112
- Close the table.
- Create a new form in Design view based on the Increment table.
- If it is not displayed, click Field List on the View menu.
- Drag the Book ID field and the second field from the field list
to the form.
- Verify that the form's DefaultView property is set to Single Form.
- Select the Book ID control.
- Set the DefaultValue property of the Book ID text box to the
following code:
NOTE: In the following sample code, an underscore (_) at the end of a
line is used as a line-continuation character. Remove the underscore
from the end of the line when re-creating this expression.
="BO-" & Right(DMax("[Book ID]","Increment"),Len(DMax_
("[Book ID]","Increment"))-3)+1
- View the form in Form view and enter a new record, the Book ID field
increments to the next available number automatically.
The components of the formula for the DefaultView property are as follows:
- DMax("[Book ID]","Increment") locates the highest value in the table.
- Len(DMax(...)) finds the length of the value returned by the DMax()
function.
- Right(DMax(...),Len(...)-3) strips the three leading text characters.
- +1 increments the result of the Right() function.
- "BO-" concatenates the text "BO-" to the incremented number.
Note that this example works correctly when the form's DefaultView
property is Single View; it may not work correctly in Continuous View.
When you move to a new record and begin to enter data, Microsoft Access
displays the next empty record. The default values for this record are
calculated before the record you are currently editing is committed.
Because the DMax() function calculates the maximum value from those
records stored in the table, the Book ID of the record you are currently
editing is repeated.
If you are working in a multiuser environment, it is possible that more
than one user may receive the same calculated Book ID value. Although you
can manually change the Book ID, you can also maintain the highest numeric
value in a separate table, using a macro or Visual Basic code (or Access
Basic in versions 1.x and 2.0).
|