mistux Site Admin
Joined: 25 Jun 2004 Posts: 1042 Location: South Bend, Indiana USA
|
Posted: Mon Jan 28, 2008 4:41 pm Post subject: Custom Sequential Number for Each Customer |
|
|
This code will let you create a custom sequential number per each customer starting at 0001 so that each customer will have a 0001. (The number gets padded--leading zeros-- to four places.)
For example:
PAB0001
PGG0001
PAB0002
PAB0003
PGG0002
Code: | Dim strCustAbrev As String
Dim intIncrement As Integer
' P=Pattern
'**Check to see that the customer has an abbreviation
If Len(Me.cbxCustID.Column(2)) < 2 Then '*The third column in the dropdown contains the customer abbreviation
MsgBox "The Customer needs at lease a two letter abbreviation."
Exit Sub
End If
'strCustAbrev = "KM"
strCustAbrev = Me.cbxCustID.Column(2)
'Find the next number and if there is no number start at 1
intIncrement = Nz(DMax("Right([PatternNumber], 4)", _
"T_Patterns", _
"[PatternNumber] Like 'P" & strCustAbrev & "*'"), 0) + 1
'MsgBox "The next number is: P" & strCustAbrev & Format([intIncrement], "0000")
'Stick the new custom number in the correct field
Me.tbxPatternNumber = "P" & strCustAbrev & Format([intIncrement], "0000") |
|
|