AutoIncrement a Text Field?

[Robert T]

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

 

[Ken Snell [MVP]]

An expression similar to this would do what you seek:

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

 

[Wayne Morgan]

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?

 

[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

 

[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

 

[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

 

[Ken Snell [MVP]]

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.

 

[David C. Holley]

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