Inventory Management System

All businesses involve inventory and need to manage it efficiently to ensure smooth running of business activities and profitability. Building a sound inventory management system usually incurs high costs. Fortunately, we can use Visual Basic 6 to build an inventory management system which does not require big capital.

In Visual Basic 6, there are built-in database management tools which we can use to manage the data. To start building a good inventory system, we need to have good planning. First, you have to understand the business requirements and establish the kind of system needed.

Interactive Inventory System Demo

Add New Inventory Item

Current Inventory

Item Name	Category	Brand	Stock	Unit Cost	Total Value
Laptop X1	Electronics	Brand A	15	$899.99	$13,499.85
Monitor Pro	Electronics	Brand B	22	$249.50	$5,489.00

Database Design

We shall use a hypothetical case to illustrate how to build an inventory system. The database design includes two main tables:

Inventory Table

Field	Description
Category	Product category
Brand	Product brand
Item Description	Detailed item description
Model Number	Unique model identifier
Stock	Current stock quantity
Unit Cost	Cost per unit
Total Cost	Stock * Unit Cost

Stock Table

Field	Description
Date	Transaction date
Category	Product category
Brand	Product brand
Item Description	Detailed item description
Model Number	Unique model identifier
Stock In	Stock received
Stock Out	Stock sold/distributed
Unit Cost	Cost per unit
Total Cost	(Stock In - Stock Out) * Unit Cost

VB6 vs VB.NET Implementation

VB6 Database Connection

' Connect to MS Access database
Private Sub Form_Load()
    ' To connect AdoInventory to database
    AdoInventory.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\inventory_db.mdb;Persist Security Info=False"
    AdoInventory.RecordSource = "SELECT * FROM Inventory"
    AdoInventory.Refresh
    Set DataInventory.DataSource = AdoInventory
    
    ' To connect AdoStock to database
    AdoStock.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\inventory_db.mdb;Persist Security Info=False"
    AdoStock.RecordSource = "SELECT * FROM Stock"
    AdoStock.Refresh
    Set DataStock.DataSource = AdoStock
End Sub

VB6 Adding New Stock

Private Sub AddStock()
    ' To add items to Ado Stock
    AdoStock.Recordset.AddNew
    AdoStock.RecordSource.Fields("Date") = Format(Date, "dd/mm/yyyy")
    AdoStock.RecordSource.Fields("Category") = AdoInventory.Recordset.Fields("Category")
    AdoStock.RecordSource.Fields("Brand") = AdoInventory.Recordset.Fields("Brand")
    AdoStock.RecordSource.Fields("Item Description") = AdoInventory.Recordset.Fields("Item Description")
    AdoStock.RecordSource.Fields("Model Number") = AdoInventory.Recordset.Fields("Model Number")
    AdoStock.RecordSource.Fields("CPU") = AdoInventory.Recordset.Fields("CPU")
    AdoStock.Recordset.Update
End Sub

VB.NET Database Connection

Imports System.Data.OleDb

Public Class InventoryForm
    Private connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\inventory_db.accdb;"
    
    Private Sub InventoryForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadInventoryData()
        LoadStockData()
    End Sub
    
    Private Sub LoadInventoryData()
        Using conn As New OleDbConnection(connString)
            Dim query As String = "SELECT * FROM Inventory"
            Dim adapter As New OleDbDataAdapter(query, conn)
            Dim table As New DataTable()
            
            adapter.Fill(table)
            DataInventory.DataSource = table
        End Using
    End Sub
    
    Private Sub LoadStockData()
        Using conn As New OleDbConnection(connString)
            Dim query As String = "SELECT * FROM Stock"
            Dim adapter As New OleDbDataAdapter(query, conn)
            Dim table As New DataTable()
            
            adapter.Fill(table)
            DataStock.DataSource = table
        End Using
    End Sub
End Class

VB.NET Adding New Stock

Private Sub AddStock()
    Using conn As New OleDbConnection(connString)
        conn.Open()
        
        Dim query As String = "INSERT INTO Stock ([Date], Category, Brand, [Item Description], [Model Number], CPU) " & _
                          "VALUES (@Date, @Category, @Brand, @ItemDesc, @ModelNo, @CPU)"
        
        Using cmd As New OleDbCommand(query, conn)
            cmd.Parameters.AddWithValue("@Date", DateTime.Now.ToString("dd/MM/yyyy"))
            cmd.Parameters.AddWithValue("@Category", txtCategory.Text)
            cmd.Parameters.AddWithValue("@Brand", txtBrand.Text)
            cmd.Parameters.AddWithValue("@ItemDesc", txtItemDesc.Text)
            cmd.Parameters.AddWithValue("@ModelNo", txtModelNo.Text)
            cmd.Parameters.AddWithValue("@CPU", CDec(txtCPU.Text))
            
            cmd.ExecuteNonQuery()
        End Using
    End Using
    
    MessageBox.Show("Stock added successfully!")
End Sub

Implementation Steps

Database Setup

Create MS Access database with Inventory and Stock tables. Define all necessary fields for both tables.

Form Design

Design the user interface with DataGrid controls for inventory and stock displays, and input forms.

Code Implementation

Implement database connection, data loading, and CRUD operations for inventory management.

Key Features to Implement

Real-time inventory tracking
Stock movement history
Automatic total cost calculation
Search and filtering capabilities
Data export to Excel

System Screenshot

Visual Basic 6 Inventory Management System Interface