how can i change the numbers so they are my own customer numbers

  • Thread starter Thread starter SomethingMore
  • Start date Start date
Add a text or numeric field to the table, make it required, and make sure
that any foreign key field is the same data type.
 
I want to have my own format for customer and project numbers, how do I do it?
Click to expand...

Use some datatype other than Autonumber, and manage the values and format
yourself.
 
So, is there a way to set it up to automatically assign a customer number.
The format I want is yymm######. Thanks, Patty
 
Patty:

Firstly the data type for the CustomerNumber column will need to be Text if
you want to store the leading zero of the year. It then depends on whether
you want the last six characters of the customer number to start from 000001
each month, or to be unique. For the former, in a form bound to the
Customers table put code along these lines in the form's BeforeInsert event
procedure:

Dim strYearMonth As String
Dim strCriteria as String
Dim varLastNumber as Variant

strYearMonth = Format(VBA.Date,"yymm")
strCriteria = "Left([CustomerNumber],4) = """ & strYearMonth & """"
varLastNumber = DMax("[CustomerNumber]", "[Customers]", strCriteria)

' add 1 to last customer for year/month, if any
If IsNull(varLastNumber) Then
Me.[CustomerNumber] = strYearMonth & "000001"
Else
Me.[CustomerNumber] = strYearMonth & _
Format(Right(varLastNumber,6)+1,"000000")
End If

If the last six characters of the customer number are unique:

Dim strYearMonth As String
Dim varLastNumber as Variant

strYearMonth = Format(VBA.Date,"yymm")
varLastNumber = DMax("[CustomerNumber]", "[Customers]")

' add 1 to last customer if any
If IsNull(varLastNumber) Then
Me.[CustomerNumber] = strYearMonth & "000001"
Else
Me.[CustomerNumber] = strYearMonth & _
Format(Right(varLastNumber,6)+1,"000000")
End If

Note that in a multi-user environment this could case conflicts if two or
more users are adding a new customer record simultaneously. Provided the
CustomerNumber column is indexed uniquely, however, this would raise an error
which could then be handled.

Ken Sheridan
Stafford, England
 
Back
Top