Product Group Sequence with Auto Numbers

Introduction.

How to generate automatic sequence numbers for different Categories of Products in a Form.  See the sample data given below to understand the gravity of the problem more clearly:

CatCod	Category	ProdSeq	Product Description
1	Beverages	1	Coffee
1	Beverages	2	Tea
1	Beverages	3	Pepsi
2	Condiments	1	Aniseed Syrup
2	Condiments	2	Northwoods Cranberry Sauce
2	Condiments	3	Genen Shouyu
2	Condiments	4	Vegie-spread
3	Confections	1	Uncle Bob's Organic Dried Pears
3	Confections	2	Tofu
1	Beverages	4
2	Condiments	5

The first two columns represent the Product Category Code and its Description, respectively. The third column contains the Category-wise Product Sequence Numbers, and the fourth column lists the Product Description. The product serial number is consecutive. The combination of Category Code and Product Sequence Number forms the Primary Key of the table, so duplicate Product Sequence Numbers are not allowed.

The Product file contains multiple products under each category (e.g., 1. Beverages, 2. Condiments, etc.), and each product within a category should have its own unique sequence number. Product sequence numbers must not contain duplicates.

When a new product is added to a category, the form should automatically generate the next sequence number. Manually tracking the last sequence number used for each category during data entry is impractical. However, MS Access can handle this task efficiently and accurately. A simple VBA routine on the form can accomplish this automatically.

An image of a sample Form for the above data is given below:

Right-click to open Large Image in New Tab/Window.

The first field, Category Code (1, 2, 3, etc.), is a lookup field implemented as a combo box linked to the Category Table. On the property sheet, the first column width is set to 0 so that the category description is displayed in the combo box instead of the numeric code.

During data entry, the user first selects a Product Category in the initial field to prepare for adding a new product under that category. When the user presses the Tab key to move out of the Category field, the next sequence number (i.e., the existing Product Sequence Number + 1) is automatically inserted into the ProdSeq field. The user then needs to enter only the Product Description manually. The ProdSeq field can be locked to prevent accidental changes.

The following program runs in the Category_LostFocus() event procedure. It identifies the highest existing Product Sequence Number for the selected category, calculates the next sequence number, and automatically inserts it into the ProdSeq field:

The Category LostFocus Code.

Private Sub Category_LostFocus()
Dim i, s
If Me.NewRecord Then
     i = Me!Category
     s = Nz(DMax("ProdSeq", "Products", "Category = " & i), 0)
     s = s + 1
     Me![ProdSeq] = s
End If 
End Sub

The program works only when the user attempts to create a New Record.

If the Category Code is a Text value, then you need to make a change in the criteria part of the DMax() Function as given below:

s = Nz(DMax("ProdSeq", "Products", "Category = ‘" & i & "’"), 0)

Technorati Tags: Microsoft

• Join Split Array Function
• Memo Field and Data Filtering
• Lively Controls on Form
• Create your own Color Palette
• Colors 24 bits and Binary Conversion