Access Auto Increment

[Jeremy Dove]

I am writing a database where the primary key is going to
be an internal system number such as ITS0000001. I am
trying to get the field to auto increment that field but
i can't figure out how to do it.

Can someone help me with that? Thanks

Jeremy

 

[Allen Browne]

Your data is not atomic. Split it into two fields.
You can select the pair of them in table design view to make the combination
the primary key.

Assuming fields named T for the text part and N for the numeric part, you
would assign the next available number in the BeforeUpdate event procedure
of your form like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.T) Then
Cancel = True
MsgBox "You must supply the T part."
ElseIf IsNull(Me.N) Then
Me.N = Nz(DMax("N", "MyTable", "T = """ & Me.T & """"), 0) + 1
End If
End Sub