If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Incrementing a key value
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. |
#3
|
|||
|
|||
Incrementing a key value
|
#4
|
|||
|
|||
Incrementing a key value
"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 |
#6
|
|||
|
|||
Incrementing a key value
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] |
Thread Tools | |
Display Modes | |
|
|