numbering in a form

[rrupp]

I have read many posts but not understanding if this is possible.

Can I create a form so that one field is a auto-number like 09-001, then the
next is 09-002, 09-003 and so on?

I use Access 2003.

Thanks.

 

[John W. Vinson]

I have read many posts but not understanding if this is possible.

Can I create a form so that one field is a auto-number like 09-001, then the
next is 09-002, 09-003 and so on?

I use Access 2003.

Thanks.

This kind of "intelligent key" should be used only if it's required for
compatibility with a strongly established paper system. Storing data (such as
a year, which I presume is the 09).

That said...

You can use the Form's BeforeInsert event. Assuming your table is named
MyTable and this field is MyID, and it's a Text field, you can put code like
this in the form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strLast As Variant
Dim iNext As Integer
strLast = DMax("MyID", "MyTable", "MyID LIKE " & Format(Date, "yy") & "*")
If IsNull(strLast) Then
iNext = 1
Else
iNext = Val(Mid(strLast, 3)
If iNext >= 999 Then
MsgBox "No more IDs can be assigned, go home until New Years Day", vbOKOnly
Else
iNext = iNext + 1
End If
Me!MyID = Format(Date, "yy") & Format(iNext, "000")
End Sub

 

[rrupp]

Thank you very much for taking the time to answer my question and send me the
code. This will do it.

Thanks again!