Auto increase letter and numbers

Hi Groupies:

I have been asked if I can create a number on a form that would start at A
0001 and then increase by one for each record. When the value gets to A 9999
then it needs to go to B 0001.

How would I do this?

--
Thanks in advance for your continued brilliance!

CJ
-----------------------------------
Work together.....Save your sanity

You'll need to create a procedure that inspects the "last" value and
increments accordingly. Untested psuedo-code:

* What is the # portion of the "last" (most recent, max dated) value?
* If 9999, set the # portion to 1, then get the alpha portion of the
value, get the ASCII value of the letter, add one, convert it back to
letter, set the alpha portion to that.
* If 9999, add one

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"CJ" wrote in message
...
Hi Groupies:

I have been asked if I can create a number on a form that would start at A
0001 and then increase by one for each record. When the value gets to A
9999 then it needs to go to B 0001.

How would I do this?

--
Thanks in advance for your continued brilliance!

CJ
-----------------------------------
Work together.....Save your sanity

Hi Jeff

Thanks for the input. Could you help me out with the psuedo-code part? I am
extremely "code challenged".

Thanks a bunch!

CJ
-----------------------------------
Work together.....Save your sanity

"Jeff Boyce" wrote in message
...
You'll need to create a procedure that inspects the "last" value and
increments accordingly. Untested psuedo-code:

* What is the # portion of the "last" (most recent, max dated) value?
* If 9999, set the # portion to 1, then get the alpha portion of the
value, get the ASCII value of the letter, add one, convert it back to
letter, set the alpha portion to that.
* If 9999, add one

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"CJ" wrote in message
...
Hi Groupies:

I have been asked if I can create a number on a form that would start at
A 0001 and then increase by one for each record. When the value gets to A
9999 then it needs to go to B 0001.

How would I do this?

--
Thanks in advance for your continued brilliance!

CJ
-----------------------------------
Work together.....Save your sanity

Perhaps one of the other volunteers here in the 'group can offer code.

Be aware that you will still need to customize it for YOUR specific data
structure. ... and trouble-shoot it when it doesn't work correctly the
first time ...

Are you sure you don't want to work on the challenge?g

A couple hints:

* the "last" number really means the # in the last (most recent) record --
so your records will have to have some kind of date/time stamp in them to be
able to pick the "last"
* alternatively, you'd have to be able to pick the "largest" alpha
character, then the largest number with that alpha character.
* you can use Access HELP to find the Char() and ASCII() functions and
their syntax.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

"CJ" wrote in message
...
Hi Jeff

Thanks for the input. Could you help me out with the psuedo-code part? I
am extremely "code challenged".

Thanks a bunch!

CJ
-----------------------------------
Work together.....Save your sanity

"Jeff Boyce" wrote in message
...
You'll need to create a procedure that inspects the "last" value and
increments accordingly. Untested psuedo-code:

* What is the # portion of the "last" (most recent, max dated) value?
* If 9999, set the # portion to 1, then get the alpha portion of the
value, get the ASCII value of the letter, add one, convert it back to
letter, set the alpha portion to that.
* If 9999, add one

I'm usually fine with the customizing and I can usually make sense of what
has been written, it's just that whole "How To Write Code" bit that gives me
trouble.

I do have a SalesID autonumber field to keep the transactions in sequential
order.

Heres hoping for more assistance.......anyone, anyone....Bueller....g

Thanks Jeff.

--

CJ
-----------------------------------
Work together.....Save your sanity


"Jeff Boyce" wrote in message
...
Perhaps one of the other volunteers here in the 'group can offer code.

Be aware that you will still need to customize it for YOUR specific data
structure. ... and trouble-shoot it when it doesn't work correctly the
first time ...

Are you sure you don't want to work on the challenge?g

A couple hints:

* the "last" number really means the # in the last (most recent)
record -- so your records will have to have some kind of date/time stamp
in them to be able to pick the "last"
* alternatively, you'd have to be able to pick the "largest" alpha
character, then the largest number with that alpha character.
* you can use Access HELP to find the Char() and ASCII() functions and
their syntax.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

"CJ" wrote in message
...
Hi Jeff

Thanks for the input. Could you help me out with the psuedo-code part? I
am extremely "code challenged".

Thanks a bunch!

CJ
-----------------------------------
Work together.....Save your sanity

