Auto increase letter and numbers

C

CJ

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?
 
J

Jeff Boyce

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
 
C

CJ

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 said:
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
Click to expand...
 
J

Jeff Boyce

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
 
C

CJ

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.
 
J

Jeff Boyce

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
 
U

UpRider

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
 
U

UpRider

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
 
C

CJ

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.
 
U

UpRider

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
 
C

CJ

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?
 
U

UpRider

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
 
C

CJ

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".
 
U

UpRider

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
 
C

CJ

........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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Merging from an Excel file with graphics 1
Expression in SQL or user-defined function? 4
Autonumber field in Excel that behaves the same as Access 0
obtain sequence number automaticly when depend on value of other f 3
Finding the proper style field and the numbered paragraph value within the style 0
Code is failing 4
Incrementing PART NUMBER on form 4
Finding the right style field in VBA 0

Top