ID field

  • Thread starter Thread starter leaf
  • Start date Start date
L

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
 
-----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
.
Click to expand...
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.
 
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.
Click to expand...

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
 
Back
Top