Custom ID's as Primary Key

[provoked]

Hi guys,

Cool forum, my first post. Just wondering if anyone can help me with this, as ive searched the forums and have found nothing helpful.

I have a small database, which a table called family, (a family enrolls student and pays fee kind of DB). Anyway, ive created some VBA code to create a key that consists of the first three letters of their surname, the year (05) and an auto generated number. Ive used this code.
-----------------------------------------------------------------------
Private Sub family_name_AfterUpdate()
Dim randNumb As Integer

randNumb = Int((999 - 100 + 1) * Rnd + 100)
family_id = Left(family_name, 3) + Right(Date, 2) + CStr(randNumb)
End Sub
-----------------------------------------------------------------------

My problem is, that even though a duplicate key is near impossible, i dont like the possibility of it occuring, therefore i need to check to see if that ID currently exists. Ive tryed the following code, which allowed duplicates.

-----------------------------------------------------------------------
Do While match = True
For i = 0 To rst.EOF
If (temp = rst!family_id) Then
match = True
rst.MoveFirst
i = 0
randNumb = Int((6 - 1 + 1) * Rnd + 1)
temp = Left(family_name, 3) + Right(Date, 2) + CStr(randNumb)
Else
rst.MoveNext
End If
If (i = rst.EOF = True) Then
match = False
Exit Do
End If
Next i
Loop

family_id = temp
------------------------------------------------------------------------

Im now stuck. Im not just asking for answers or my problem solved, even if someone can give me some direction?

Any help would be greatly appreciated.

Cheers!

 

[Meltdown]

Hi provoked, take a look at the attachment in this thread http://www.access-programmers.co.uk/forums/showthread.php?t=75516 , ...another way of creating a unique ID.

If implemented properly there should be no need to check for duplicates.

 

[Argonak]

i did something similiar to what you're doing, and handled this by recursivly calling the same function when i hit a duplicate.

Function insert_record(contractor_id_mTBL As Long, job_name_mTBL As String, contract_number_mTBL As String, region_mTBL As String, labor_mTBL As String, discipline_mTBL As String, pm_sub_mTBL As String, category_mTBL As String, subcategory_mTBL As String) As Long
Dim line_number As Long
Randomize
line_number = Int((99999999) * Rnd + 0)
DoCmd.SetWarnings False
matchnumber = DCount("[Id_number]", "mTBL_main_info", "[Id_number] = " & line_number & "")
    If matchnumber = 0 Then
       mysqlstmt = "INSERT INTO mTBL_main_info([Id_number], [contractor_id_mTBL], [job_name_mTBL],[contract_number_mTBL], [region_mTBL], [labor_mTBL], [discipline_mTBL], [pm_sub_mTBL], [category_mTBL], [subcategory_mTBL]) VALUES (" & line_number & ", " & contractor_id_mTBL & ", '" & job_name_mTBL & "', '" & contract_number_mTBL & "', '" & region_mTBL & "', '" & labor_mTBL & "', '" & discipline_mTBL & "', '" & pm_sub_mTBL & "', '" & category_mTBL & "', '" & subcategory_mTBL & "');"
       DoCmd.RunSQL mysqlstmt
    Else
       line_number = insert_record(contractor_id_mTBL, job_name_mTBL, contract_number_mTBL, region_mTBL, labor_mTBL, discipline_mTBL, pm_sub_mTBL, category_mTBL, subcategory_mTBL)
    End If
DoCmd.SetWarnings True
insert_record = line_number
End Function

since i use 99999999 as my cap, odds of hitting a duplicate aren't very high. However, my probable database size will be quite large and so I felt it was better to be safe than sorry.

 

[MarkK]

What I would do is create a function that always returns a unique ID, something along these lines...

Function GetID(Optional surName as string, Optional strID as string) As String
  Dim rst as DAO.Recordset
  Dim i as Integer
  Dim s as String

  If strID = "" Then
    [COLOR=Green]'construct the initial strID[/COLOR]
    strID = Left(surName, 3) & Right(Date, 2) & "00"
  Else
[COLOR=Green]    'get the counter on the end of the string
[/COLOR]    i = CInt(Right(strID, 2))
[COLOR=Green]   'increment and append the counter to the ID
[/COLOR]   strID = Left(strID, 5) & Format(i + 1, "00")
  End If

[COLOR=Green]  'check for unique, so open an r-set looking for the new strID
[/COLOR]  Set rst = currentdb.openrecordset( _
    "SELECT yourID FROM yourTable WHERE yourID = '" & strID & "'")
  With rst
    If .EOF then
[COLOR=Green]      'strID was not found, and is therefore unique
[/COLOR]      GetID = strID
    Else
[COLOR=Green]      'strID was found, so recall GetID so counter can be incremented
[/COLOR]      GetID = GetID(, strID)
    End If
    .Close
  End With

End Function