codify products

[mseo]

hi,
I need to know if i can give each product unique code depending on the category and sequence number other than PK field
in other words, the sequence of products in each category
for example
dairy-001
cheese-001
milk-002
Vcheese-002

thank you very much

[mseo]

can I codify products?

[NeoPa]

You can, but you'll need to make the question details clearer before I can direct you how. Your example data makes little sense to me. Please explain.

[missinglinq]

I have to agree with NeoPa, the exemplar data does nothing toward explaining what you're trying to accomplish!

Linq ;0)>

[mseo]

hi,
I have a ProductID (AutoNumeber)field and it seems meaningless for the user so I added one more field and I need to fill this field as the following:
each product has category so I need to codify the products using first four characters from the category name and concate it with a number
the problem I need this number to be generated automatically, and grouped by categoryID
let's assume I have two Categories :
1- dairy
2- Oil
and more than one products I need to generate the code for each product to be like that

Product    Code
Milk       DAI-0001 
IceCream   DAI-0002
Olive      OIL-0001
Butter     OIL-0002
 

so,characters reflect the Category, and numbers reflect the sequence of the product in each category

I really appreciate your help
thank you very much

[rwalle]

Hi Mseo:

I Have done a small Hardware store DB that store Products , customers , suppliers, etc,the products have several categories I set the table like

Pk : Autonumber
ProdType: FK( Cbox to choose from TypeTbl that stores values like. "Electric","Plumber","Garden","Construction", . . Etc.)
Supplier:FK (ComboBox to choose from SuppTbl)
PartNum:Text which I format to be like "01-02-003"
that stands for: (01: Product type); (02: Supplier);(003:serial number)
The user who "create" new Products should know the product codification logic, so He can input right way,
then the users that just use the Db to invoice products or make inventory transactions can have clues about what to input into search forms

I hope this can be help you

Raymundo Walle

[NeoPa]

First of all, you need a field that identifies the category. From this, there must be a way of determining your three-character stub.

You would also need a form to enter this data in. For simplicity we'll start with a form that has the three simple controls - [txtCode], [txtCategory] & [txtProduct].

Now, an important point is that you cannot guarantee the value of the code until it has been saved. We can create a DefaultValue for the control if you like, but this may not reflect the actual value used, so you may prefer not to set a default at all. Your choice.

The important code, that can be run to set the DefaultValue, but must be run from within Form_BeforeInsert, will determine the correct value at that specific point in time using the category from [txtCategory].

This code will use DMax() to find the max numerical value attached to any record of the matching category. It will take this value, or 0 if no value found, then add 1 to it and append it to the category stub to form the new [txtCode].

Private Sub Form_BeforeInsert(Cancel As Integer)
  If IsNull(Me.txtCategory) Then
    Call MsgBox("No Category selected")
    Cancel = True
    Exit Sub
  End If
  Me.txtCode = NextCode(Me.txtCategory)
End Sub
 
Private Function NextCode(strCategory as String) As String
  Dim intMax As Integer
 
  intMax = Val(Right(Nz(DMax("[code]", _
                             "[ProdTable]", _
                             "[Category]='" & Me.strCategory & "'"), 0), 4))
  NextCode = UCase(Left(strCategory, 3)) & "-" & Format(intMax + 1, "0000")
End Function