increment revision value

[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 & Regards'

[Keith Wilby]

"Burghew" <bu*****@mailin ator.com> wrote:

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.

There are a number of ways you could do this but perhaps using the DMax
method might be worth a look. You could have a DMax statement in the
'Default Value' property for your 'revision' text box such that it filters
your recordset for the contents of your 'document' field, finds the max
value from revision and then add 1:

DMax("[Revision]","[MyTable]","[Document] = 'MyCriteria'") +1

where MyTable is the name of your table and MyCriteria is the filter
criteria for the 'Document' Field.

I haven't tested this specific example but it should point you in the right
direction.

HTH - Keith.
www.keithwilby.com

[Steve Jorgensen]

As I recall, in Access, by creating a unique index on the combination of
fields (essentially a secondary key), you will enforce uniqueness, but allow
nulls. Another way to handle the optional nature of the document
title/revision combination is to split that into its own document identity
table, leaving the other fields in a document content table, and have a 1-to-1
relationship between them with the document content record as the source for
the primary key. The document identity table would use a uniqu index to
enforce uniqueness of the title/revision combination, but both fields would be
required since the record itself id optional.

To create a unique index on a combination of fields, you must go into the
indexes popup in table design, and put the 2 fields on consecutive rows with
the index name only on the first row. With the first row selected, check the
"Unique" box in the index properties.

On Tue, 14 Dec 2004 14:58:18 +0400, "Burghew" <bu*****@mailin ator.com> wrote:

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.

[Bas Cost Budde]

Steve Jorgensen wrote:

As I recall, in Access, by creating a unique index on the combination of
fields (essentially a secondary key), you will enforce uniqueness, but allow
nulls.

No. I agree with you that it should (set theory) but Access doesn't allow.

Can't set the IgnoreNulls in the PK either. I can set the property in
another index (not a primary one) but I can still not enter a Null in
the field.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[paii, Ron]

You could set the default value for revision to "" and allow zero length
strings.

"Bas Cost Budde" <b.*********@he uvelqop.nl> wrote in message
news:cp******** **@news2.solcon .nl...

Steve Jorgensen wrote:

As I recall, in Access, by creating a unique index on the combination of
fields (essentially a secondary key), you will enforce uniqueness, but allow nulls.

No. I agree with you that it should (set theory) but Access doesn't allow.

Can't set the IgnoreNulls in the PK either. I can set the property in
another index (not a primary one) but I can still not enter a Null in
the field.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea