I am a long time user of a product called "Alpha Five" where you simply place
a check mark in the field rule for a primary key text field and it will
automatically increment the value for each record. For example, Class_ID
"003" will become "004" on the next record.

I checked everywhere I could in Access 2000 and could not find a similar
field rule unless I use a numeric field. I'm assuming I will have to program
some type of autoincrement field rule for a text field. Can someone please
advise how I can do such? Hopefully there is a simple solution.

Thanks,
Robert T

An expression similar to this would do what you seek:

NewTextValue = Format(Nz(DMax("Val([NameOfTextField])", "NameOfTable"), 0) +
1, "000")


--

Ken Snell
<MS ACCESS MVP>

"Robert T" <(E-Mail Removed)> wrote in message
news:7DE04536-8C15-4925-95C6-(E-Mail Removed)...
>I am a long time user of a product called "Alpha Five" where you simply
>place
> a check mark in the field rule for a primary key text field and it will
> automatically increment the value for each record. For example, Class_ID
> "003" will become "004" on the next record.
>
> I checked everywhere I could in Access 2000 and could not find a similar
> field rule unless I use a numeric field. I'm assuming I will have to
> program
> some type of autoincrement field rule for a text field. Can someone please
> advise how I can do such? Hopefully there is a simple solution.
>
> Thanks,
> Robert T

This formula should work. It will find the largest number currently in the
field, add one, then format it to 3 characters.

=Format(DMax("[FieldName]", "[TableName]") + 1, "000")

What do you want done if you go beyond 999?

--
Wayne Morgan
MS Access MVP


"Robert T" <(E-Mail Removed)> wrote in message
news:7DE04536-8C15-4925-95C6-(E-Mail Removed)...
>I am a long time user of a product called "Alpha Five" where you simply
>place
> a check mark in the field rule for a primary key text field and it will
> automatically increment the value for each record. For example, Class_ID
> "003" will become "004" on the next record.
>
> I checked everywhere I could in Access 2000 and could not find a similar
> field rule unless I use a numeric field. I'm assuming I will have to
> program
> some type of autoincrement field rule for a text field. Can someone please
> advise how I can do such? Hopefully there is a simple solution.
>
> Thanks,
> Robert T

Hi Wayne:

Thanks to you and Ken for your prompt, excellent replies.

If there were any possibility of going past 900, I would have used a text
field with a maximum value of 4, etc.

Robert T

Ken:

Thanks for the prompt reply. I guess that means I did not miss a field rule
that would automatically do this for me.

The expression appears to be exactly what I'm looking for. Please advise the
best place to utilize this expression. Do I use it in Field Rules or on a
form?

Robert T

Hi Ken:

I decided to try using your expression as a "default" for the Class_ID field
on a form and it worked like a charm.

Thanks a lot,
Robert T

Yes, using the expression as the DefaultValue for the textbox that is bound
to the field Class_ID is the correct thing to do.
Good luck.

--

Ken Snell
<MS ACCESS MVP>

"Robert T" <(E-Mail Removed)> wrote in message
news:1E3B22C4-1654-4B8C-A700-(E-Mail Removed)...
> Hi Ken:
>
> I decided to try using your expression as a "default" for the Class_ID
> field
> on a form and it worked like a charm.
>
> Thanks a lot,
> Robert T

Up on my soapbox (again), What specifically are you trying to
accomplish? By that I mean, why do you want to increment a text field
and why won't a numeric field do? If you're dealing with an existing DB
where the text fild contains a text value that was created via
Autoincrement, it is possible to convert the value in the text field to
a numeric value.

David H

Robert T wrote:
> I am a long time user of a product called "Alpha Five" where you simply place
> a check mark in the field rule for a primary key text field and it will
> automatically increment the value for each record. For example, Class_ID
> "003" will become "004" on the next record.
>
> I checked everywhere I could in Access 2000 and could not find a similar
> field rule unless I use a numeric field. I'm assuming I will have to program
> some type of autoincrement field rule for a text field. Can someone please
> advise how I can do such? Hopefully there is a simple solution.
>
> Thanks,
> Robert T