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

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:
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.

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.

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

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

oops...the line:

Me![ID] = CInt(strID)

should be:

Me![ID] = CLng(strID)

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

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

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

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