ID field

[leaf]

Hello,

I would like to have my ID as CAM00001 and increment by
one automatically. The IDs will be unique. How do I use
VBA codes to create this ID? This id will also be the
primary key.

Thank you very much.

leaf

 

[tina]

-----Original Message-----
Hello,

I would like to have my ID as CAM00001 and increment by
one automatically. The IDs will be unique. How do I use
VBA codes to create this ID? This id will also be the
primary key.

Thank you very much.

leaf
.

Public Function isID() As String

On Error GoTo handle_error

Dim strMax As String, lngNum As Long

strMax = DMax("ID", "Table1")

lngNum = Right(strMax, 5)

isID = "CAM" & Format(lngNum + 1, "00000")

handle_error:
If Err.Number = 94 Then
strMax = 0
Resume Next
End If

End Function

this should work correctly, unless/until you 1) add more
than 99,999 records over the lifetime of the table or 2)
change the alphabetic prefix from "CAM" to something else.
because ID is a text field in the table, the DMax function
is returning the last record after an Ascending sort, not
an actual maximum NUMBER.

 

[Wayne Gillespie]

Public Function isID() As String

On Error GoTo handle_error

Dim strMax As String, lngNum As Long

strMax = DMax("ID", "Table1")

lngNum = Right(strMax, 5)

isID = "CAM" & Format(lngNum + 1, "00000")

handle_error:
If Err.Number = 94 Then
strMax = 0
Resume Next
End If

End Function

this should work correctly, unless/until you 1) add more
than 99,999 records over the lifetime of the table or 2)
change the alphabetic prefix from "CAM" to something else.
because ID is a text field in the table, the DMax function
is returning the last record after an Ascending sort, not
an actual maximum NUMBER.

I didn't see the start of this thread but you could set the DefaultValue of the
ID control to -
="CAM" & Format(Nz(DMax("CLng(Right([MyID],5))","tblMyTable")),"00000")


Wayne Gillespie
Gosford NSW Australia