AutoNumber for Primary Key

Yusrina · January 5th, 2009, 10:09 AM

Hi,

I'm using 2003 Access. I have build one sistem using access. One of my table using ID for primary key. I have enter some format for this id such as "PEK-0001". At design view i put PEK-@@@@;@ as format. But unfornately. I have to remember and key in the Id sequently at form view.

can the Id become autonumber such "PEK-0001" to "PEK-0002" by automatically at the form view.

Can someone give some advise.

Thank

mmcdonal · January 5th, 2009, 04:10 PM

Create an autonumber field to build the second half of your string. Hide this field on the form. Then on the After Insert event of the form, do some code to concatenate the String you want for your PK: like:

tblYourTable
PK = string
ID = autonumber

After Insert:

Dim lID As Long
Dim sPK As String

lID = Me.ID

Select Case lID
Case >10
sPK = "PEK-000" & lID
Case >100
sPK = "PEK-00" & lID
Case >1000
sPK = "PEK-00" & lID
Case >10000
sPK = "PEK-0" & lID
End Select

Me.PK = sPK

I hope you're notplanning on having more than 10,000 records.

Did that help?

mmcdonal · January 5th, 2009, 04:11 PM

Sorry, that should be

Select Case lID
Case <10
sPK = "PEK-000" & lID
Case <100
sPK = "PEK-00" & lID
Case <1000
sPK = "PEK-00" & lID
Case <10000
sPK = "PEK-0" & lID
End Select

Of course.

mmcdonal · January 5th, 2009, 04:13 PM

Yikes, I am very tired today. I just use the autonumber myself. You can't do what I said since it will always stop at the first case, so order them the other way, like this:

Select Case lID
Case >10000
sPK = "PEK-0" & lID
Case >1000
sPK = "PEK-00" & lID
Case >100
sPK = "PEK-000" & lID
Case >10
sPK = "PEK-000" & lID
End Select

mmcdonal · January 5th, 2009, 04:14 PM

Okay, I am going to bed. Backwards won't work either. Sorry. I will try again after more sleep.

Yusrina · January 6th, 2009, 07:29 AM

Dear Donal,

I have tried, the auto number is successful but still left out the front ID which is supposed "PEK-0001" but the autonumber goes to 1 only...

I'm very appreciated if you can find another way. Thanks a lot. You very helping.

mmcdonal · January 6th, 2009, 10:29 AM

Sorry about yesterday, you can do this:

Code:

Dim lID As Long
Dim sPK As String
lID = 111
Select Case lID
    Case 1 To 9
        sPK = "PEK-000" & lID
    Case 10 To 99
        sPK = "PEK-00" & lID
    Case 100 To 999
        sPK = "PEK-0" & lID
    Case 1000 To 9999
        sPK = "PEK-" & lID
End Select
 
MsgBox sPK

I tested this and it works for various values from 1 to 9999. This will work on the After Insert event, I think. Are you using a save button? If so, put it on that button.

Old Pedant · January 7th, 2009, 01:59 AM

Code:

Dim lID As Long
Dim sPK As String
sPK = "PEK-" & Right("0000" & lID, 4)

Or the equivalent, as needed in your situation.