Welcome again to our tutorial, the last tutorial we saw the way to save and restrict some data in our Complete inventory management system
Complete inventory management system | 24
Welcome again to our tutorial, the last tutorial we saw the way to save and restrict some data in our Complete inventory management system. In this tutorial, we have demonstrated the code and illustrated the meaning of each line with the respective code on it.
the image below shows the part we have learned in this tutorial, each part has a number which will be illustrated respectively.
 |
| Afya C System Database |
Combo Box and Add Items (1)
As the picture above shows number one comprises the combo box and a button (Add Items). As I illustrated in the video, the difference between the Item code combo. with this is That part one combo has the filter text in it, when you type the items names start rearranging alphabetically. this helps to give the users the easiest way to add items to the sales details list. the following Code is for Command150 (Button Name).
Private Sub Command150_Click()
If IsNull(ProductCombo) Then
DoCmd.GoToControl "ProductCombo"
ProductCombo.Dropdown
Exit Sub
End If
AddProductToOrder
End Sub
In Complete Inventory Management System the code above starts by checking the null value in our combo box (ProductCombo). then after that we focus on the ProductCombo then we open the dropdown menu of the ProductCombo then exit the sub if the above statement is true the app will exit the sub but if it is false the app should call AddProductToOrder.
What is AddProductToOrder?
in the Complete Inventory Management system the AddProductToOrder is a Private Function that has the codes for adding items in the Sales Details. List Let us check the code involved in this Function.
Private Sub AddProductToOrder()
If IsNull(Forms!SalesOrder!SalesOrder) Then
Forms!SalesOrder!OrderDate = Date
Forms!SalesOrder.Refresh
End If
If ItemCode.Enabled = False Then
FormattedMsgBox "Your not allowed to add" & vbCrLf & _
"Item in a Complete order, Please Create NEW Order.!", vbInformation, "Bad Choice"
Exit Sub
Else
DoCmd.GoToRecord , , acNewRec
DoCmd.GoToControl "Quantity"
ItemCode = Me.ProductCombo.Value
End If
End Sub
This is the code contained in AddProductToOrder, First of all, the line above checks if the sales order number is null and if found null order date will be filled with a today date the the form will refresh. After that, the app checks if there are any disabled fields if it is false then the message will pop up and exit the sub. if it is false then the app creates a new order sets the focus to the quantity, and equalizes the ItemCode value to the ProductCombo Value.
Complete inventory management system Video tutorial
ProductCombo (a combo box) (2)
Product Lookup
In this Complete inventory management system tutorial the Texbox (ProductLookup) Has the code on After Update and On Got focus. Remember this is number (2) and the function of this textbox is to sell using bar code. the thing is to have a scanner and scan the bar code of each item when registering the item's name. so when you want to sell you can identify or place the product in the sales list by scanning the bar code and place enter. the following is a code for After Update.
Private Sub ProductLookup_AfterUpdate()
Dim ProductID As Long, OrderDetailID As Long, Quantity As Double
ProductID = Nz(DLookup("ItemCode", "Inventory", "[Bar Code]=""" & ProductLookup & """"), 0)
If ProductID = 0 Then
' product not found
MsgBox "Barcode not found in product database"
Exit Sub
End If
' check to see if a product is on the order
OrderDetailID = Nz(0, ItemCode)
If OrderDetailID = 0 Then
' product not on order yet, add it
ProductCombo = ProductID
AddProductToOrder
Else
If ItemCode.Enabled = False Then
FormattedMsgBox "Invalid Action" & _
"@This Action Not allowed@", , "Bad Choice"
Else
' product is already on order, increment quantity
Quantity = Nz(DLookup("Quantity", "SalesQ", "ItemCode=" & ItemCode), 1)
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE SalesQ SET Quantity=" & Quantity + 1 & " WHERE ItemCode=" & ItemCode
DoCmd.SetWarnings True
Me.Requery
End If
End If
ProductLookup = ""
Me.Quantity.SetFocus
End Sub
Private Sub ProductLookup_GotFocus()
ProductLookup = ""
End Sub
Bar code lookup (3)
In this Bar code lookup, the Texbox is Named Productlookup and has a button Captioned as Look Up. A textbox, it has the code Get Focus and on Lost Focus, While a Button has the code On Click Event. Let's start with the On Click Event (Button)
Private Sub Command115_Click()
Dim ItemCode As Long
ItemCode = Nz(DLookup("ItemCode", "Inventory", "[Bar Code]=""" & ProductLookup & """"), 0)
If ItemCode = 0 Then
' product not found
If FormattedMsgBox("Barcode not found in product database. Would you like to Google it?", vbYesNoCancel) = vbYes Then
' Google it
Application.FollowHyperlink "https://google.com/search?q=" & ProductLookup '& "%20site:amazon.com"
End If
Exit Sub
End If
' product found
DoCmd.OpenForm "Inventory", , , "[Bar Code]=""" & ProductLookup & """"
ProductLookup = ""
End Sub
On Got Focus And Lost Focus (Text Box ProductLookup).
Private Sub ProductLookup_GotFocus()
ProductLookup = ""
End Sub
Private Sub ProductLookup_LostFocus()
'ProductLookup = "Paste/Scan Bar Code"
End Sub
Combobox Sales Order Number Filter (4)
 |
| After Update Micro |
.png)
COMMENTS