Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
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 September 23rd, 2003, 03:36 PM
Registered User
 
Join Date: Sep 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Beginner - Access '02 Autonumber sequencing logic?

Hello all,

I've been struggling with this for the past few days....I need a guru's advice. Please help! (thanks in advance)

Here's my situation:
I am building a Purchase Order Database using the 2002 version of Access. The business requirement says the PO # must start with '5'. So the PO number after 5999 cannot be 6000, but 50000. After 59999, it cannot go to 60000 but to 500000 (you get the pattern...)

Now, I have the PO# as an Autonumber and Primary Key in my "PurchaseOrders" table. Should I put a "POID" (as an AutoNumber field) and a "PO Number" (as a Number field) with some kind of algorithm/code? What do you suggest is the best way to handle this logic?

In all this, I am a beginner in Access and have not yet learned all the tricks. However, I need to get this DBMS up and running in less than 2 weeks!! SO I very much need assistance...

THANKS MUCH!
Mita

 
Old September 23rd, 2003, 05:30 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Mita

As it stands, your database breaks the 7th commandment of Access database design (http://www.mvps.org/access/tencommandments.htm!

So, your second idea may be the way to go.

Another possibility would be to use your autonumber field, but format in a way that is acceptable whenever it's presented to the user. Putting the following in the format property of a text box control, bound to your autonumber field, would give you 100 million unique PO numbers, all of them beginning with 5:
Code:
\500000000
This will complicate matters when you need to filter or search by your PO numbers, as you will have to strip off the 5 and 'unformat' the supplied number. But it may be the simpler solution...?


Brian Skelton
Braxis Computer Services Ltd.
 
Old September 24th, 2003, 12:23 AM
Friend of Wrox
 
Join Date: Sep 2003
Posts: 171
Thanks: 0
Thanked 1 Time in 1 Post
Default

First of all if you're planning on a half million records you may consider something other than MS Access which will ultimately slow down at that amount. Perhaps MsSql.

Definately do not make the PO Number field your primary key. Have a unique id field (PoId like you said), then perhaps you can capture the unique id at order entry and append a 5 to the value of it and store it in a PO Number field. It'll be easier to manage later and your user will not actually ever know what the unique id is, which is a good thing.


 
Old September 24th, 2003, 10:01 AM
Registered User
 
Join Date: Sep 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Braxis and DaveGerard, Thank you both for your inputs.

I definitely don't want my DB to break any Access commandment, so I'm actually leaning more towards having both fields - one as an autoincrement and one with a custom algorithm.

DaveGerard: I am not planning on a half a million records by the way It's just that the last PO # currently printed on paper is 502000. I would need to start the PO's at 502001. Plus, my deptmt currently processes about 1500 PO/year only - so it's going to be a long time before we get to the half a million record mark - and hopefully by then, we've moved up to a better DBMS/Method.

Anyone suggestion on what the VBA algorithm should look like and how/where to actually implement it? On the form "on open"? Pls help.

Thanks a lot again!
Mita



 
Old September 24th, 2003, 03:21 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Mita,

This should get it for ya'. Just be sure your ID field is a number data type so the conversions work. You need to store your ID as a number so that the field remains sorted correctly. I just bound a form to tblPurchaseOrdsers. Paste this code behind the bound form.

Private Sub Form_BeforeInsert(Cancel As Integer)

   Dim strID As String
   Dim lngID As Long

   lngID = DMax("[ID]", "tblPurchaseOrders") + 1

   Select Case lngID
      Case 60000
         lngID = 0
         strID = "5" & Format(CStr(lngID), "00000")
         Me![ID] = CInt(strID)
         Me![txtID].Requery
      Case 6000
         lngID = 0
         strID = "5" & Format(CStr(lngID), "0000")
         Me![ID] = CLng(strID)
         Me![txtID].Requery
     Case Else
         Debug.Print "ID: " & lngID
         Me![ID] = lngID
         Me![txtID].Requery
     End Select

End Sub

HTH,

Bob

 
Old September 24th, 2003, 03:23 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

oops...the line:

Me![ID] = CInt(strID)

should be:

Me![ID] = CLng(strID)


 
Old September 24th, 2003, 03:50 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

You should also lock/disable the ID field on the form so focus is set at the first textbox you'll be entering info in. Typing in any textbox will fire the BeforeInsert event and auto-generate the next ID number.

Bob

 
Old September 24th, 2003, 05:09 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Actually, it turns out all you need is:

Private Sub Form_BeforeInsert(Cancel As Integer)

   Dim strID As String
   Dim lngID As Long

   lngID = DMax("[ID]", "tblPurchaseOrders") + 1

   Select Case lngID
     Case 6000
         strID = "50000"
         Me![ID] = CLng(strID)
         Me![txtID].Requery
    Case 60000
         strID = "500000"
         Me![ID] = CLng(strID)
         Me![txtID].Requery
     Case Else
         Me![ID] = lngID
         Me![txtID].Requery
     End Select

End Sub

Also, on the matter of primary keys: I've begun a careful reevaluation of my "always use autonumbers" (i.e., surrogate keys) as primary keys position. Fact of the matter is you can use your Purchase Order ID as an intelligent (semantic, "carrying business meaning") primary key. Intelligent keys are a common practice in production systems, and there are some very articulate advocates of them out there (noteably Joe Celko). I had an interesting e-mail exchange with Mr. Celko recently in which he makes the case that surrogate keys are really carry-overs from the days of sequential file processing in the mainframe world, but that they make poor relational keys, that is, they aren't helpful in modeling the relational attributes of entities in a relational database. I know its a huge debate, and I'm not prepared to argue either side of it, so I'll just hold the middle ground for now and say that for every voice decrying the use of intelligent keys in your relational tables, there is another voice parading the intelligent key banner just as proudly. I'll leave it at that for now. Choose at your own peril. As far as I can tell, neither choice is categorically "wrong".

Bob

 
Old September 24th, 2003, 05:53 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

yikes! its even simpler! no string necessary. i'll go away now...:)

Private Sub Form_BeforeInsert(Cancel As Integer)

   Dim lngID As Long

   lngID = DMax("[ID]", "tblPurchaseOrders") + 1

   Select Case lngID
      Case 60000
         Me![ID] = 500000
         Me![txtID].Requery
      Case 6000
         Me![ID] = 50000
         Me![txtID].Requery
     Case Else
         Me![ID] = lngID
         Me![txtID].Requery
     End Select

End Sub

 
Old September 25th, 2003, 10:10 AM
Registered User
 
Join Date: Sep 2003
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Bob,

Thanks so much for all your input!
I haven't quite gotten it to work yet though...No clue as to why!
Let me see what else I can try...

Mita







Similar Threads
Thread Thread Starter Forum Replies Last Post
Autonumber in Microsoft Access ozzii Classic ASP Databases 1 January 17th, 2007 04:08 PM
autonumber problem in Access Pat Crotty Access VBA 2 September 6th, 2006 10:37 AM
Access Autonumber chall90909 Access ASP 1 November 21st, 2004 10:26 AM
Autonumber on Access 2002 pdunning Access 1 August 23rd, 2004 10:33 AM
Mixing Data access logic and business logic polrtex BOOK: Professional Jakarta Struts 0 December 15th, 2003 07:19 PM





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