"Jeff Boyce" wrote in message
...
You'll need to create a procedure that inspects the "last" value and
increments accordingly. Untested psuedo-code:

* What is the # portion of the "last" (most recent, max dated) value?
* If 9999, set the # portion to 1, then get the alpha portion of the
value, get the ASCII value of the letter, add one, convert it back to
letter, set the alpha portion to that.
* If 9999, add one

Be aware that an Access Autonumber field is NOT guaranteed to be sequential
.... it's primary purpose is to provide a unique row identifier.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"CJ" wrote in message
news
I'm usually fine with the customizing and I can usually make sense of what
has been written, it's just that whole "How To Write Code" bit that gives
me trouble.

I do have a SalesID autonumber field to keep the transactions in
sequential order.

Heres hoping for more assistance.......anyone, anyone....Bueller....g

Thanks Jeff.

--

CJ
-----------------------------------
Work together.....Save your sanity


"Jeff Boyce" wrote in message
...
Perhaps one of the other volunteers here in the 'group can offer code.

Be aware that you will still need to customize it for YOUR specific data
structure. ... and trouble-shoot it when it doesn't work correctly the
first time ...

Are you sure you don't want to work on the challenge?g

A couple hints:

* the "last" number really means the # in the last (most recent)
record -- so your records will have to have some kind of date/time stamp
in them to be able to pick the "last"
* alternatively, you'd have to be able to pick the "largest" alpha
character, then the largest number with that alpha character.
* you can use Access HELP to find the Char() and ASCII() functions and
their syntax.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

"CJ" wrote in message
...
Hi Jeff

Thanks for the input. Could you help me out with the psuedo-code part? I
am extremely "code challenged".

Thanks a bunch!

CJ
-----------------------------------
Work together.....Save your sanity

"Jeff Boyce" wrote in message
...
You'll need to create a procedure that inspects the "last" value and
increments accordingly. Untested psuedo-code:

* What is the # portion of the "last" (most recent, max dated) value?
* If 9999, set the # portion to 1, then get the alpha portion of the
value, get the ASCII value of the letter, add one, convert it back to
letter, set the alpha portion to that.
* If 9999, add one

CJ, put this code in a new module, say basControl
Option Compare Database
Option Explicit
Public Function fcnGetNextSequence() as String
fcnGetNextSequence = DLookup("seqNo", "tblControl")
End Function

Public Function fcnUpdate_tblControl(Optional strStart As String)
Dim strSQL As String
Dim strLtr As String
Dim strNum As Variant
Dim strConcat As String
If Len(strStart) = 0 Then
strNum = Right(DLookup("seqno", "tblControl"), 4)
strLtr = Left(DLookup("seqno", "tblControl"), 1)
Else
strNum = Right(strStart, 4)
strLtr = Left(strStart, 1)
End If
If strNum = "9999" Then
strNum = "0001"
strLtr = Asc(strLtr) + 1
strLtr = Chr$(strLtr)
Else: strNum = strNum + 1
End If
strNum = Format(strNum, "0000")
strConcat = strLtr + strNum
strSQL = "UPDATE tblControl set seqNo = " & Chr$(39) & strConcat &
Chr$(39)
CurrentDb.Execute strSQL, dbFailOnError
End Function

Create a new table with one row and one column, no key. Call it tblControl.
The single text field is called seqno
Put a starting number in the table, say A9998, for testing.

Calling fcnNextSequence will return the value of seqNo in the table.
Calling fcnUpdate_tblControl will increment the value of seqNo in tblControl

The latter function has an optional argument; it will reset the beginning
seqNo in tblControl.
If you ever use the optional argument make sure it is entered like
call fcnUpDate_tblControl("A1234")
You might want to use this to reset the SeqNo when it reaches Z9999 !

HTH, UpRider

"CJ" wrote in message
news
I'm usually fine with the customizing and I can usually make sense of what
has been written, it's just that whole "How To Write Code" bit that gives
me trouble.

I do have a SalesID autonumber field to keep the transactions in
sequential order.

Heres hoping for more assistance.......anyone, anyone....Bueller....g

Thanks Jeff.

--

CJ
-----------------------------------
Work together.....Save your sanity


"Jeff Boyce" wrote in message
...
Perhaps one of the other volunteers here in the 'group can offer code.

