Auto increase letter and numbers

CJ, you don't want to have tblControl as the recordsource for your form.
Such a setup would add rows to tblControl. You don't want to do that. It
should *always* have only one row.
Do it like this in your code:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim intnewrec As Integer
intnewrec = frm.NewRecord
If intnewrec = True Then
Call fcnUpdate_tblControl
myTextBox = fcnGetNextSequence ' --here. Use your control
name.
End If
End Sub

UpRider

"CJ" wrote in message
...
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

Hmmmm, OK, I now have an unbound form (frmControl) with one unbound textbox
called CJTEST.

My code for the frmControl is as follows:

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

When I view the form, my cursor is flashing inside of the blank textbox. I
have the ability to type in the textbox but that is all.

Shouldn't the next value from the table be showing up in here?

--
Thanks in advance for your continued brilliance!

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



"UpRider" wrote in message
...
CJ, you don't want to have tblControl as the recordsource for your form.
Such a setup would add rows to tblControl. You don't want to do that. It
should *always* have only one row.
Do it like this in your code:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim intnewrec As Integer
intnewrec = frm.NewRecord
If intnewrec = True Then
Call fcnUpdate_tblControl
myTextBox = fcnGetNextSequence ' --here. Use your control
name.
End If
End Sub

UpRider

"CJ" wrote in message
...
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

CJ, having the code in an unbound form is rather pointless. An unbound form
will never have a BeforeInsert event. There's nothing for it to insert into.
Perhaps you should explain what you want to do with the seqNo.
Do you want to add it to a column in a new row in some table, maybe along
with some other textboxes you fill in on the form?

We're working in a vacuum here, and need more information on what you are
trying to do.

UpRider

"CJ" wrote in message
...
Hmmmm, OK, I now have an unbound form (frmControl) with one unbound
textbox called CJTEST.

My code for the frmControl is as follows:

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

When I view the form, my cursor is flashing inside of the blank textbox. I
have the ability to type in the textbox but that is all.

Shouldn't the next value from the table be showing up in here?

--
Thanks in advance for your continued brilliance!

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



"UpRider" wrote in message
...
CJ, you don't want to have tblControl as the recordsource for your form.
Such a setup would add rows to tblControl. You don't want to do that. It
should *always* have only one row.
Do it like this in your code:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim intnewrec As Integer
intnewrec = frm.NewRecord
If intnewrec = True Then
Call fcnUpdate_tblControl
myTextBox = fcnGetNextSequence ' --here. Use your control
name.
End If
End Sub

UpRider

"CJ" wrote in message
...
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

Well, I thought it was pointless too but I was will to test it and you
didn't mention what I should use to call the procedure and, as previously
mentioned, I am extremely code challenged.

Anyway, I now have the unbound textbox on an employee form and I added the
code to the BeforeInsert event of this form.

At this point, I can edit existing records, but I am unable to enter
anything into a new record. I can move between the fields but the form is
completely blank. The form works fine without the BeforeInsert code and
textbox.

I need to 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.

It needs to be a value in a field in the forms recordsource.

Thanks for your persistence in cracking "this nut".


"UpRider" wrote in message
...
CJ, having the code in an unbound form is rather pointless. An unbound
form will never have a BeforeInsert event. There's nothing for it to
insert into.
Perhaps you should explain what you want to do with the seqNo.
Do you want to add it to a column in a new row in some table, maybe along
with some other textboxes you fill in on the form?

We're working in a vacuum here, and need more information on what you are
trying to do.

UpRider

"CJ" wrote in message
...
Hmmmm, OK, I now have an unbound form (frmControl) with one unbound
textbox called CJTEST.

My code for the frmControl is as follows:

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

When I view the form, my cursor is flashing inside of the blank textbox.
I have the ability to type in the textbox but that is all.

Shouldn't the next value from the table be showing up in here?

--
Thanks in advance for your continued brilliance!

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



"UpRider" wrote in message
...
CJ, you don't want to have tblControl as the recordsource for your form.
Such a setup would add rows to tblControl. You don't want to do that. It
should *always* have only one row.
Do it like this in your code:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim intnewrec As Integer
intnewrec = frm.NewRecord
If intnewrec = True Then
Call fcnUpdate_tblControl
myTextBox = fcnGetNextSequence ' --here. Use your control
name.
End If
End Sub

UpRider

