increment revision value

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'

"Burghew" <burghew@mailinator.com> wrote:
[color=blue]
> 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.[/color]

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

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" <burghew@mailinator.com> wrote:
[color=blue]
>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.[/color]

Steve Jorgensen wrote:[color=blue]
> 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.[/color]

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

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

"Bas Cost Budde" <b.costbudde@heuvelqop.nl> wrote in message
news:cpn14i$89e$1@news2.solcon.nl...[color=blue]
> Steve Jorgensen wrote:[color=green]
> > As I recall, in Access, by creating a unique index on the combination of
> > fields (essentially a secondary key), you will enforce uniqueness, but[/color][/color]
allow[color=blue][color=green]
> > nulls.[/color]
>
> 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[/color]