Complete inventory management system

Complete inventory management system

Complete inventory management system

Here I have explained most things in MS Access Database Programming after reading the code and checking the tutorial you will know how some of the logic in the Login System is done.  

    Below I provided you with the code you can learn also you can copy the code and use it to build your application just alter them as you want these blocks of code are arranged as the tutorial shows

The code below is the key to our Inventory Management system it has the sale heart of the MS Access Database but what you can learn here is the Logics and some Techniques used to accomplish the Inventory Management System.

Complete inventory management system

Afya C System Database Sales Order Form

Private Sub SaveOrder_Click()

Dim Activity As Recordset
Dim rsUser As String
Dim rsSub As DAO.Recordset
rsUser = Forms![Navigation Form]!txtUser.Value

The above code has the Declared variables with the types of data they belong or acquired The Line  rsUser = Forms![Navigation Form]!txtUser.Value    

The line above grabs the user Name of the one logged in to the system for security purposes Because the username is located in the main form ( Navigation Form) that's why we have referenced the main for as you see the [Navigation Form] there in Complete inventory management system

If Status.Value = "Deleted" Then

        Forms!SalesOrder!FSubPurchaseOderLine.Form!Quantity.Enabled = False

        Forms!SalesOrder!FSubPurchaseOderLine.Form!txtDiscount.Enabled = False

        Forms!SalesOrder!FSubPurchaseOderLine.Form!CmdRemove.Enabled = False

        Forms!SalesOrder!FSubPurchaseOderLine.Form!CmdRemove.ForeColor = vbGreen

        Forms!SalesOrder!FSubPurchaseOderLine.Form!ItemCode.Enabled = False

        MsgBox "This order has been Deleted", vbCritical, "Bad Choice"

        Undo

        DoCmd.GoToRecord , , acNewRec

    Exit Sub

End If

In the Complete inventory management system the following code above is the code to disable some features when a user tries to save the deleted order as you see our complete inventory Management System has a status bar that differentiates between Not saved, on progress, Deleted, and Completed orders.

If Status.Value = "Completed" Then

    FormattedMsgBox "Sorry..my Dear" & " " & rsUser & _

    "@ This Order has been Completed," & vbCrLf & _

    "Please Create new order now@", vbCritical, "Bad Choice"

    Undo

    DoCmd.GoToRecord , , acNewRec

    Forms!SalesOrder!FSubPurchaseOderLine.Form!Quantity.Enabled = True

    Forms!SalesOrder!FSubPurchaseOderLine.Form!txtDiscount.Enabled = True

    Forms!SalesOrder!FSubPurchaseOderLine.Form!CmdRemove.Enabled = True

    Forms!SalesOrder!FSubPurchaseOderLine.Form!ItemCode.Enabled = True

    Exit Sub

End If

This VBA Code in Complete inventory management system shows when the user tries to save the completed order first gets the message thus the order is completed also the details locked Early are now Enabled because after the message the application creates a new order as you see the Code DoCmd.GoToRecord , , acNewRec This code creates a new order, so the app must unlock these features to allow the user to enter customer details.

Code on Progress

If Status.Value = "On Progress" Then

   Forms!SalesOrder!FSubPurchaseOderLine.Form!Quantity.Enabled = False

   Forms!SalesOrder!FSubPurchaseOderLine.Form!txtDiscount.Enabled = False

   Forms!SalesOrder!FSubPurchaseOderLine.Form!CmdRemove.Enabled = False

   Forms!SalesOrder!FSubPurchaseOderLine.Form!CmdRemove.ForeColor = vbGreen

   Forms!SalesOrder!FSubPurchaseOderLine.Form!ItemCode.Enabled = False

 

 Set rsSub = CurrentDb.OpenRecordset("SalesOrdersub", dbOpenDynaset)

'Loop through the subform records and save each one to the new table

    With Forms!SalesOrder!FSubPurchaseOderLine.Form.Recordset

        .MoveFirst

        Do Until .EOF

            rsSub.AddNew

            rsSub!ItemCode = Forms!SalesOrder!FSubPurchaseOderLine.Form!ItemCode.Column(0) 'Change "ItemName" to the name of the field in your subform

            rsSub!UnitPrice = Forms!SalesOrder!FSubPurchaseOderLine.Form![Inventory.UnitPrice].Value 'Change "UnitPrice" to the name of the field in your subform

            rsSub!Description = Forms!SalesOrder!FSubPurchaseOderLine.Form!ItemCode.Column(1)

            rsSub!Quantity = Forms!SalesOrder!FSubPurchaseOderLine.Form!Quantity.Value

            rsSub!OrderDate = Forms!SalesOrder!FSubPurchaseOderLine.Form![OrderDate].Value

            rsSub!Discount = Forms!SalesOrder!FSubPurchaseOderLine.Form![Discount].Value

            rsSub![ID Number] = Forms!SalesOrder!SalesOrder.Value

            rsSub.Update

            .MoveNext

        Loop

    End With

    rsSub.Close

    Set rsSub = Nothing

    Status.Value = "Completed"

    MsgBox "Changes Saved"

    

    Edit the record

    Set Activity = CurrentDb.OpenRecordset("UpdatesDate")

