Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 5th, 2009, 10:09 AM
Registered User
 
Join Date: Jan 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default AutoNumber for Primary Key

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
 
Old January 5th, 2009, 04:10 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

Look it up at: http://wrox.books24x7.com
 
Old January 5th, 2009, 04:11 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

Look it up at: http://wrox.books24x7.com
 
Old January 5th, 2009, 04:13 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

Look it up at: http://wrox.books24x7.com
 
Old January 5th, 2009, 04:14 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

Look it up at: http://wrox.books24x7.com
 
Old January 6th, 2009, 07:29 AM
Registered User
 
Join Date: Jan 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default RE : AutoNumber for Primary Key

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.
 
Old January 6th, 2009, 10:29 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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.
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old January 7th, 2009, 01:59 AM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default Much easier way...

Code:
Dim lID As Long
Dim sPK As String
sPK = "PEK-" & Right("0000" & lID, 4)
Or the equivalent, as needed in your situation.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Foreign key not updating with Primary key xavier1945 BOOK: Access 2003 VBA Programmer's Reference 2 July 4th, 2007 09:48 PM
recovering an autonumber primary key Loralee Access 10 October 2nd, 2005 12:54 AM
FOREIGN KEY and PRIMARY KEY Constraints junemo Oracle 10 June 15th, 2004 01:00 AM
Autonumber Primary Key JonnyRPI Access 1 June 27th, 2003 10:59 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.