Incrementing a key value

I need to increment a key value by "1" with each new record and the "1" needs
to be a text field so that the format of it can be "L0001", "L0002",.....

Using Access 2003.

I'd use a regular autonumber column and just add the 'L' to the front when
you need to display the key value.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"Eric Johnson" wrote:

I need to increment a key value by "1" with each new record and the "1" needs
to be a text field so that the format of it can be "L0001", "L0002",.....

Using Access 2003.

On Thu, 8 Jan 2009 09:46:43 -0800, Eric Johnson Eric
wrote:

I need to increment a key value by "1" with each new record and the "1" needs
to be a text field so that the format of it can be "L0001", "L0002",.....

Using Access 2003.


If the L is constant and never varies, I'd suggest not storing it in the field
at all. You can instead use an Integer or Long Integer number field with a
Format property of

"\L0000"

to just display the letter and the leading zeroes.

You can increment the ID in various ways. If this is a single user database
then the simplest would be to use a form for data entry, and in its
Beforeinsert event put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Integer
iNext = NZ(DMax("[ID]", "[tablename]")) + 1
If iNext = 10000 Then
Cancel = True
MsgBox "All ID's have been used, shut off the PC and go home", vbOKOnly
Else
Me![ID] = iNext
End If
End Sub

For multiuser databases you need to take precautions against having two users
create new records at the same time - post back with details if you need this.
--

John W. Vinson [MVP]

"John W. Vinson" wrote:
Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Integer
iNext = NZ(DMax("[ID]", "[tablename]")) + 1
If iNext = 10000 Then
Cancel = True
MsgBox "All ID's have been used, shut off the PC and go home", vbOKOnly
Else
Me![ID] = iNext
End If
End Sub


I was afraid it was going to get complex. How do I do the incrementing in a
table...no form? If I can't do this, I'll have to look for another solution
as there are no forms...embedded tables and reports only.

Eric Johnson

"...MsgBox "All ID's have been used, shut off the PC and go home",
vbOKOnly..."

I like that message, not sure I'll use it anywhere, could get me 'fired'!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"John W. Vinson" wrote in message
...
On Thu, 8 Jan 2009 09:46:43 -0800, Eric Johnson Eric
wrote:

I need to increment a key value by "1" with each new record and the "1"
needs
to be a text field so that the format of it can be "L0001", "L0002",.....

Using Access 2003.


If the L is constant and never varies, I'd suggest not storing it in the
field
at all. You can instead use an Integer or Long Integer number field with a
Format property of

"\L0000"

to just display the letter and the leading zeroes.

You can increment the ID in various ways. If this is a single user
database
then the simplest would be to use a form for data entry, and in its
Beforeinsert event put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Integer
iNext = NZ(DMax("[ID]", "[tablename]")) + 1
If iNext = 10000 Then
Cancel = True
MsgBox "All ID's have been used, shut off the PC and go home", vbOKOnly
Else
Me![ID] = iNext
End If
End Sub

For multiuser databases you need to take precautions against having two
users
create new records at the same time - post back with details if you need
this.
--

John W. Vinson [MVP]

On Thu, 8 Jan 2009 11:18:09 -0800, Eric Johnson
wrote:

"John W. Vinson" wrote:
Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Integer
iNext = NZ(DMax("[ID]", "[tablename]")) + 1
If iNext = 10000 Then
Cancel = True
MsgBox "All ID's have been used, shut off the PC and go home", vbOKOnly
Else
Me![ID] = iNext
End If
End Sub


I was afraid it was going to get complex. How do I do the incrementing in a
table...no form? If I can't do this, I'll have to look for another solution
as there are no forms...embedded tables and reports only.

Eric Johnson

If you're doing data entry in tables... DON'T.

That is not their function, and they are of very, very limited utility. In
particular tables have no events, so you cannot program things like
increments.

What do you mean by "embedded tables"? How do you do your data entry? What's
your aversion to forms: they are *essential* to any production Access
application?
--

John W. Vinson [MVP]