specific auto-numbering

[jerbacher]

Hi everyone,

This is a fairly simple question:
How can I make an autonumber field which numbers PRO-02-0001, PRO-02-0002 etc.?

Any suggestions would be great! Thanks a lot.


Jon

 

[Tim K.]

Will this part, "PRO-02-", change over time? If so, what do they stand for? If not, why do you want the prefix? If you want to display the prefix, just add it when you want to show it only.

If you insist, I will give the code.

 

[jerbacher]

Tim,

PRO-02-0001 stands for and engineering project at the company i work for. 02 represents the year. PRO is simply a prefix they use currently outside of the database which needs to remain. This prefix needs to also be attatched to every record in this table, therefore, I do need the code. Thank you much!

Jon

 

[Tim K.]

Here's the code. I assume your table called tblCustomers with ID field.

Private Sub ID_DblClick(Cancel As Integer)
Dim intMax As Integer
Dim strPrefix As String


strPrefix = "PRO-02-"

If Me.ID = "" Or IsNull(Me.ID) Then
If IsNull(DMax("Val(Mid([ID],8))", "tblCustomers")) Then
Me.ID = strPrefix & "0001"
Else
intMax = DMax("Val(Mid([ID],8))", "tblCustomers")
intMax = intMax + 1
Me.ID = strPrefix & Format(intMax, "0000")
End If

End If

End Sub

 

[RichMorrison]

You wrote
<<
How can I make an autonumber field which numbers PRO-02-0001, PRO-02-0002 etc.?

Any suggestions would be great! Thanks a lot.
>>

Here is a suggestion

DO NOT DO THIS !!!!!!!

You are attempting to create an "intelligent" code numbering system.

THAT IS A VERY VERY BAD IDEA. !!!!!

Store "PRO" as a field called Prefix.
Store the year as a 4-digit field called PartYear or something.
Store the sequence as an integer field called Sequence.

You can DISPLAY the fields in any nice format you want; PRO-02-0002.

DO NOT STORE as some kind of merged string.

RichM