Help!!! Creating a Unique ID (primary key) in a database

[Ausburgh]

Help!!!

How do I create a unique ID field?

[YY - MM - (Autonumber)] example: 04-04-9458

I already tried "[yy]& - &[mm]& - &[Fieldname]" putting each element of the ID in separate field. Hence trying to combine them into another field.


Thanks in advance

 

[originalxavier]

Why isn't the "Autonumber" field sufficient since that will always be unique?

Xavier

----------------------------------------
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far the Universe is winning."
Rick Cook
----------------------------------------

 

[TheAceMan1]

How are ya Ausburgh . . . . .

Unless you have your own method of generating [blue]Unique[/blue] ID's, autonumber is not bad at all. YY & MM are easily found items otherwise. Besides, its better if ID's are numeric.

See Ya! . . . . . .

 

[Ausburgh]

Thanks for your response.

This is a project for an archeological center - they're are trying to migrate their old database (Hypercard) into SQL-server 2000 while keeping the structure of the way they are used to doing things.

They decided that it will be fine if we provided an Access front-end with the back-end being SQL.They want to be able to search / reference by year and month and the auto generated number. They get thousands of requests per month so the Autonumber is only unique for a month. So combining the YY + MM (both of the current year) + an autonumber - they will alway have a unique # (that's the way they've always done it and they want to keep it that way.

sorry for the long explanation!

 

[TheAceMan1]

Ausburgh . . . . . Understood! . . . . .

Just one question . . . . .

Is it necessary to have the dashes between values?

If so, then the ID has to be a string (which I frown upon).

If not . . . . ditto . . . . perfer converting to numeric!

See Ya! . . . . . .

 

[Ausburgh]

Unfortunately, it has to have the dashes. We even thought of (on the form - data Entry) creating at least 2 fields (1. for YY-MM and the other for the autonumber) but that didn't work out too well.

 

[TheAceMan1]

Ausburgh . . . No Problem . . . I'll get back to you with the code. Understand, when assigning new records, the PrimaryKey has to be the first entry!

See Ya! . . . . . .

 

[Ausburgh]

Thanks for all your help TheAceMan1!

 

[KenReay]

Hi

You will make life much easier for yourself if you define the PK in this case a three columns in the table

So

intYY Integer )
intMM Integer ) Prime Key
lngId Autonumber )

you can populate this very easily without code by setting the default value of intYY to Val(Right(Format(Year(Date()),"0000"),2)) and the default value of intMM to Month(Date()), the autonumber will populate automatically anyway

You can show the three columns as a single value to the user by simply concatinating the columns and including the "-" at that stage


Similarly, you can allow users to enter keys as a single value including "-", but you can strip these out before performing the lookup. Given that the key is a number string, it seems unlikely that users will be keying it in anyway, presumably the will be choosing it from drop down lists, in which case uyou can make it look as you wish, no need for them to know how it is actually stored

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[TheAceMan1]

OK Ausburgh . . . . .

Sorry to get back so late.

First, you need to add and [blue]autonumber[/blue] field to your table. Call it [purple]AutoID[/purple]. Be aware, the AutoID will be working [blue]hidden in the background[/blue], invisible to the user. Just make sure you include it the [blue]RecordSource[/blue] for the form. It does'nt have to be on the form, but it should appear in the [blue]Field List[/blue].

Now, enter the following code in the forms code module (you have to replace [purple]purple[/purple]:

[blue]Public Sub InsertID()
   Dim YY As String, MM As String
   Dim hldNow As Date, Pad As String
   
   If Me.NewRecord Then
      hldNow = Int(Now())
      YY = Format(hldNow, "YY")
      MM = Format(hldNow, "MM")
      Pad = String(8 - Len(Me!AutoID), "0")
      Me![purple][b]YourIDname[/b][/purple] = YY & "-" & MM & "-" & Pad & LTrim(Str(Me!AutoID))
   End If
   
End Sub[/blue]

Note the format is set as follows: [purple]YY-MM-12345678[/purple], padded with zeros to a full eight characters. This allows for increasing autonumber.[purple]Autonumber does not start over for each month[/purple]. This may be a consideration to you. To change the length of the autonumber, just change the [purple]8[/purple] in the code above to the desired length.

Now. since autonumber will not be generated until data is entered into a new record, the routine hase to be triggered this way. So in any/all controls requiring data, add the following call in the [blue]AfterUpdate[/blue] event of the control:

[blue]   Call InsertID[/blue]

Thats it for now. Give it a spin and let me know . . . .

See Ya! . . . . . .

 

[KenReay]

Hi

If you really want to do it the hard way, something like:

Option Compare Database
Option Explicit
Public Function NextString(strPrimeKey As String, strTableName As String, strPrefix As String, intKeyLength As Integer) As String
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim strSQL As String
'
Set Db = CurrentDb()
If Len(strPrefix) = 0 Then
strSQL = "SELECT " & strPrimeKey & " FROM " & strTableName & " ORDER BY " & _
strPrimeKey & " DESC;"
Else
strSQL = "SELECT " & strPrimeKey & " FROM " & strTableName & " " & _
"WHERE Left(" & strPrimeKey & "," & Len(strPrefix) & ") = " & Chr(34) & _
strPrefix & Chr(34) & " ORDER BY " & _
strPrimeKey & " DESC;"
End If
Set Rs = Db.OpenRecordset(strSQL, dbOpenForwardOnly)
If Rs.RecordCount < 1 Then
NextString = strPrefix & Format(1, String(intKeyLength, "0"))
Else
NextString = strPrefix & Format(Val(Mid(Rs(strPrimeKey), Len(strPrefix) + 1)) + 1, String(intKeyLength, "0"))
End If
Rs.Close
Set Rs = Nothing
Set Db = Nothing
End Function
Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click

DoCmd.Close

Exit_cmdExit_Click:
Exit Sub

Err_cmdExit_Click:
MsgBox Err.Description
Resume Exit_cmdExit_Click

End Sub

Private Sub cmdSave_Click()
On Error GoTo Error_cmdSave
DoCmd.RunCommand acCmdSaveRecord
Exit_cmdSave:
Exit Sub
Error_cmdSave:
Select Case Err.Number
Case 3022
strPrimeKey = NextString("strPrimeKey", "tblTestString", "", 7)
Resume
Case Else
MsgBox Err.Number & " " & Err.Description
Resume Exit_cmdSave
End Select
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(strPrimeKey) Then
strPrimeKey = NextString("strPrimeKey", "tblTeststring", Format(Date, "YY-MM"), 2)
End If
End Sub
Private Sub cmdNew_Click()
On Error GoTo Err_cmdNew_Click


DoCmd.GoToRecord , , acNewRec

Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description
Resume Exit_cmdNew_Click

End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 3022
lngPrimeKey = NextNumber("lngPrimeKey", "tblTest", Format(Date, "YY/MM"), 2)
Response = acDataErrContinue

Case Else
MsgBox "Error " & DataErr & " " & Error(DataErr)
Response = acDataErrContinue
End Select
End Sub

will do it and should work ok in Multi user environment

if you need explanation post back

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MichaelRed]

see faq700-184




MichaelRed
mlred@verizon.net

 

[Ausburgh]

Thanks guys for all your help. I'll report back when my team reconvenes next week.


Ausburgh