Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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
Reply With Quote
  #2 (permalink)  
Old January 5th, 2009, 04:10 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #3 (permalink)  
Old January 5th, 2009, 04:11 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #4 (permalink)  
Old January 5th, 2009, 04:13 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #5 (permalink)  
Old January 5th, 2009, 04:14 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #6 (permalink)  
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.
Reply With Quote
  #7 (permalink)  
Old January 6th, 2009, 10:29 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #8 (permalink)  
Old January 7th, 2009, 01:59 AM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 05:57 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.