With Forms!SalesOrder!FSubPurchaseOderLine.Form.Recordset

        .MoveFirst

        Do Until .EOF

        Activity.AddNew

        Activity!PreparedBy = Forms![Navigation Form].Form!txtUser.Value

        Activity!OrderDate = Me.OrderDate

        Activity!CustomerName = Me.Customer

        Activity!TimeStamp = Now()

        Activity!OrderNumber = Me.SalesOrder

        Activity!Status = Me.Status

        Activity!Description = Forms!SalesOrder!FSubPurchaseOderLine.Form!ItemCode.Column(1)

        Activity!Quantity = Forms!SalesOrder!FSubPurchaseOderLine.Form!Quantity

        Activity!UnitPrice = Forms!SalesOrder!FSubPurchaseOderLine.Form![Inventory.UnitPrice].Value

        Activity!OrderDate2 = Forms!SalesOrder!FSubPurchaseOderLine.Form![OrderDate].Value

        Activity!Edited = "Edited"

        Activity.Update

        .MoveNext

    Loop

End With

    Activity.Close

    Set Activity = Nothing

Exit Sub

Complete inventory management system

Sales Order Form in Design View

Else

Set Activity = CurrentDb.OpenRecordset("UpdatesDate")

With Forms!SalesOrder!FSubPurchaseOderLine.Form.Recordset

        .MoveFirst

        Do Until .EOF

        Activity.AddNew

        Activity!PreparedBy = Forms![Navigation Form].Form!txtUser.Value

        Activity!OrderDate = Me.OrderDate

        Activity!CustomerName = Me.Customer

        Activity!TimeStamp = Now()

        Activity!OrderNumber = Me.SalesOrder

        Activity!Description = Forms!SalesOrder!FSubPurchaseOderLine.Form!ItemCode.Column(1)

        Activity!Quantity = Forms!SalesOrder!FSubPurchaseOderLine.Form!Quantity.Value

        Activity!UnitPrice = Forms!SalesOrder!FSubPurchaseOderLine.Form![Inventory.UnitPrice].Value

        Activity!OrderDate2 = Forms!SalesOrder!FSubPurchaseOderLine.Form![OrderDate].Value

        Activity.Update

        .MoveNext

    Loop

End With

    Activity.Close

    Set Activity = Nothing

Dim db As DAO.Database, rs As DAO.Recordset, count As Long, n As Long, SQL As String

    Set db = CurrentDb

    Set rs = db.OpenRecordset("SELECT * FROM SalesOderDetails WHERE SalesOrder=" & Me.SalesOrder)

    If rs.EOF Then Exit Sub 'If there are no records, stop execution at this level.

    rs.MoveLast: count = rs.RecordCount: rs.MoveFirst

    For n = 1 To count

    SQL = "UPDATE inventory SET Inv_Qty = inventory.Inv_Qty - " & rs.Fields("quantity") & " WHERE ItemCode=" & rs.Fields("ItemCode")

    db.Execute SQL

    rs.MoveNext

Next n

Set rsSub = CurrentDb.OpenRecordset("SalesOrdersub", dbOpenDynaset)

'Loop through the subform records and save each one to the new table

    With Forms!SalesOrder!FSubPurchaseOderLine.Form.Recordset

        .MoveFirst

        Do Until .EOF

            rsSub.AddNew

            rsSub!ItemCode = Forms!SalesOrder!FSubPurchaseOderLine.Form!ItemCode.Column(0) 'Change "ItemName" to the name of the field in your subform

            rsSub!UnitPrice = Forms!SalesOrder!FSubPurchaseOderLine.Form![Inventory.UnitPrice].Value 'Change "UnitPrice" to the name of the field in your subform

            rsSub!Description = Forms!SalesOrder!FSubPurchaseOderLine.Form!ItemCode.Column(1)

            rsSub!Quantity = Forms!SalesOrder!FSubPurchaseOderLine.Form!Quantity.Value

            rsSub!OrderDate = Forms!SalesOrder!FSubPurchaseOderLine.Form![OrderDate].Value

            rsSub!Discount = Forms!SalesOrder!FSubPurchaseOderLine.Form![Discount].Value

            rsSub![ID Number] = Forms!SalesOrder!SalesOrder.Value

            rsSub!Status = Forms!SalesOrder!Status.Value

            rsSub.Update

            .MoveNext

        Loop

    End With

    rsSub.Close

    Set rsSub = Nothing

Call Notsve_Click

        MsgBox rs.RecordCount & " Item(s) Inventory  Updated ", vbInformation, "Updated"

        Me.FSubPurchaseOderLine.Requery

        Me.Refresh

        Forms!SalesOrder!FSubPurchaseOderLine.Form!Quantity.Enabled = False

        Forms!SalesOrder!FSubPurchaseOderLine.Form!txtDiscount.Enabled = False

        Forms!SalesOrder!FSubPurchaseOderLine.Form!CmdRemove.Enabled = False

        Forms!SalesOrder!FSubPurchaseOderLine.Form!CmdRemove.ForeColor = vbGreen

        Forms!SalesOrder!FSubPurchaseOderLine.Form!ItemCode.Enabled = False

        Status.Value = "Completed"

End If

End Sub

So as you can see above the overall code explained in the tutorial is here check the code hope you will learn something here in the Complete inventory management system and don't forget to leave a comment on my page YouTube channel and subscribe also for more details like this.