Creating Primary Key Value

[PJB]

I'm using an Access database in my application, for adding new records i want to automatically create the number used in my primary key(ie:ProductID) but i don't want to use the Auto Number feature in Access how could i simply add 1 to the last record in the database and have it displayed as the new ProductID and let the user input the rest of the information?

I'm using a DataGrid with it's Datasource = DataEnvironment1

[Stevie]

I create my own primary keys for my Employee table. Wgat I do is take the first 3 letters of the Surname and add on a four digit number starting from 0001.

All I do is use a loop with a counter starting at 1, which checks to see if a Employee exists with that key. Each time through the loop I call the following function with the value of the counter, then check to see if the returned key exists.


Private Function CalculateEmployeeId(EmployeeNumber As Integer) As String
'***************************************************************************
'Purpose: Calculates the new Employee Id.
' Employee Id made from first 3 chars of surname, and a 4 digit number.
'Parameters: EmployeeNumber - The number to append to the first 3 chars.
'Returns: String - The Employee Id.
'***************************************************************************

Dim strSurname As String
Dim strFirstThree As String
Dim strEmpNumber As String
Dim strFourDigits As String

' Get the first 3 chars of the surname
strSurname = txtSurname.Text
strFirstThree = Left$(strSurname, 3)
strFirstThree = UCase(strFirstThree)

' Add a Zero to the chars for each char short of the 3
If Len(strFirstThree) = 1 Then
strFirstThree = strFirstThree & "00"
ElseIf Len(strFirstThree) = 2 Then
strFirstThree = strFirstThree & "0"
End If

' Make the EmployeeNumber into 4 digits
strEmpNumber = CStr(EmployeeNumber)

' Add a Zero to the string for each zero short of the 4 digits
If Len(strEmpNumber) = 1 Then
strEmpNumber = "000" & strEmpNumber
ElseIf Len(strEmpNumber) = 2 Then
strEmpNumber = "00" & strEmpNumber
ElseIf Len(strEmpNumber) = 3 Then
strEmpNumber = "0" & strEmpNumber
End If

' Return the employee id
CalculateEmployeeId = strFirstThree & strEmpNumber

End Function


Hope this helps :)

[PJB]

I'll try adapting it to what i'm doing and see what happens

[PJB]

I think what I'm looking is something that will simply read the last entry in my primary key(a number) add 1 to it and make it the primary key for my new entry.
I'm not quite sure if I'm explaining it right, but then again if I knew what I was talking about I wouldn't be asking silly questions :)

[ttingen]

Dim iPKID as Integer
Dim iNewPKID as Integer

'Open the recordset

With rs
.MoveLast
iPKID = .Fields!PKID
iNewPKID = iPKID + 1
.AddNew
.Fields!PKID = iNewPKID
.Fields!PRODUCT_DESCRIPTION = sProductDescription
'Other fields in the db
.Update
End With

rs.Close

[Chuck Sweet]

More generally, write this function and keep it in a separate module to alleviate this problem for all time:

Public Function GetIndexNumber(Table As String, IndexField As String, Connect As ADODB.Connection) As Long
Dim rsRecords As New ADODB.Recordset
'only pulling one record, you can just get the info you want
rsRecords.Open "select top 1 * from " & Table & " order by " & IndexField & " desc", Connect, adOpenStatic
'if there are no records, then ID = 1
If rsRecords.EOF Then
GetIndexNumber = 1
Else
'otherwise it equals highest value + 1
GetIndexNumber = CLng(rsRecords.Fields(IndexField)) + 1
End If
'clean up
rsRecords.Close
Set rsRecords = Nothing
End Function

[JHausmann]

Originally posted by ttingen

Dim iPKID as Integer
Dim iNewPKID as Integer

'Open the recordset

With rs
.MoveLast
iPKID = .Fields!PKID
iNewPKID = iPKID + 1
.AddNew
.Fields!PKID = iNewPKID
.Fields!PRODUCT_DESCRIPTION = sProductDescription
'Other fields in the db
.Update
End With

rs.Close



Nicely done. Only problem (and it may not be a problem) I see is that it could re-use PKIDs if some are deleted. If you have to ensure that only one number is ever used, you should store the value in a field in an unrelated table, one that isn't affected by deletes.

[ttingen]

Good catch JHausmann.

I didn't think about that. That's one of the things that would have been quickly written and 6 months after the app goes into production you get a call about it. Oops!