Be aware that you will still need to customize it for YOUR specific data
structure. ... and trouble-shoot it when it doesn't work correctly the
first time ...

Are you sure you don't want to work on the challenge?g

A couple hints:

* the "last" number really means the # in the last (most recent)
record -- so your records will have to have some kind of date/time stamp
in them to be able to pick the "last"
* alternatively, you'd have to be able to pick the "largest" alpha
character, then the largest number with that alpha character.
* you can use Access HELP to find the Char() and ASCII() functions and
their syntax.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

"CJ" wrote in message
...
Hi Jeff

Thanks for the input. Could you help me out with the psuedo-code part? I
am extremely "code challenged".

Thanks a bunch!

CJ
-----------------------------------
Work together.....Save your sanity

"Jeff Boyce" wrote in message
...
You'll need to create a procedure that inspects the "last" value and
increments accordingly. Untested psuedo-code:

* What is the # portion of the "last" (most recent, max dated) value?
* If 9999, set the # portion to 1, then get the alpha portion of the
value, get the ASCII value of the letter, add one, convert it back to
letter, set the alpha portion to that.
* If 9999, add one

UpRider.... cool code!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"UpRider" wrote in message
...
CJ, put this code in a new module, say basControl
Option Compare Database
Option Explicit
Public Function fcnGetNextSequence() as String
fcnGetNextSequence = DLookup("seqNo", "tblControl")
End Function

Public Function fcnUpdate_tblControl(Optional strStart As String)
Dim strSQL As String
Dim strLtr As String
Dim strNum As Variant
Dim strConcat As String
If Len(strStart) = 0 Then
strNum = Right(DLookup("seqno", "tblControl"), 4)
strLtr = Left(DLookup("seqno", "tblControl"), 1)
Else
strNum = Right(strStart, 4)
strLtr = Left(strStart, 1)
End If
If strNum = "9999" Then
strNum = "0001"
strLtr = Asc(strLtr) + 1
strLtr = Chr$(strLtr)
Else: strNum = strNum + 1
End If
strNum = Format(strNum, "0000")
strConcat = strLtr + strNum
strSQL = "UPDATE tblControl set seqNo = " & Chr$(39) & strConcat &
Chr$(39)
CurrentDb.Execute strSQL, dbFailOnError
End Function

Create a new table with one row and one column, no key. Call it
tblControl.
The single text field is called seqno
Put a starting number in the table, say A9998, for testing.

Calling fcnNextSequence will return the value of seqNo in the table.
Calling fcnUpdate_tblControl will increment the value of seqNo in
tblControl

The latter function has an optional argument; it will reset the beginning
seqNo in tblControl.
If you ever use the optional argument make sure it is entered like
call fcnUpDate_tblControl("A1234")
You might want to use this to reset the SeqNo when it reaches Z9999 !

HTH, UpRider

"CJ" wrote in message
news
I'm usually fine with the customizing and I can usually make sense of
what has been written, it's just that whole "How To Write Code" bit that
gives me trouble.

I do have a SalesID autonumber field to keep the transactions in
sequential order.

Heres hoping for more assistance.......anyone, anyone....Bueller....g

Thanks Jeff.

--

CJ
-----------------------------------
Work together.....Save your sanity


"Jeff Boyce" wrote in message
...
Perhaps one of the other volunteers here in the 'group can offer code.

Be aware that you will still need to customize it for YOUR specific data
structure. ... and trouble-shoot it when it doesn't work correctly the
first time ...

Are you sure you don't want to work on the challenge?g

A couple hints:

* the "last" number really means the # in the last (most recent)
record -- so your records will have to have some kind of date/time
stamp in them to be able to pick the "last"
* alternatively, you'd have to be able to pick the "largest" alpha
character, then the largest number with that alpha character.
* you can use Access HELP to find the Char() and ASCII() functions and
their syntax.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

"CJ" wrote in message
...
Hi Jeff

Thanks for the input. Could you help me out with the psuedo-code part?
I am extremely "code challenged".

Thanks a bunch!

CJ
-----------------------------------
Work together.....Save your sanity

"Jeff Boyce" wrote in message
...
You'll need to create a procedure that inspects the "last" value and
increments accordingly. Untested psuedo-code:

* What is the # portion of the "last" (most recent, max dated) value?
* If 9999, set the # portion to 1, then get the alpha portion of the
value, get the ASCII value of the letter, add one, convert it back to
letter, set the alpha portion to that.
* If 9999, add one