"CJ" wrote in message
...
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

CJ, take all the code out of the Before_Insert event.
Add the code to the below 2 events.
txtSeqNo is a textbox on your form.
Now, you need to store the seqNo in a table in your database. You probably
need to add a text column to your Employees table to store the value in.
The txtSeqNo control above needs to be bound to that column in Employee
table.
If you don't know how to do this, let me know and we can get through it.

Also, you say you cannot add *any* new employees to your table. Is that
true? Is that a new problem?
Test this *after* you remove all the code from the Before_Insert.
Also, please let me know what the recordsource is for your form.


Private Sub Form_AfterInsert()
'update seqNo after record is saved
Call fcnUpdate_tblControl
End Sub

Private Sub Form_Current()
'if this is a new record, get the seqNo
If Me.NewRecord Then
txtSeqNo = fcnGetNextSequence
End If
End Sub

UpRider

"CJ" wrote in message
...
Well, I thought it was pointless too but I was will to test it and you
didn't mention what I should use to call the procedure and, as previously
mentioned, I am extremely code challenged.

Anyway, I now have the unbound textbox on an employee form and I added the
code to the BeforeInsert event of this form.

At this point, I can edit existing records, but I am unable to enter
anything into a new record. I can move between the fields but the form is
completely blank. The form works fine without the BeforeInsert code and
textbox.

I need to 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.

It needs to be a value in a field in the forms recordsource.

Thanks for your persistence in cracking "this nut".


"UpRider" wrote in message
...
CJ, having the code in an unbound form is rather pointless. An unbound
form will never have a BeforeInsert event. There's nothing for it to
insert into.
Perhaps you should explain what you want to do with the seqNo.
Do you want to add it to a column in a new row in some table, maybe along
with some other textboxes you fill in on the form?

We're working in a vacuum here, and need more information on what you are
trying to do.

UpRider

"CJ" wrote in message
...
Hmmmm, OK, I now have an unbound form (frmControl) with one unbound
textbox called CJTEST.

My code for the frmControl is as follows:

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

When I view the form, my cursor is flashing inside of the blank textbox.
I have the ability to type in the textbox but that is all.

Shouldn't the next value from the table be showing up in here?

--
Thanks in advance for your continued brilliance!

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



"UpRider" wrote in message
...
CJ, you don't want to have tblControl as the recordsource for your
form. Such a setup would add rows to tblControl. You don't want to do
that. It should *always* have only one row.
Do it like this in your code:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim intnewrec As Integer
intnewrec = frm.NewRecord
If intnewrec = True Then
Call fcnUpdate_tblControl
myTextBox = fcnGetNextSequence ' --here. Use your control
name.
End If
End Sub

UpRider

"CJ" wrote in message
...
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

........and we have a Winner!!!

Absolutely Brilliant!

It created the sequential value and I can add records again.

If you keep working so hard, somebody's going to make you an MVP. g

Thanks very, very much!
--

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


"UpRider" wrote in message
...
CJ, take all the code out of the Before_Insert event.
Add the code to the below 2 events.
txtSeqNo is a textbox on your form.
Now, you need to store the seqNo in a table in your database. You probably
need to add a text column to your Employees table to store the value in.
The txtSeqNo control above needs to be bound to that column in Employee
table.
If you don't know how to do this, let me know and we can get through it.

Also, you say you cannot add *any* new employees to your table. Is that
true? Is that a new problem?
Test this *after* you remove all the code from the Before_Insert.
Also, please let me know what the recordsource is for your form.


Private Sub Form_AfterInsert()
'update seqNo after record is saved
Call fcnUpdate_tblControl
End Sub

Private Sub Form_Current()
'if this is a new record, get the seqNo
If Me.NewRecord Then
txtSeqNo = fcnGetNextSequence
End If
End Sub

UpRider

"CJ" wrote in message
...
Well, I thought it was pointless too but I was will to test it and you
didn't mention what I should use to call the procedure and, as previously
mentioned, I am extremely code challenged.

Anyway, I now have the unbound textbox on an employee form and I added
the code to the BeforeInsert event of this form.

At this point, I can edit existing records, but I am unable to enter
anything into a new record. I can move between the fields but the form is
completely blank. The form works fine without the BeforeInsert code and
textbox.

I need to 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.

It needs to be a value in a field in the forms recordsource.

