Stock Control Using Visual Basic.net - Essay Example

Page: Unit Stock Control Using Visual Basic.net Group Members: Lecturer: Submitted on: Table of Contents Pages Stock Control Using Visual Basic. Net 1.0 Abstract 3 1.1 Introduction 4 1.2. Overview 4 1.3. Objective 4 1.4.Research Methodology 4 1.5.Program Coding 5 1.6 Risk Assessment 5 1.7 Qualitative survey 5 1.8-1.9.9 Program Object Design 6-16 2.0.0 VB 6.0 Code for form 1 17 2.0.1 VB 6.0 Code for form 4 17 2.0.2 VB 6.0 Code for form 5 18 2.0.3 VB 6.0 Code for form 6 20 2.0.4 VB 6.0 Code for form 12 24 2.0.5 Conclusion 25 2.0.6 Reference 26 1.0 Abstract. Stock control and inventory are the major anomalies facing the business world today. With a colossal volume of consumers popping into shopping malls everyday, can easily hamper the stock exchange if the right program is not put in place to curtail the anathema. Wherefore the grave need to incorporate the .NET framework, a developmental software environment that blends very well with Access and other databases to enhance efficiency and accountability in stock control and inventories. 1.1 Introduction: The fact, that the business world is loosing a hell of money due to the imbalance of inventory and stock control. Does not warrant programmers from sitting back and just anticipation to see a market that crumbles upon itself. Instead this has triggered the development integrated system that would otherwise control the stock as well as build a balanced inventory, in the sense that eliminates all forms of deficits, i.e. shortage of goods in the store. 1.2 Overview Identifying problems the business are facing due to sock Determine the objectives of visual basic.net Importance of stock control & inventory Strategy and steps of the programme to be build Testing of the prototype system developed 1.3 Objective The main vision of this project; is to achieve a method of a simple stock count of a mini market (corner shop), as these shop are still mainly managed and run in a traditional way. These groceries are in a large numbers due to high demand; they have the potential of optimising the business as well as deliver goods to customers in a continual basis. Therefore the project proposed is intended to assist the clients with an easy way to acknowledge the owner of the goods available and goods which a due to run out, so that they can place and order at the right time, with this method the client is able to reduce holding time as well as optimising cash flow. The delivered program will be able to stored data of goods, which will be strategically sequenced by product line code (PLU). It will also have the ability to indicate when stock is running low. As well have an easy and friendly way to operate. 1.4 Research Methodology Since it’s a proven fact indeed that Inventory and Stock Control has a big impact to efficiency of the business, intensive research is to be carried out, to determine the problems of stock control that retail business is facing. This will be achieved by the use of survey questionnaires in carrying out the project. Then the data collected will be critically analysed and computed along with key retail- experienced staff that has been in the business that will then determine the need of the program. 1.5 Program Coding Steps to be taken to create the programme are to be strategically planned and laid out on a data flow diagram (DFB) and data process diagram (DPD) to illustrate the contents. The importance of vb.net and Microsoft access into creating the programme proposed are to be collected by form textbooks, journals and the Internet. Real product data are to be collected from retailers. 1.6 Risk Assessment The main problem that would occur are of that same products may be entered several times, therefore categorisation of the product will have to be define whereas each category line will have a certain coding then PLU discussed above will follow. Other problem would be of copyright of the programmes available that have the same objectives; therefore an intensive search on current available systems will be taken under a serious consideration. 1.7 Qualitative Survey Project survey is due to commence on the 23rd Jan 2006, which will take 2 weeks. Meeting with retailer staff will be organise at short notice once the data has been collected, which will be carried out on the weekend starting on the 4th of Feb 2006. Below is a table of time frame. Topic No. Weeks From To Survey Questionnaire’s 2 23rd Jan 2006 4th Feb 2006 Meeting with retailers 1 day 4th Feb 2006 ----------------- Current sys research 1 6th Feb 2006 13th Feb 2006 Programming & testing 2 14th Feb 2006 5th March 2006 Interim report 3 day 6th March 2006 9th March 2006 Final report preparation 10 13th March 2006 24th May 2006 1.8 Program Object Design This project was developed using Visual Basic 6.0 and Microsoft Access XP. The figure 1 below shows the design mode of the application. For linking the forms to the database, which is access, the Microsoft ADO Data Control 6.0 (OLEDB) object is used as highlighted in figure below. Figure 1 1.8.1 Fig 2 below shows first stage of configuring the ADO object by using the Build option. Figure 2 1.8.2 Clicking on the build option brings up the following figure that enables the user to link to any of the listed Databases. Figure 3 1.8.3 Figure 4 below enables the user to link to a specific database as shown in figure 4 below left. Figure 4 1.8.4 The record source tab allows you to link to an object like tables within the database as in figure 5 below. Figure 5 1.8.5 First action is to click in the invoice number text box to get the invoice number To record a sale, the cursor should be placed in the product number text box of sales detail section then the product code list box will appear To make the program function effectively, the last action after a sale should be a click in the invoice number text box. Figure 6 The program starts with a menu form as in figure 1 above The user chooses the option by clicking on the button of choice 1.8.6 The option that is most used is the sales option. It enables the user to record all the transactions. When chosen, the following screen appears Figure 7 1.8.7 The form opens with the cursor placed in the date text box which displays the current date. Clicking in the invoice No box automatically generates an invoice number. To record a sale, the cursor should be placed in the first text box under PRODUCT NUMBER. When the text box gets focus, a list box containing a list of all the product numbers, appear. (This is used in place of bar codes). This is shown in figure 8 below. Figure 8 1.8.8 Clicking on a product number in the list box places it in the text box with the cursor as shown in fig 9 below By choosing a product number, its description and unit price, which is taken from the product table in the Access database, are displayed in the adjacent label boxes. Figure 9 1.8.9 When the cursor leaves the QTY text box after entering the quantity purchased, the cancel sales button appears, this enables the user to cancel a sale for the current sale line. Also the line total is displayed in the last label box (the program multiplies the unit price with the quantity typed) and the overall sales total is displayed in the TOTAL label box (the program adds all the labels above it) as in figure 10 below. Figure 10 1.9.0 Fig 11 below shows the quantity sold is deducted from the quantity in stock in the products table in the Access database. Also the sales table is also updated in the sales table with the sale line in the form. Figure 11 1.9.1 Figure 12 below shows a form Before sales transaction contents of the Product and Sales tables Figure 12 Updates in Tables after a sales transaction. 1.9.1 Fig 13 below The program enables the user to cancel a sale by clicking on the Cancel Product Sale button as below. The line of sale is removed and the database is updated by updating the quantity in stock in the Product table and deleting that particular sale record from the Sale table as shown below. Figure 13 1.9.2 Fig 14 below shows Contents of tables after the second sale. Figure 14 1.9.3 Fig 15 below shows form after a sale is cancelled. Notice the total sale is reduced/updated. Figure 15 1.9.4 Fig 16 below shows the Product and Sales tables in the database. When cancelling product and sales. Quantity in stock for PROD005 is updated to 100 and the line is removed from Sales Figure 16 1.9.5 When the quantity sold leads to the quantity in stock falling below 10 in the Product table, a message box pops up to notify the user to re-order more stock as in figure 17 below left. Figure 17 After the sales, the return to main form button is clicked, the list box is cleared and the main menu form is displayed as in figure 17 above. 1.9.6 The user can use the Order button on the main menu form to see a list of all the stock that need to be re-ordered. Also the supplier button on the form when clicked shows a list of the suppliers and their details to make it easy to contact and re-order as in figure 18 below. Figure 18 1.9.7 The product form in fig 19 allows the user to add new products to the database or update stocks by updating the quantity in stock box. The product search box enables the user to find a specific product by typing the product number in the text box and clicking the search button as shown with arrows in the figure below. Figure 19 1.9.8 The supplier form as in figure 20 allows the user to add new suppliers to the database or edit details of suppliers. The search box, as pointed by arrow enables the user to find a specific supplier by typing the supplier id in the text box and clicking the search button as in figure below. Figure 20 1.9.9 Fig 21 below is a supplier form with the option for the user to confirm a delete action with a message box as shown below on the left. These primarily ensure the safeguarding of important data. Figure 21 2.0.0 VB 6.0 CODE FOR FORM1 Form1.Hide Form6.Show End Sub Private Sub Command6_Click() End End Sub 2.0.1 VB 6.0 CODE FOR FORM4 Private Sub Command1_Click() Adodc1.Recordset.AddNew Command1.Visible = False Text1.SetFocus End Sub Private Sub Command2_Click() prompt$ = " Do You Really Want to Delete this Record?" reply = MsgBox(prompt$, vbOKCancel, "Delete Record") If reply = vbOK Then Adodc1.Recordset.Delete Adodc1.Recordset.MoveFirst End If End Sub Private Sub Command3_Click() Adodc1.Recordset.Update Adodc1.Recordset.MoveFirst Command1.Visible = True End Sub Private Sub Command4_Click() Text1.Text = "" Text2.Text = "" Text3.Text = "" Text4.Text = "" Text5.Text = "" End Sub Private Sub Command5_Click() Form4.Hide Form1.Show End Sub Private Sub Command6_Click() Dim STRSEARCH As String STRSEARCH = Text6.Text Adodc1.Recordset.MoveFirst Adodc1.Recordset.Find "SUPP_NO = " + STRSEARCH + "" If Adodc1.Recordset.EOF Then MsgBox " SUPPLIER DETAILS NOT FOUND" End If End Sub Private Sub Command7_Click() Adodc1.Recordset.MoveNext If Adodc1.Recordset.EOF Then MsgBox " THIS IS THE LAST RECORD" Adodc1.Recordset.MoveLast End If End Sub Private Sub Command8_Click() Adodc1.Recordset.MovePrevious If Adodc1.Recordset.BOF Then MsgBox " THIS IS THE LAST RECORD" Adodc1.Recordset.MoveFirst End If End Sub Private Sub Command9_Click() Form4.Hide Form7.Show End Sub Private Sub Form_Load() End Sub 2.0.2 VB 6.0 CODE FOR FORM5 Private Sub Command1_Click() Adodc1.Recordset.AddNew Command1.Visible = False Text1.SetFocus End Sub Private Sub Command2_Click() prompt$ = " Do You Really Want to Delete this Record?" reply = MsgBox(prompt$, vbOKCancel, "Delete Record") If reply = vbOK Then Adodc1.Recordset.Delete Adodc1.Recordset.MoveFirst End If End Sub Private Sub Command3_Click() Adodc1.Recordset.Update Adodc1.Recordset.MoveFirst Command1.Visible = True End Sub Private Sub Command5_Click() Form5.Hide Form1.Show End Sub Private Sub Command6_Click() Dim STRSEARCH As String STRSEARCH = Text7.Text Adodc1.Recordset.MoveFirst Adodc1.Recordset.Find "PROD_NO = " + STRSEARCH + "" If Adodc1.Recordset.EOF Then MsgBox " PRODUCT DETAILS NOT FOUND" End If End Sub Private Sub Command7_Click() Adodc1.Recordset.MoveNext If Adodc1.Recordset.EOF Then MsgBox " THIS IS THE LAST RECORD" Adodc1.Recordset.MoveLast End If End Sub Private Sub Command8_Click() Adodc1.Recordset.MovePrevious If Adodc1.Recordset.BOF Then MsgBox " THIS IS THE LAST RECORD" Adodc1.Recordset.MoveFirst End If End Sub Private Sub Command9_Click() Adodc1.Recordset.Update End Sub Private Sub Form_Load() End Sub 2.0.3 VB 6.0 CODE FOR FORM6 Private Sub Command2_Click() List1.Visible = False Unload Me Form1.Show End Sub Private Sub Command3_Click() Dim count, Index As Integer Index = Text1.Text Adodc5.RecordSource = "select * from SALES" Adodc5.Refresh Adodc5.Recordset.MoveLast Adodc5.Recordset.Delete Adodc4.RecordSource = "select * from product" Adodc4.Refresh Adodc4.Recordset.Find "PROD_NO=" & Chr$(39) & List1.Text & Chr$(39) Adodc4.Refresh Adodc4.Recordset.Fields("qty") = Val(Text8.Text) + Val(Text7.Text) Adodc4.Recordset.Update Label4.Caption = Val(Label4.Caption) - Val(Label3(Index).Caption) Text4(Index).Text = "" Text5(Index).Text = "" Label1(Index).Caption = "" Label2(Index).Caption = "" Label3(Index).Caption = "" Text6.Text = "" Text7.Text = "" Text8.Text = "" End Sub Private Sub Command5_Click() Form2.Show End Sub Private Sub Form_Load() Adodc1.RecordSource = "select * from product" Adodc1.Refresh Do Until Adodc1.Recordset.EOF = True List1.AddItem Adodc1.Recordset("prod_no") Adodc1.Recordset.MoveNext Loop Text2.Text = Date Command3.Visible = False End Sub Private Sub List1_Click() Dim count, Index As Integer Index = Text1.Text Adodc1.RecordSource = "select * from product" Adodc1.Refresh Adodc2.Recordset.MoveFirst Adodc1.Recordset.Find "PROD_NO=" & Chr$(39) & List1.Text & Chr$(39) Label1(Index).Caption = Adodc1.Recordset("DESCRIPTION") Label2(Index).Caption = Adodc1.Recordset("Unit_Price") Label2(Index).Caption = FormatCurrency(Label2(Index).Caption) Adodc1.Refresh If Text4(Index).Text = "" Then Text4(Index).Text = List1.Text Text4(Index).SetFocus Else Text4(Index).SetFocus End If End Sub Private Sub Text3_Click() Dim invnum As String If Text3.Text = "" Then Randomize Text3.Text = Int(9999 * Rnd()) Exit Sub End If End Sub Private Sub Text4_GotFocus(Index As Integer) Dim pos As Integer pos = Index Text1.Text = pos List1.Visible = True End Sub Private Sub Text4_LostFocus(Index As Integer) If Text4(Index).Text = "" Then Text4(Index).SetFocus Exit Sub End If Command3.Visible = True End Sub Private Sub Text5_GotFocus(Index As Integer) If Text4(Index).Text = "" Then MsgBox "enter something" Text4(Index).SetFocus Else Exit Sub End If End Sub Private Sub Text5_LostFocus(Index As Integer) Dim target1 As String Dim mac As Single Adodc3.RecordSource = "select * from PRODuct" Adodc3.Refresh Adodc3.Recordset.Find "prod_NO=" & Chr$(39) & List1.Text & Chr$(39) Adodc3.Refresh Do Until Adodc3.Recordset.EOF = True Text6.Text = Adodc3.Recordset ("QTY") Adodc3.Recordset.MoveNext Loop Adodc3.Recordset.Find "prod_NO=" & Chr$(39) & List1.Text & Chr$(39) Adodc3.Refresh mac = Val((Text6.Text) - (Text5(Index).Text)) Text7.Text = mac Text8.Text = Text5(Index).Text Adodc3.Recordset.Find "prod_NO=" & Chr$(39) & List1.Text & Chr$(39) Adodc3.Recordset.Find "prod_NO=" & Chr$(39) & List1.Text & Chr$(39) Debug.Print Adodc3.Recordset.AbsolutePosition Adodc3.Refresh Debug.Print Adodc3.Recordset.AbsolutePosition Adodc3.Recordset.Fields ("qty") = Val(Text7.Text) Adodc3.Recordset.Update If Text5 (Index).Text = "" Then Text4 (Index).SetFocus Exit Sub Else Label3 (Index). Caption = Val (Text5 (Index). Text * Label2 (Index). Caption) Label4.Caption = Val (Label4.Caption) + Val (Label3 (Index). Caption) ‘Label3 (Index). Caption = FormatCurrency (Label3 (Index). Caption) Label4.Caption = FormatCurrency (Label4.Caption) End If If Mac < 10 Then MsgBox "time to order" Text4 (Index). SetFocus End If Adodc2.RecordSource = "select * from SALES" Adodc2.Refresh Adodc2.Recordset.AddNew Adodc2.Recordset.Fields ("TRANS_NO") = Text3.Text Adodc2.Recordset.Fields ("prod_no") = (Text4 (Index).Text) Adodc2.Recordset.Fields ("qty") = (Text5 (Index). Text) Adodc2.Recordset.Fields ("LINE_TOT") = Label3 (Index). Caption Adodc2.Recordset.Update Adodc2.Recordset.MoveNext End Sub 2.0.4 VB 6.0 CODE FOR FORM12 Private Sub Command1_Click () DataGrid2.Visible = True End Sub Private Sub Command2_Click () Unload Me Form1.Show End Sub Private Sub Form_Load () End Sub Conclusion: The burgeoning business empires of our contemporary world are faced with challenges that call for the design and implementation of adaptable application systems that would help ease the burden of inventories. However, the vision of this project is vividly agreeable with the market philosophy that brings about the imbalances in the stock control and inventories. With this kind of technology at our disposal. It is affirmatively clear that the mini market soon business will drift away from an age old traditional system of paper records to embracing application system that are user friendly. In a nutshell the stock control project proposed is intended to facilitate efficiency in the serving the clients by reducing the holding time as well as optimising cash flow. Eventually, acting as a boost on the Customer Relation Management (CRM). Finally, since the program synchronises on the access or SQL database, becomes an authentic reservoir for storing information that can be easily retrieved for future auditing e.t.c. Therefore the technological trends of our times are in at the right time though much more need to be done by programmers in designing object oriented platforms that can easily be conceived by novice students in actualising the coding dream Reference Richard Blair et al: Beginning VB.NET (2nd Edition); Wrox Press Jeffrey. P. McManus: Database access with visual basic. Net; Addison Wesley Press Michael Halvorson: Microsoft Visual Basic. Net; Microsoft Press Deitel & Deitel: Visual Basic .NET How to Program; Prentice Hall Press http://www.accesstogo.org.uk/?s=g?g=rmdb?w=microsoftaccessdatabasetemplates?p=4 http://www.access-programmers.co.uk/learning2/TipsModules.htm#LearningVBA http://databases.about.com/od/administration/a/databasefund.htm http://databases.about.com/od/access/l/aaaccess1.htm http://www.databasecorner.com/?refr=google&at=ms-access&keyw=query%20access%20database&gclid=CPbo9Kj214ICFU1QEAodKlwjJQ http://www.netrics.com/info/dqdi/consistency1.shtml Read More