Gina,
Well, thank you. I'm just a 69 year old retired guy who has picked up up a
few things about coding. Been doing it 40 years.
You could never get this done as easily in COBOL!

UpRider

"Gina Whipp" wrote in message
...
UpRider.... cool code!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"UpRider" wrote in message
...
CJ, put this code in a new module, say basControl
Option Compare Database
Option Explicit
Public Function fcnGetNextSequence() as String
fcnGetNextSequence = DLookup("seqNo", "tblControl")
End Function

Public Function fcnUpdate_tblControl(Optional strStart As String)
Dim strSQL As String
Dim strLtr As String
Dim strNum As Variant
Dim strConcat As String
If Len(strStart) = 0 Then
strNum = Right(DLookup("seqno", "tblControl"), 4)
strLtr = Left(DLookup("seqno", "tblControl"), 1)
Else
strNum = Right(strStart, 4)
strLtr = Left(strStart, 1)
End If
If strNum = "9999" Then
strNum = "0001"
strLtr = Asc(strLtr) + 1
strLtr = Chr$(strLtr)
Else: strNum = strNum + 1
End If
strNum = Format(strNum, "0000")
strConcat = strLtr + strNum
strSQL = "UPDATE tblControl set seqNo = " & Chr$(39) & strConcat &
Chr$(39)
CurrentDb.Execute strSQL, dbFailOnError
End Function

Create a new table with one row and one column, no key. Call it
tblControl.
The single text field is called seqno
Put a starting number in the table, say A9998, for testing.

Calling fcnNextSequence will return the value of seqNo in the table.
Calling fcnUpdate_tblControl will increment the value of seqNo in
tblControl

The latter function has an optional argument; it will reset the beginning
seqNo in tblControl.
If you ever use the optional argument make sure it is entered like
call fcnUpDate_tblControl("A1234")
You might want to use this to reset the SeqNo when it reaches Z9999 !

HTH, UpRider

"CJ" wrote in message
news
I'm usually fine with the customizing and I can usually make sense of
what has been written, it's just that whole "How To Write Code" bit that
gives me trouble.

I do have a SalesID autonumber field to keep the transactions in
sequential order.

Heres hoping for more assistance.......anyone, anyone....Bueller....g

Thanks Jeff.

--

CJ
-----------------------------------
Work together.....Save your sanity


"Jeff Boyce" wrote in message
...
Perhaps one of the other volunteers here in the 'group can offer code.

Be aware that you will still need to customize it for YOUR specific
data structure. ... and trouble-shoot it when it doesn't work
correctly the first time ...

Are you sure you don't want to work on the challenge?g

A couple hints:

* the "last" number really means the # in the last (most recent)
record -- so your records will have to have some kind of date/time
stamp in them to be able to pick the "last"
* alternatively, you'd have to be able to pick the "largest" alpha
character, then the largest number with that alpha character.
* you can use Access HELP to find the Char() and ASCII() functions and
their syntax.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

"CJ" wrote in message
...
Hi Jeff

Thanks for the input. Could you help me out with the psuedo-code part?
I am extremely "code challenged".

Thanks a bunch!

CJ
-----------------------------------
Work together.....Save your sanity

"Jeff Boyce" wrote in message
...
You'll need to create a procedure that inspects the "last" value and
increments accordingly. Untested psuedo-code:

* What is the # portion of the "last" (most recent, max dated)
value?
* If 9999, set the # portion to 1, then get the alpha portion of the
value, get the ASCII value of the letter, add one, convert it back to
letter, set the alpha portion to that.
* If 9999, add one

Wow, thanks for the code UpRider.

So I have created the table and the module, as directed. I also created a
form, called frmControl, based on the table and added the field. I added an
extra text field call Stuff so that I had somewhere to type

I don't think that I am calling the module correctly.....or something,
because the number is not increasing when I move to the next record on the
form. I was thinking that the number would increase on the form as soon as I
move to a new record.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim intnewrec As Integer
intnewrec = frm.NewRecord
If intnewrec = True Then
Call fcnUpdate_tblControl
Call fcnGetNextSequence
End If
End Sub

The rest of the code is:

Option Compare Database
Option Explicit
Public Function fcnGetNextSequence() As String
fcnGetNextSequence = DLookup("seqNo", "tblControl")
End Function