Thanks for your persistence in cracking "this nut".


"UpRider" wrote in message
...
CJ, having the code in an unbound form is rather pointless. An unbound
form will never have a BeforeInsert event. There's nothing for it to
insert into.
Perhaps you should explain what you want to do with the seqNo.
Do you want to add it to a column in a new row in some table, maybe
along with some other textboxes you fill in on the form?

We're working in a vacuum here, and need more information on what you
are trying to do.

UpRider

"CJ" wrote in message
...
Hmmmm, OK, I now have an unbound form (frmControl) with one unbound
textbox called CJTEST.

My code for the frmControl is as follows:

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

When I view the form, my cursor is flashing inside of the blank
textbox. I have the ability to type in the textbox but that is all.

Shouldn't the next value from the table be showing up in here?

--
Thanks in advance for your continued brilliance!

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



"UpRider" wrote in message
...
CJ, you don't want to have tblControl as the recordsource for your
form. Such a setup would add rows to tblControl. You don't want to do
that. It should *always* have only one row.
Do it like this in your code:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim intnewrec As Integer
intnewrec = frm.NewRecord
If intnewrec = True Then
Call fcnUpdate_tblControl
myTextBox = fcnGetNextSequence ' --here. Use your control
name.
End If
End Sub

UpRider

"CJ" wrote in message
...
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

Glad you got it working. Keep at it and you can figure stuff out....

UpRider

"CJ" wrote in message
...
.......and we have a Winner!!!

Absolutely Brilliant!

It created the sequential value and I can add records again.

If you keep working so hard, somebody's going to make you an MVP. g

Thanks very, very much!
--

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


"UpRider" wrote in message
...
CJ, take all the code out of the Before_Insert event.
Add the code to the below 2 events.
txtSeqNo is a textbox on your form.
Now, you need to store the seqNo in a table in your database. You
probably need to add a text column to your Employees table to store the
value in.
The txtSeqNo control above needs to be bound to that column in Employee
table.
If you don't know how to do this, let me know and we can get through it.

Also, you say you cannot add *any* new employees to your table. Is that
true? Is that a new problem?
Test this *after* you remove all the code from the Before_Insert.
Also, please let me know what the recordsource is for your form.


Private Sub Form_AfterInsert()
'update seqNo after record is saved
Call fcnUpdate_tblControl
End Sub

Private Sub Form_Current()
'if this is a new record, get the seqNo
If Me.NewRecord Then
txtSeqNo = fcnGetNextSequence
End If
End Sub

UpRider

"CJ" wrote in message
...
Well, I thought it was pointless too but I was will to test it and you
didn't mention what I should use to call the procedure and, as
previously mentioned, I am extremely code challenged.

Anyway, I now have the unbound textbox on an employee form and I added
the code to the BeforeInsert event of this form.

At this point, I can edit existing records, but I am unable to enter
anything into a new record. I can move between the fields but the form
is completely blank. The form works fine without the BeforeInsert code
and textbox.

I need to 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.

It needs to be a value in a field in the forms recordsource.

Thanks for your persistence in cracking "this nut".


"UpRider" wrote in message
...
CJ, having the code in an unbound form is rather pointless. An unbound
form will never have a BeforeInsert event. There's nothing for it to
insert into.
Perhaps you should explain what you want to do with the seqNo.
Do you want to add it to a column in a new row in some table, maybe
along with some other textboxes you fill in on the form?

We're working in a vacuum here, and need more information on what you
are trying to do.

UpRider

"CJ" wrote in message
...
Hmmmm, OK, I now have an unbound form (frmControl) with one unbound
textbox called CJTEST.

My code for the frmControl is as follows:

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

When I view the form, my cursor is flashing inside of the blank
textbox. I have the ability to type in the textbox but that is all.

Shouldn't the next value from the table be showing up in here?

--
Thanks in advance for your continued brilliance!

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



"UpRider" wrote in message
...
CJ, you don't want to have tblControl as the recordsource for your
form. Such a setup would add rows to tblControl. You don't want to do
that. It should *always* have only one row.
Do it like this in your code:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim intnewrec As Integer
intnewrec = frm.NewRecord
If intnewrec = True Then
Call fcnUpdate_tblControl
myTextBox = fcnGetNextSequence ' --here. Use your
control name.
End If
End Sub

UpRider

"CJ" wrote in message
...
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