auto gen no. based on fields

[Guest]

Is it possible to create an automatic numbering system based on the first
letter of certain fields, with an auto number attached (to the new reference
field) on each new record entered? If so, not being a programmer could
someone please explain how to do this? I have read up on the truncate code
and the count, but now need to understand the where and how.. to clarify
further, I have a table called QuoteList with the following fields
CompanyName, Category, City. The quote number is based on the first letter
of each field eg Whirlpool, Cooking, Saltillo eg WCS. Then it requires the
next number attached.

Any assistance is much appreciated.

 

[Guest]

Is the sequential number based on the first letter combinations or does it
run for the entire table. for example:
WCS001
WCS002
WCS003
ABC001
ABC002
ABC003
ABC004

Or is it
WCS001
WCS002
ABC003
WCS004
ABC005

 

[David C. Holley]

YES....BUT.... how are you ensuring unqiueness in your records? Are the
records

WCS0001
WCS0002
WCS0003
WCS0004

or some other format? Do you have a *SPECIFIC* need to identify a
particular quote as being the 4th quote issued to Whirlpool? or Do you
simply have a need to identify a quote as being issued to Whirlpool
regardless of sequence?

David H

 

[David C. Holley]

Didn't I just ask that?

Is the sequential number based on the first letter combinations or does it
run for the entire table. for example:
WCS001
WCS002
WCS003
ABC001
ABC002
ABC003
ABC004

Or is it
WCS001
WCS002
ABC003
WCS004
ABC005


:

 

[Guest]

As a new quote is added at the end of the table would it not be based on the
first letter combinations but is stored randomly in the automatic reference
field? these are stored in one table not separate tables per new combination
- is this difficult?

 

[Guest]

Hi David and Klatuu, thanks for your quick responses. The records entered
into the table are unique with the Auto Numbering. There is a need to number
each new quote, and yes ideally they would be as per your example.

 

[Guest]

I did not get a clear answer to whether the sequence number is over the
entire recordset or unique to a 3 letter sequence so I will give examples of
both:

This version returns the next number regardless of the prefix. Also note
that since I don't know how many digits the number needs to be, I used a
length of 4 digits.

Function GetNextNumber() As String
Dim lngSeq as Long
Dim strSequenceNumber as String
Dim varLastNumber as Variant
Dim strPrefix as String

strPrefix = Left(Me.CompanyName, 1) & Left(Me.Category, 1) &
Left(Me.City, 1)
varLastNumber = DMax(Right("[QUOTE_ID], 4)", "MyTableName")
If IsNull(varLastNumber) Then
varLastNumber = 1
Else
varLastNumber = varLastNumber + 1
End If
GetNextNumber = strPrefix & Format(varLastNumber, "0000")
End Function

This version returns a number sequenced by the 3 letter prefix:

Function GetNextNumber() As String
Dim lngSeq as Long
Dim strSequenceNumber as String
Dim varLastNumber as Variant
Dim strPrefix as String

strPrefix = Left(Me.CompanyName, 1) & Left(Me.Category, 1) &
Left(Me.City, 1)
varLastNumber = DMax(Right("[QUOTE_ID], 4)", "MyTableName", _
"Left([QUOTE_ID], 3) = '" & strPrefix & "'")
If IsNull(varLastNumber) Then
varLastNumber = 1
Else
varLastNumber = varLastNumber + 1
End If
GetNextNumber = strPrefix & Format(varLastNumber, "0000")
End Function

 

[Guest]

You asked it after I did.
Is this great minds thinking alike?

 

[David C. Holley]

Yes, nice to know I'm not alone.

You asked it after I did.
Is this great minds thinking alike?

:

 

[Guest]

Thank you Klatuu, the sequence number is unique to a 3 letter sequence. I
appreciate your help. Will let you know how I get on. Cheers
--
Jo

I did not get a clear answer to whether the sequence number is over the
entire recordset or unique to a 3 letter sequence so I will give examples of
both:

This version returns the next number regardless of the prefix. Also note
that since I don't know how many digits the number needs to be, I used a
length of 4 digits.

Function GetNextNumber() As String
Dim lngSeq as Long
Dim strSequenceNumber as String
Dim varLastNumber as Variant
Dim strPrefix as String

strPrefix = Left(Me.CompanyName, 1) & Left(Me.Category, 1) &
Left(Me.City, 1)
varLastNumber = DMax(Right("[QUOTE_ID], 4)", "MyTableName")
If IsNull(varLastNumber) Then
varLastNumber = 1
Else
varLastNumber = varLastNumber + 1
End If
GetNextNumber = strPrefix & Format(varLastNumber, "0000")
End Function

This version returns a number sequenced by the 3 letter prefix:

Function GetNextNumber() As String
Dim lngSeq as Long
Dim strSequenceNumber as String
Dim varLastNumber as Variant
Dim strPrefix as String

strPrefix = Left(Me.CompanyName, 1) & Left(Me.Category, 1) &
Left(Me.City, 1)
varLastNumber = DMax(Right("[QUOTE_ID], 4)", "MyTableName", _
"Left(