running serial number - different group of products

[lota]

i am in the middle of developing a barcode recording db. User will input
product code, quantity and serial number (SN). There are 9 products, where
each product has its OWN SETof serial number.

snenario: FIRST INPUT
product A, 5 units, starting SN -> SN0001, ending SN0005
product B, 2 units, starting SN -> SN0001, ending SN0002
product E, 7 units, starting SN -> SN0001, ending SN0007

SECOND INPUT
product A, 3 units, starting SN -> SN0006, ending SN0008 (system will auto
generate the starting SN for product A, which is "SN0006", and when the
quantity is input, which is "3" here, system will generate the ending SN for
this record as "SN0008")

product B, 1 unit, starting SN will be -> SN0003, ending SN0003

my Question:
since all 9 product's SN start with SN0001, how to track & identify the last
SN entered for each individual product?

Many thanks in advance.

 

[Klatuu]

Function NextSerialNumber() As String
Dim varNextSN As Variant
Dim lngNumberPart As Long

varNextSN = DMax("[sn]","producttable","[product] = """ & Me.Projdct &
"""")
If IsNull(varNextSN) Then
lngNumberPart = 0
Else
lngNumberPart = CLng(Mid(varNextSN,3))
End If
NextSerialNumber = Format(lngNumberPart + 1, "\S\N0000")
End Function

 

[KARL DEWEY]

Adapt this to include your table and change to an append query.
Create a table named CountNumber with field named CountNUM containing number
from 0 (zero) through your maximum spread.
INSERT INTO YourTable ( SN )
SELECT [Enter Starting Serial #]+[CountNUM] AS Expr1
FROM CountNumber
WHERE (((CountNumber.CountNUM)<=[Enter qunaity of records]-1));

Reference the textboxes on the form instead of [Enter Starting Serial #]
and [Enter qunaity of records] to enter data from form.