increment field value

B

Burghew

Hi All,

I have 2 fields called document & Revision which contains the titles for
the documents to be stored in the databse it gets filled up as the user
enters them and the revison numbers associated with them.
1. What I need to do is to make sure that no duplicate entry is entered for
document and if entered should have a different revison number. I cannot
make them both as the primary key bacause there may be times when both the
fields maybe null but the entry must be there in the database.
2. If the user types in an existing document then I need the revision field
to be filled up with the current revision number after update.

I am a bit stuck here and any help will be great.

Thanks,
B
 
A

Allen Browne

Couple of possibilities:

Solution A using Numbers for DocNum and Revision
======================================
Summary: This solution requires that the DocNum and Revision fields both
have values, or neither have values. If they have values the combination
must be unique.
Advantage: Easy to get the next revision number (numeric).
Disadvantage: Must have a revision number (0?) for the original as well.

1. Open the table in design view.

2. Open the Indexes dialog (View menu)

3. Create a 2-field index by entering something like this:
DocRevision DocNum Ascending
RevisionNum Ascending
In the lower pane of the dialog when DocRevision is selected:
Primary No
Unique Yes
Ignore Nulls No

4. Open the Properties dialog (View menu), and set these properties for the
table:
Validation Rule ([DocNum] Is Null)=([RevisionNum] Is Null)
Validation Rule If you enter a DocNum or Revision, you must enter
the other one also.

Solution B: using a Text field for Revision
=============================
Summary: This solution records a zero-length string for the Revision
where no value needs to be shown. This allows the original (unrevised)
document to have no revision number (or appears that way), but it prevents
multiple entries for a document unless they have different revision numbers.
Advantage: Accepts a zero-length string for the original (unrevised)
document.
Disadvantage: Harder to get the next revision number for a text field.

1. Open the table in design view.

2. Make sure Revision is a Text field, and set these properties:
Default Value ""
Required Yes
Allow Zero Length Yes

3. Set up the index as in the example above, but set the Ignore Nulls
property to Yes.
 
Top