Public Function fcnUpdate_tblControl(Optional strStart As String)
Dim strSQL As String
Dim strLtr As String
Dim strNum As Variant
Dim strConcat As String
If Len(strStart) = 0 Then
strNum = Right(DLookup("seqno", "tblControl"), 4)
strLtr = Left(DLookup("seqno", "tblControl"), 1)
Else
strNum = Right(strStart, 4)
strLtr = Left(strStart, 1)
End If
If strNum = "9999" Then
strNum = "0001"
strLtr = Asc(strLtr) + 1
strLtr = Chr$(strLtr)
Else: strNum = strNum + 1
End If
strNum = Format(strNum, "0000")
strConcat = strLtr + strNum
strSQL = "UPDATE tblControl set seqNo = " & Chr$(39) & strConcat &
Chr$(39)
CurrentDb.Execute strSQL, dbFailOnError
End Function

Thanks so much for helping me out. I didn't have any clue how to start.
--
CJ
-----------------------------------
Work together.....Save your sanity


"UpRider" wrote in message
...
CJ, put this code in a new module, say basControl
Option Compare Database
Option Explicit
Public Function fcnGetNextSequence() as String
fcnGetNextSequence = DLookup("seqNo", "tblControl")
End Function

Public Function fcnUpdate_tblControl(Optional strStart As String)
Dim strSQL As String
Dim strLtr As String
Dim strNum As Variant
Dim strConcat As String
If Len(strStart) = 0 Then
strNum = Right(DLookup("seqno", "tblControl"), 4)
strLtr = Left(DLookup("seqno", "tblControl"), 1)
Else
strNum = Right(strStart, 4)
strLtr = Left(strStart, 1)
End If
If strNum = "9999" Then
strNum = "0001"
strLtr = Asc(strLtr) + 1
strLtr = Chr$(strLtr)
Else: strNum = strNum + 1
End If
strNum = Format(strNum, "0000")
strConcat = strLtr + strNum
strSQL = "UPDATE tblControl set seqNo = " & Chr$(39) & strConcat &
Chr$(39)
CurrentDb.Execute strSQL, dbFailOnError
End Function

Create a new table with one row and one column, no key. Call it
tblControl.
The single text field is called seqno
Put a starting number in the table, say A9998, for testing.

Calling fcnNextSequence will return the value of seqNo in the table.
Calling fcnUpdate_tblControl will increment the value of seqNo in
tblControl

The latter function has an optional argument; it will reset the beginning
seqNo in tblControl.
If you ever use the optional argument make sure it is entered like
call fcnUpDate_tblControl("A1234")
You might want to use this to reset the SeqNo when it reaches Z9999 !

HTH, UpRider

"CJ" wrote in message
news
I'm usually fine with the customizing and I can usually make sense of
what has been written, it's just that whole "How To Write Code" bit that
gives me trouble.

I do have a SalesID autonumber field to keep the transactions in
sequential order.

Heres hoping for more assistance.......anyone, anyone....Bueller....g

Thanks Jeff.

--

CJ
-----------------------------------
Work together.....Save your sanity


"Jeff Boyce" wrote in message
...
Perhaps one of the other volunteers here in the 'group can offer code.

Be aware that you will still need to customize it for YOUR specific data
structure. ... and trouble-shoot it when it doesn't work correctly the
first time ...

Are you sure you don't want to work on the challenge?g

A couple hints:

* the "last" number really means the # in the last (most recent)
record -- so your records will have to have some kind of date/time
stamp in them to be able to pick the "last"
* alternatively, you'd have to be able to pick the "largest" alpha
character, then the largest number with that alpha character.
* you can use Access HELP to find the Char() and ASCII() functions and
their syntax.

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

"CJ" wrote in message
...
Hi Jeff

Thanks for the input. Could you help me out with the psuedo-code part?
I am extremely "code challenged".

Thanks a bunch!

CJ
-----------------------------------
Work together.....Save your sanity

"Jeff Boyce" wrote in message
...
You'll need to create a procedure that inspects the "last" value and
increments accordingly. Untested psuedo-code:

* What is the # portion of the "last" (most recent, max dated) value?
* If 9999, set the # portion to 1, then get the alpha portion of the
value, get the ASCII value of the letter, add one, convert it back to
letter, set the alpha portion to that.
* If 9999, add one