"Autonumber" an alphanumeric field - Easy Way?

[Heidi]

Hi there,

I need to be able to apply an incremental "autonumber" to
an alphanumeric field. I found an article that shows a DAO
code to use, but I was wondering if there is an easier
way, as I am not too slick with coding language.

Any help?????? Thank you very much!

Heidi

 

[John Vinson]

Hi there,

I need to be able to apply an incremental "autonumber" to
an alphanumeric field. I found an article that shows a DAO
code to use, but I was wondering if there is an easier
way, as I am not too slick with coding language.

 

[John Vinson]

Hi there,

I need to be able to apply an incremental "autonumber" to
an alphanumeric field. I found an article that shows a DAO
code to use, but I was wondering if there is an easier
way, as I am not too slick with coding language.

Easier than... what? Without knowing what sample code you're using (it
might be one line of code or it might be 200), it's a bit difficult to
know what to suggest!

You *will* need some VBA code, but it needn't be all that difficult.

 

[Heidi]

-----Original Message-----

Easier than... what? Without knowing what sample code you're using (it
might be one line of code or it might be 200), it's a bit difficult to
know what to suggest!

You *will* need some VBA code, but it needn't be all that difficult.


.
Sorry John,

I got cut off somehow before I was finished typing....

Function FindMax ()

Dim db As DAO.Database
Dim mx As Integer
Dim rs As DAO.Recordset
Dim rsVal As String

Set db = CurrentDb()
Set rs = db.OpenRecordset ("Increment", dbOpenDynaset)

rs.MoveFirst

rsVal = rs.Fields("[fieldname]").Value
mx = Right(rsVal, Len(rsVal) - 3)

Do While Not rs.EOF
rsVal - rs.Fields("[fieldname]").Value
If Right(rsVal, Len(rsVal) - 3) > mx Then
mx = Right(rsVal, Len(rsVal) - 3)
End If
rs.MoveNext
Loop

FindMax = " " & (mx + 1)

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

End Function
===========================
That is quite confusing to me! I can do SOME VBA, but I
don't think this fits the bill. Do you know of something a
bit simpler? Again, thank you very much for your help!

 

[Heidi]

-----Original Message-----
Heidi,

I think I am the person that code you posted was written

for, lol. Anyway, whats the format for your numbers and
such? What is the use for the numbers, text, etc. I
adapted the code to make it year compliant, so if the
numbers are date related, i could probably help you.

Ben
.
Hi Ben,

What I need to do is create PO Numbers in this format:
MTR1, MTR2, and so on....
Of course, each new entry creating a new incremental
number to those above. While searching many News Groups, I
found quite a few postings relating to this question, but
each posting giving a different way to accomplish this
task. The instructions I found were a bit vague - thus
this posting. I need step by step instructions as I'm not
too savy with code. (I'm learning though!) lol
If you or anyone else could spare some time to help I
would GREATLY appreciate it.
Thanks all!

 

[Heidi]

-----Original Message-----


Hmmm: this kind of thing usually sounds like it should be either (a)
handled with a Format property, or (b) two separate fields.

If the "numbers" are always heidi0001, heidi0002, heidi0003 etc., then it's
easy to make the Format property on the text box ="""Heidi""0000" and use a
plain autonumber.

If the text prefix is significant eg heidi0001, heidi0002, johnv0001,
johnv0002, etc., then you have two separate pieces of information and, in
1NF, two fields. Putting the key into two fields also makes the allocation
of the next number very easy, in one line of VBA.

(Sorry: if you ask a question in a tables design group, you are likely to
be told to redesign your tables..!)

Best wishes


Tim F

.
Hi Tim,

Thank you very much for you helpful response. In my case,
the alpha prefix will always be the same, I only need the
numbers to change. I tried typing in what you had told me
in the format property in the PO# field on my table, and
then left the datatype as AutoNumber. At least I have the
alpha characters in that field now, but it is not
increasing the numbers by one on each new record. (I think
I'm missing something...) Can you offer any more help?
Thanks so much!!!

 

[Tim Ferguson]

Thank you very much for you helpful response. In my case,
the alpha prefix will always be the same, I only need the
numbers to change. I tried typing in what you had told me
in the format property in the PO# field on my table, and
then left the datatype as AutoNumber.

Sorry: it was me being a bit overzealous with the quoting. If you open the
properties for the text box on the form you should type in this exactly:

"MTR"000

(Those are zeroes, and the MTR is inside double-quotes.) All this does is
display the autonumber in three digits with the literal MTR stuck in front
of it.

You can set this on the Tabledef field property, in which case it will be
echoed into any textboxes you create based on the field (saves time when
creating Forms and Reports), or just remember to set it whenever you create
a form or report.

Hope that helps


Tim F