1. INTRODUCTION 1.1 OVERVIEW OF THE PROJECT In the modern era, computers have made every thing under one root and have knit the world together. Even the complicated and time consuming works are made nothing by the computers. Because of the technological IT revolution computer has been made as the core element in every place. Computerization has become an essential one, for the people who prefer speedy and ease of access. Human beings are liable to make mistakes and the output usually depends upon the efficiency of the persons involved. But computers could usually generate error output and highly efficient which varies depending on the person operating. Jewellary management system helps the retailer to maintain their records properly and accurately. This system provides a user friendly interface for controlling Purchase and sales management system.
The project entitled “Jewellary Management System” is developed with an aim of automating the work of Retailer. The project is developed using Microsoft Visual Basic 6.0-Front End Tool Ms-Access 2000-Back end tool The project is broadly divided into following modules Customer Details Supplier Details Item Details Order Details Sales Details Billing Details
All the above modules are designed to meet the requirements of a Retailer. The interface is developed with a feature rich interface and user-friendly Environment
1.2OBJECTIVES OF THE PROJECT The main objective of this system is to simplify the complicated process of maintaining updated information about rd record. The important objectives are To simplify the work of retailer in account maintenance process. To calculate rate and tax details automatically To maintain records easily The project must be simple and user-friendly. The project must be efficient than present system. The program must provide proper securities to prevent unauthorized access. By this software manual effort can be reduced.
1.3
BACKGROUND STUDY
1.3.1 THE ORGANIZATIONAL PROFILE Vigneshwara traders were established in the year 1991, with the objective of handling distributorship of quality products. It is a partnership organization. The organization emphasis fair trade practices and is very conscious of providing top of line service to the customers. The organization from the beginning had the opportunity of representing well-known companies like M/s. Larsen & Turbo Limited, Novar India Limited, Osram India Private Limited, etc. Novar India Limited is manufacturers of quality electrical products like switches, wiring accessories, cable management system, etc., and Vigneshwara Traders are their sole distributors in Coimbatore and Nilgiri Districts. Osram are one of the world’s largest manufacturers of “Light and Light Sources”, which has the widest range of lamps. Osram has now introduced special fittings to take the energy efficient fluorescent tube lights. Vigneshwara Traders are also distributors of Osram India Private Limited. Vigneshwara Traders has excellent infrastructure like office, godown, trained and experienced sales and service staff, delivery vehicle etc. The management is totally committed to quality products and customer services.
1.3.2 STUDY ON EXISTING SYSTEM The existing system is handled manually. The system has a formatted accounting system for Purchase and Sales. The indent is prepared when items are to be purchased and bill is generated for sale of items. The system follows the predetermined purchase and sales procedures. Drawbacks of existing system Manual systems are more time consuming Large number of ledger books has to be maintained for each transaction Since the data are entered manually there are possibilities for errors. Data security is very minimal. There is a possibility for double entries. Since the transaction are mainly viz. paperwork updating of data is very hard. Generating the reports in the desired format is a tedious process. Reporting to the higher officials is not done through the proper channel. Data stored on papers is subject to loss due to physical damage.
2. SYSTEM ANALYSIS 2.1 STUDY OF PROPOSED SYSTEM The proposed system is developed after a detailed study about the requirements requested by the user. Proposed system is a computerized one, where all the limitations of manual system are compensated. The proposed system shall have the following features. It automatically update monthly amount. Error free and menu driven interface. Saves lot of time. Quick preparation of reports. Reports could be generated through printouts. Consistency in quality and delivery system Web based user friendly environments for personalized information Continuous up gradation and maintenance. 2.1.1 Defining a problem The study of the system gives an insight into the structure and functioning of the system study and it will also give an idea of the user’s requirements. Timeless information is equally important as that of correct information. This can be easily achieved by this project. Once the manual operations carried in maintaining the customer details in a separate register are noted. The manually created details are roughly developed into the computerized form initially we have to find what are the drawbacks found in the existing system by means of the initial investigation stage.
2.1.2 SOLUTION DEVELOPMENT STRATEGIES Purchase and Sales management system of Jewellary Management has simplified the working information and makes a user friendly environment, where the user is provided with much flexibility to manage effectively. It helps the retailer to generate desirable reports more quickly and also to produce better results. The objectives of the proposed system are Build a user friendly system Make retrieval & editing easy Updating records easily The core elements of Jewellary Management are, Control section View section Print Reports Control Section The control section involves three parts. One is Customer and Supplier details, Item details and Order details. This module helps to the retailer to enter the Customer details, Item details gives information about items and customer, through the login option we can control the unauthorized persons to access the system. With the help of this module, we can update the new Customer’s details day by day. View Section This module helps to view the following details and it generates reports: Customer details Supplier Details Item details
Sales details Billing details Print Reports This module helps to print the following details: Customer details Supplier Details Order Details Item details THE ADVANTAGES OF THE PROPOSED SYSTEM: Less time consumption and manpower Fast and economical Accurate calculation More interactive screens Large database capacity No separate bill book is necessary Easy report generating
2.2 SYSTEM SPECIFICATION 2.2.1 Hardware Specifications Processor RAM Hard disk Monitor Key board Floppy Drive Mouse
: : : : : : :
Pentium III 800 MHz 128 MB 10 GB VGA 104 Keys or above 1.44 MB Two Buttons
2.2.2 Software Specifications Operating System
:
Language
:
Windows XP/ Windows NT/ Windows 2000 Visual Basic
Back end
:
MS-Access
2.2.3 FEATURES OF SOFTWARE USED VISUAL BASIC 6.0 In 1975, the only 20-year-old Bill Gates and his good friend, Paul Allen develop Basic on the microprocessor. And then Basic became popular to computer players. Basic has become the first program language for many people since the personal computers became more and more popular. Along with the evolution of the computer technique, the pure writing output was replaced by graphically operational interface. The Microsoft Company announced Visual Basic1.0 in 1991. Its good news for the basic lovers and it also provided another choice to program language. Much software likes to add a word “Visual” in front of their names, and Visual Basic is the pioneer among them. Why do we say “visual” to describe Basic? Because Visual Basic provides programmers many visual tools to design windows. These windows will be as same as the appearances when the programs are executing. The Integrated Development Environment The Visual Basic Integrated Development Environment (IDE) may be the single biggest reason for the vast popularity of Visual Basic. It provides everything you need to develop applications in an easy-to-use-and-learn Graphical User Interface (GUI – pronounced Gooey). Like many Windows applications, Visual Basic has several ways in which it can be opened. First, and probably the easiest way to access Visual Basic is through the Windows Start menu – the exact path required to access this shortcut is dependent upon your installation and may differ on individual machines. Another option is to create a shortcut on your desktop, which will execute Visual Basic by double-clicking on it. Lastly, because Visual Basic sets up default associations when it is installed, you can also run it by double-clicking on files that end with a vbp (Visual Basic Project) extension.
Pointer: The pointer is the only item on the Toolbox that isn’t a Control. You can use it to select Controls that have already been placed on a Form. Picture Box: You use the Picture Box Control to display images in several different graphics formats such as BMP, GIF, and JPEG among others. Label: The Label Control is used to display text information that does not have a need to be edited by an end user. It’s often displayed next to additional Controls such as Text Boxes to label their use. Text Box: You use Text Box Controls for user input. It may be the most widely used Control. Frame: A Frame Control is typically used for containing other Controls and for dividing the GUI. Controls placed within a Frame cannot be displayed outside of it, and if the Frame is moved on the Form, the Controls are moved with it. Command Button: Much like the Text Box Control, Command Button Controls are used for input on almost every Form. They are used as standard buttons for input like OK or Cancel. Check Box: If you need the ability to select True/False or Yes/No, the Check Box Control is the correct Control. Option Button: The Option Button Control is similar to the Check Box Control in that it offers the ability to select an option. However, an Option Button Control is most often used when a group of options exists and only one item can be selected. All additional items are deselected when a choice is made. List Box: The List Box Control contains a list of items, allowing an end user to select one or more items. Combo Box: Combo Box Controls are similar to List Box Controls, but they only provide support for a single selection. Scroll Bars: The HScrollBar and VScrollBar Controls let you create scroll bars but are used infrequently because many Controls provide the ability to display their own Scroll Bars. Timer: The Timer Control is an oddity when it is compared to other Controls, in that it isn’t displayed at runtime. It’s used to provide timed functions for certain events.
Drive List Box, Dir List Box, and File List Box: These Controls can be used individually, but many times are used together to provide dialog boxes (also known as windows in this book) that display the contents of Drives, Directories, and Files. Shape, Line: The Shape and Line Controls are simply used to display lines, rectangles, circles, and ovals on Forms. Image: You can think of the Image Control as a lighter version of the Picture Box Control, and although it doesn’t provide all of the functionality that the Picture Box Control does, it consumes fewer resources. As a result, you should use the Image Control whenever possible. Data: The Data Control is a component that allows you to connect one or more Controls on a Form to fields in a database. MS-ACCESS What is Access? Microsoft Access is a relational database management system (DBMS). At the most basic level, a DBMS is a program that facilitates the storage and retrieval of structured information on a computer’s hard drive. Examples of well-know industrialstrength relational DBMSs include, Oracle Microsoft SQL Server IBM DB2 Informix Well-know PC-based (“desktop”) relational DBMS include Microsoft Access Microsoft FoxPro Borland dBase The many faces of Access Microsoft generally likes to incorporate as many features as possible into its products. For example, the Access package contains the following elements: Relational database system that supports two industry standard query languages: Structured Query Language (SQL) and Query By Example (QBE);
1. Introduction to Microsoft Access •
a full-featured procedural programming language- essentially a subset of Visual Basic,
•
a simplified procedural macro language unique to Access;
•
a rapid application development environment complete with visual form and report development tools;
•
a sprinkling of objected-oriented extensions; and,
•
Various wizards and builders to make development easier.
For new users, these “multiple personalities” can be a source of enormous frustration. The problem is that each personality is based on a different set of assumptions and a different view of computing. For instance, •
the relational database personality expects you to view your application as sets of data;
•
the procedural programming personality expects you to view your application as commands to be executed sequentially;
•
The object-oriented personality expects you to view your application as objects which encapsulate state and behavior information. Microsoft makes no effort to provide an overall logical integration of these personalities (indeed, it is unlikely that such integration is possible). Instead, it is up to you as a developer to pick and choose the best approach to implementing your application. Since there are often several vastly different ways to implement a particular feature in Access, recognizing the different personalities and exploiting the best features (and avoiding the pitfalls) of each are important skills for Access developers. The advantage of these multiple personalities is that it is possible to use Access to learn about an enormous range of information systems concepts without having to interact with a large number of “single-personality” tools, for example:
•
Oracle for relational databases
•
PowerBuilder for rapid applications development,
•
Small Talk for object-oriented programming.
Keep this advantage in mind as we switch back and forth between personalities and different computing paradigms. Access database file Although the term “database” typically refers to a collection of related data tables, an Access database includes more than just data. In addition to tables, an Access database file contains several different types of database objects: •
saved queries for organizing data,
•
forms for interacting with the data on screen,
•
reports for printing results,
•
macros and Visual Basic programs for extending the functionality of database applications. All these database objects are stored in a single file named .mdb. When you are running Access, a temporary “locking” file named . ldb is also created. You can safely ignore the *.ldb file; everything of value is in the *.mdb file.
3. DESIGN AND DEVELOPMENT PROCESS 3.1 DESIGN NOTATION 3.1.1 Data Flow Diagram
PURCHASE MODULE Supplier
SUPPLIER
1 Validate Supplier Details
DETAI Details LS
Item Details
* SUPPLIER
2 Validate Item Details
Validated Supplier Details
Validated Item Details
3 Indent Generatio n ITEM
ITEM
Supplier Details Indent Details
*
SUPPLIER
Item Details
Indent
INDENT SUPPLI ER
* INDENT DENT INDENT SUPP LIER
Indent Details Bill Details
4 Generation of Verification Report
Verified Item Details
*
BILL
Verified *
*
BILL
Item Details
ITEM Item Details
5 Stock Updation
Updated Item * Details
ITEM
SALES MODULE
Customer CUSTOMER
Details
6 Validate Customer Details
7 Validate Order Details
Item Details
Customer
* CUSTOMER
Details
*
ORDER
Order Details
* CUSTOMER
* oRDER * *
ITEM DCHALLAN
* DCHALLAN
*
SBILL
Validated CUSTOMER
Customer Details
Validated ORDER
Order Details
8 Delivery Bill Generatio n
Delivery Billing Details
Delivery Challan
DCHALLAN CUSTOMER
Customer Details Order Details Item Details
9 Sales Bill Generatio n
Bill
CUSTOMER
Bill Details
SBILL
Delivery Item Details Challan Details 10 Stock Updatio n
Updated
*
ITEM
Item Details
Supplier Item Details Details Quotation Details
Report Generation Reports
MANAGEMENT
*
ITEM
*
SUPPLIER
Item Details
* QUOTATION
Customer * CUSTOMER
*
ORDER
*
BILL
*
SBILL
Details
Sales Order Details Customized Bill Details Sales Bill Details
3.2 DESIGN PROCESS 3.2.1 Data Base Design Table details: 1. Supplier details FIELD NAME SUPPLIER_ID
DATA TYPE TEXT
DESCRIPTION SUPPLIER CODE
SUPPLIER_NAME ADDRESS1 ADDRESS2 PHONE_NO CITY
TEXT TEXT TEXT TEXT TEXT
COMPANY NAME STREET NAME1 STREET NAME2 PHONE NO CITY NAME
2. Customer details FIELD NAME CUSTOMER_ID CUSTOMER_NAME ADDRESS1 ADDRESS2 PHONE_NO CITY EMAIL_ID VISIT_DATE
DATA TYPE TEXT TEXT TEXT TEXT NUMBER NUMBER TEXT DATE
DESCRIPTION CUSTOMER NUMBER CUSTOMER’S NAME STREET NAME 1 STREET NAME 2 PHONE NUMBER CITY EMAIL ID CUSTOMER VISITING DATE
FIELD NAME
DATA TYPE
DESCRIPTION
SUPPLIER_ID ITEM_ID ITEM_DESCRIPTION
TEXT TEXT CURRENCY
QUANTITY
NUMBER
WEIGHT REORDER_LEVEL
NUMBER NUMBER
SUPPLIER NAME ITEM CODE DESCRIPTION OF THE ITEM TOTAL NUMBER OF ITEMS CURRENTLY AVAILABLE IN THE STORES WEIGHT PER UNIT REORDER LEVEL OF JEWEL
3. Item details
MAKING_CHARGE ITEM_TYPE
NUMBER TEXT
MAKING CHARGE OF JEWEL GOLD OR SILVER
4. Order details FIELD NAME
DATA TYPE
DESCRIPTION
CUSTOMER_ID
TEXT
CUSTOMER ID
ITEM_ID
TEXT
ITEM ID
WEIGHT
DATE
WEIGHT OF THE ITEM
QUANTITY
DATE
QUANTITY OF ITEM
ITEMDESCRIPTION
TEXT
ITEM DESCRIPTION
ADVANCE
NUMBER
ADVANCE AMOUNT PAID
ORDER DATE
DATE
ORDERING DATE
ORDER ACKNOW
TEXT
ORDER DELIVERED OR NOT
5. Rate details FIELD NAME
DATA TYPE
DESCRIPTION
TODAY DATE
DATE
CURRENT DATE
GOLD RATE
NUMBER
GOLD RATE
6. Billing Details FIELD NAME
DATA TYPE
DESCRIPTION
BILL_NO
TEXT
PURCHASE BILL NUMBER
BILL_DATE
DATE
BILL DATE
CUSTOMER_ID
TEXT
CUSTOMER CODE
GOLD_RATE
TEXT
GOLD RATE
TOTAL_AMOUNT
NUMBER
TOTAL AMOUNT
PAID CUSTOMIZED /READY MADE
NUMBER
PAID AMOUNT
TEXT
CUSTOMIZED BILL OR NEW BILL
TOTAL ADVANCE PAID BALANCE
ADVANCE PAID NET AMOUNT
7. Bill item Details FIELD NAME
DATA TYPE
DESCRIPTION
BILL_NO
TEXT
SALES BILL NUMBER
ITEM_ID
DATE
ITEM ID
QUANTITY
TEXT
QUANTITY OF ITEM
AMOUNT
NUMBER
AMOUNT OF ITEM
WEIGHT
NUMBER
WEIGHT OF THE ITEM
WASTAGE
NUMBER
WASTAGE OF ITEM
MAKING_CHARGE
NUMBER
MAKING CHARGE OF ITEM
8. Login Details FIELD NAME
DATA TYPE
DESCRIPTION
USE_NAME
TEXT
USER NAME
PASSWORD
TEXT
PASSWORD
3.2.2 Input Design Inaccurate input data are the most common cause of errors in data processing. Errors entered by data entry operators can be controlled by input design. Input design is the process of converting user originated inputs to computer based format. In the DFD phase, the expanded data flow diagram identifies logical data flows, data stores, sources and destinations. It also specifies the master files and transaction files. A source document should be logical and easy to understand. Each area should be clearly identified and should specify as what the user should enter. The analyst must decide the methods of inputting the data into the system. Input devices used in this
system are keyboard and mouse.
The input design consists of specifications and
procedures for data preparation and entry. Input design Jewellary management software includes forms to: 1. Supplier details 2. Customer details 3. Item details 4. Order Details
3.2.3 Output Design Computer output is the most important and direct source of information to the user. Efficient and intelligible output design should improve the system’s relationships with the user and help in decision making. A major form of output factors such as compatibility of the device with the system, response time requirements, expected print quality and number of copies needed. Output design for Purchase and sales management Software includes: 1. Reports, which displays the supplier and customer detail. 2. User can identify the payment details, reorder level and repayment details. 3. Help menu gives the help contact for the end users.
4. TESTING AND IMPLEMENTATION 4.1 SYSTEM TESTING After each program passes its own test, its linkage to other programs is scrutinized with a program integration test. This ensures that the entire system works together as intended. Before the implementation phase, the designed system should be tested with raw data to ensure that all modules of the system work correctly and satisfactorily. If some bug is found, it can be removed. There are four kinds of testing. 4.1.1 UNIT TESTING Unit testing focuses the verification effort on the smallest unit of S/W design i.e., the module. The unit testing is always white-box oriented and the step can be conducted in parallel for modules. During unit test, testers can use the same project or projects as the developers, if functional units organize the project, or separate projects have been created for functional units. The project or projects can also be exported, so unit test can take place in a variety of environments and on a variety of platforms. Unit Test Considerations The tests that occur as part of unit testing. The module ‘interface’ is tested to ensure that information properly flows into and out of the program unit under test. The ‘local data structures’ are examined to ensure that data stored temporarily maintains its integrity during all steps in an algorithms execution. ‘Boundary Conditions’ are tested to ensure that the module operates properly at boundaries established to limit or restrict processing. All ‘independent paths’ through the control structures are exercised to ensure that all statements in a module have been executed at least once. Finally, all ‘error-handling paths’ are tested. Unit Test Procedures Unit testing is considered an equivalent to the coding step. After the source level code has been developed, reviewed and verified for correct syntax, unit test case design
begins since a module is not a stand alone program, ‘driver’ and/or ‘stub’ S/W must be developed for each unit test. In most applications, a driver is nothing more than a main program that accepts test case data, passes such data to the module to be tested, and prints the relevant results. The stubs serve to replace modules that are subordinates called by the modules to be tested. A stub or a dummy stub or a dummy subprogram uses the subordinate modules interface, may do minimal data manipulation, prints verification of entry, and returns. The drivers and scrubs represent overhead i.e., both are S/W that must be written but that is not delivered with the final S/W product. If the drivers and the stub are kept simple, then the overhead is low. The Unit Test is carried out in this project, and is found successful. The data is flowing correctly to all part of the project.
4.2 SYSTEM IMPLEMENTATION Implementation is the state in the project where the theoretical design is turned into a working system. The most crucial stage is achieving a new successful system and in confidence that the new system will work efficiently and effectively for the user The system can be implemented only after through testing is done and if it is found to work to the specifications. It involves careful planning and investigation of the current system and its constraints on implementation. Two major tests of preparation for implementation arre educating and training of users and testing the system. The most difficult task in the system life cycle is the successful implementation of the new system design. Implementation includes all those activities that take place to convert from the old system to the new system. The new system may be that a major implementation becomes necessary so that a reliable system based on the requirements of the organization can be provided. There are three aspects of implementation,
1. Training personal 2. Conversion procedure 3. Post implementation review After the system is implemented and conversion is complete a review should be conducted to determine whether the system is meeting expectations and where improvements are needed. A post implementation review measures the system’s performance against predefined requirements.
It determines how well the system
continues to meet performance specifications. I t also provides information to determine whether a major re design or small modification is required. The post implementation review is an evolution of a system in terms of the extent to which the system accomplishes stated objectives and if the actual project costs exceeds initial estimates. The post implementation study begins unexpected changes in the system that affects the user or system performance is a primary factor that prompts system review. Once a request is filed, the user is asked how well the system is functioning to specifications or how well the measured benefits have been realized. Suggestions regarding changes and improvements are also asked for. 4.3 IMPLEMENTATION PROCEDURE The Implementation phase can be described as the phase in which the game is truly constructed, programmed and executed. This phase involves programming, debugging, compiling and executing. Implementation is the stage of the project when the theoretical design is turned into a working system. If the implementation stage is not properly planned and controlled, it can cause chaos. Thus it can be considered to be the most crucial stage in a successful new system and in giving the new system will work and be effective.
5. CONCLUSION The system “Jewellary Management system” deals with purchase and sales processing of a Jewellary shop. This system has been developed to satisfy all the proposed requirements. The process of recording details about supplier, item, Billing and customers is more simple and easy. The system reduces the possibility of errors to a great extent and maintains the data in an efficient manner. User friendliness is the unique feature of this system. The system generates the reports as and when required. The system is highly interactive and flexible for further enhancement. The coding is done in a simplified and easy to understandable manner so that other team trying to enhance the project can do so without facing much difficulty. The documentation will also assist in the process as it has also been carried out in a simplified and concise way.
6. SCOPE FOR FUTURE ENHANCEMENTS This system is developed such a way that additional enhancement can be done without much difficulty. The renovation of the project would increase the flexibility of the system. Also the features are provided in such a way that the system can also be made better and efficient functionality The programs were coded in an easier and more structured manner so that may further modifications may be incorporated easily. The processing time in this system is very lesser compared to existing system. This system has good flexibility of accommodating any more changes that might arise in the future also. In this system, data integrity is maintained and data redundancy is avoided and it increase system efficiency. The database is designed in such a way that it will be also helpful for enhancement of the system.
7. BIBLIOGRAPHY 1. Visual Basic 6.0 from the Ground up, Gary Cornell, Tata McGraw Hill Edition 2. The Complete Reference Visual Basic 6.0 , Noel Jerke, Tata Mcgraw Hill Edition 3. Visual Basic 6.0 Programming Black Book, Sten Holzner, Dream tech Press, Dreamtech Press, New Delhi-2004 4. MS-Office 2000, Michael Busy and Rossell a Stultz, BPB Publication, New Delhi. 5. Micro soft Office Access Bible by Groh 6. Software Engineering a practioner’s approach- Roger S. Pressman, Tata McGraw Hill Edition 7. Working with Access by RON Mansfield, Tata McGraw Hill Publication.
SAMPLE CODING Private f_rsCustomer As New ADOR.Recordset Private f_rsOpened As Boolean Private f_Custid As Integer Private f_Custname As String Private f_EditThis As Boolean Private Sub cmdClose_Click() If g_blnbillform Then Unload Me frmBill.Show g_blnbillform = False Exit Sub End If Unload Me frmMenuList.Show End Sub Private Sub cmdDeleteCustomer_Click() On Error GoTo errdel If cmdEditCustomer.Enabled = False Then cmdEditCustomer.Enabled = True If cmdNewCustomer.Enabled = False Then cmdNewCustomer.Enabled = True If cmdSave.Enabled = True Then cmdSave.Enabled = False cmdDeleteCustomer.Enabled = False lblDisplay.Caption = "You can delete any Customer by double clicking the Customer Id or by pressing F4 Key" imgDisplay.Visible = False Dim SQlStr As String Dim rsDel As New ADOR.Recordset fraEdit.Visible = True SQlStr = "Select CustomerId, CustomerName, VisitDate from Customerdetails" rsDel.Open SQlStr, g_conn, adOpenForwardOnly, adLockReadOnly, adCmdText If rsDel.EOF = False Then lstEdit.Clear Dim sngCustNameWidth As Single Dim intspc As Integer Do While rsDel.EOF = False sngCustNameWidth = TextWidth(Space(58)) TextWidth(rsDel("CustomerName")) If sngCustNameWidth < 0 Then lstEdit.AddItem rsDel("CustomerId") & Space(18) & rsDel("CustomerName") & Space(3) & rsDel("VisitDate") Else
intspc = CInt(sngCustNameWidth / 45) lstEdit.AddItem rsDel("CustomerId") & Space(18) & rsDel("CustomerName") & Space(intspc) & rsDel("VisitDate") End If rsDel.MoveNext Loop lstEdit.ListIndex = 0 lstEdit.SetFocus End If If Not picCustomer.Visible = False Then picCustomer.Visible = False g_status = "Delete" Exit Sub errdel: MsgBox Err.Number & Err.Description End Sub Private Sub cmdEditCustomer_Click() On Error GoTo errEdit Dim SQlStr As String Dim rsEdit As New ADOR.Recordset f_EditThis = False lblDisplay.Caption = "You can Edit/Change any Customer Details by double clicking the Customer Id or by pressing F3 Key" imgDisplay.Visible = False fraEdit.Visible = True SQlStr = "Select CustomerId, CustomerName, VisitDate from Customerdetails" rsEdit.Open SQlStr, g_conn, adOpenForwardOnly, adLockReadOnly, adCmdText If rsEdit.EOF = False Then lstEdit.Clear Dim sngCustNameWidth As Single Dim intspc As Integer Do While rsEdit.EOF = False sngCustNameWidth = TextWidth(Space(58)) TextWidth(rsEdit("CustomerName")) If sngCustNameWidth < 0 Then lstEdit.AddItem rsEdit("CustomerId") & Space(18) & rsEdit("CustomerName") & Space(3) & rsEdit("VisitDate") Else intspc = CInt(sngCustNameWidth / 45) lstEdit.AddItem rsEdit("CustomerId") & Space(18) & rsEdit("CustomerName") & Space(intspc) & rsEdit("VisitDate") End If rsEdit.MoveNext Loop lstEdit.ListIndex = 0
lstEdit.SetFocus End If If cmdDeleteCustomer.Enabled = False Then cmdDeleteCustomer.Enabled = True If cmdNewCustomer.Enabled = False Then cmdNewCustomer.Enabled = True cmdEditCustomer.Enabled = False If Not picCustomer.Visible = False Then picCustomer.Visible = False g_status = "Edit" Exit Sub errEdit: MsgBox Err.Number & Err.Description End Sub Private Sub cmdEditThis_Click() Call EnableTextBox(frmCustomer) lblDisplay.Caption = "You can Edit/Change this Customer Details only" imgDisplay.Visible = False f_Custid = Val(txtCustomerId.Text) g_status = "Edit" cmdSave.Enabled = True txtCustomerId.SetFocus f_EditThis = True End Sub Private Sub DisableAll() f_EditThis = False If cmdSave.Enabled = True Then cmdSave.Enabled = False Call DisableTextBox(frmCustomer) imgDisplay.Visible = False End Sub Private Sub cmdFirst_Click() If f_EditThis Then Call DisableAll If cmdSave.Enabled = True Then cmdSave.Enabled = False Call MoveFirst(f_rsCustomer, "frmCustomer", frmCustomer) End Sub Private Sub cmdLast_Click() If f_EditThis Then Call DisableAll Call MoveLast(f_rsCustomer, "frmCustomer", frmCustomer) End Sub Private Sub cmdMovements_Click() On Error GoTo errMove picCustomer.Visible = True If Not fraEdit.Visible = False Then fraEdit.Visible = False If cmdEditCustomer.Enabled = False Then cmdEditCustomer.Enabled = True If cmdNewCustomer.Enabled = False Then cmdNewCustomer.Enabled = True
If cmdDeleteCustomer.Enabled = False Then cmdDeleteCustomer.Enabled = True If cmdSave.Enabled = True Then cmdSave.Enabled = False lblDisplay.Caption = "Customer Details can be Viewed Only" imgDisplay.Visible = False f_EditThis = False Call DisableTextBox(frmCustomer) If Not f_rsOpened Then Call MakeConnection Else f_rsCustomer.Requery End If Call cmdFirst_Click Exit Sub errMove: MsgBox Err.Number & Err.Description End Sub Private Sub cmdNewCustomer_Click() On Error GoTo errNew Call ClearTextBox(frmCustomer) g_status = "Add" If cmdEditCustomer.Enabled = False Then cmdEditCustomer.Enabled = True If cmdDeleteCustomer.Enabled = False Then cmdDeleteCustomer.Enabled = True Call EnableTextBox(frmCustomer) cmdSave.Enabled = True cmdNewCustomer.Enabled = False If Not fraEdit.Visible = False Then fraEdit.Visible = False If Not picCustomer.Visible = False Then picCustomer.Visible = False lblDisplay.Caption = "Adding Customer Details..." imgDisplay.Visible = False If Not f_rsOpened Then Call MakeConnection End If txtCustomerId.Text = GenerateId(f_rsCustomer, "Customer") txtVisitDate.Locked = True txtCustomerId.SetFocus f_EditThis = False Exit Sub errNew: MsgBox Err.Number & Err.Description End Sub Private Sub MakeConnection() On Error GoTo errMake f_rsCustomer.CursorLocation = adUseServer f_rsCustomer.CursorType = adOpenKeyset
f_rsCustomer.LockType = adLockOptimistic f_rsCustomer.Open "CustomerDetails", g_conn, , , adCmdTable f_rsOpened = True Exit Sub errMake: MsgBox Err.Number & Err.Description End Sub Private Sub SaveData(ByVal rsCustData As ADOR.Recordset) On Error GoTo errSave rsCustData("CustomerId") = Val(txtCustomerId.Text) rsCustData("CustomerName") = txtCustomerName.Text rsCustData("Address1") = txtAddress1.Text rsCustData("Address2") = txtAddress2.Text rsCustData("City") = txtCity.Text rsCustData("Phone") = txtPhoneNo.Text rsCustData("EmailId") = txtEmailId.Text If Trim(txtVisitDate.Text) <> "" Then rsCustData("VisitDate") = CVDate(txtVisitDate.Text) Else rsCustData("VisitDate") = Format(Now, "dd-mm-yyyy") End If rsCustData.Update If Not f_EditThis Then Call ClearTextBox(frmCustomer) Else f_EditThis = False End If Call DisableTextBox(frmCustomer) imgDisplay.Picture = LoadPicture(App.Path & "\save.bmp") imgDisplay.Visible = True cmdSave.Enabled = False g_status = "" Exit Sub errSave: MsgBox Err.Number & Err.Description End Sub Private Sub cmdNext_Click() If f_EditThis Then Call DisableAll Call MoveNext(f_rsCustomer, "frmCustomer", frmCustomer) End Sub Private Sub cmdPrevious_Click() If f_EditThis Then Call DisableAll Call MovePrev(f_rsCustomer, "frmCustomer", frmCustomer)
End Sub Private Sub cmdSave_Click() On Error GoTo errCustDataSave If g_status = "Add" Then If Trim(txtCustomerName.Text) = "" Then MsgBox "Please enter Customer Name" txtCustomerName.SetFocus Exit Sub End If If Trim(txtAddress1.Text) = "" Then MsgBox "Please enter Customer Address" txtAddress1.SetFocus Exit Sub End If If Not f_rsOpened Then Call MakeConnection End If f_rsCustomer.AddNew Call SaveData(f_rsCustomer) lblDisplay.Caption = "New Customer Details Saved" cmdNewCustomer.Enabled = True ElseIf g_status = "Edit" Then If f_EditThis Then Call SaveData(f_rsCustomer) lblDisplay.Caption = "The Existing Customer Details Saved" Exit Sub End If Dim rsCust As New ADOR.Recordset Dim SQlStr As String SQlStr = "Select * from CustomerDetails where CustomerId = " & f_Custid rsCust.Open SQlStr, g_conn, adOpenKeyset, adLockOptimistic, adCmdText Call SaveData(rsCust) lblDisplay.Caption = "The Existing Customer Details Saved" cmdEditCustomer.Enabled = True End If Exit Sub errCustDataSave: MsgBox Err.Number & Err.Description End Sub Private Sub Form_Load() On Error GoTo errLoad
Call DisableTextBox(frmCustomer) cmdSave.Enabled = False fraEdit.Visible = False picCustomer.Visible = False imgDisplay.Visible = False f_rsOpened = False f_EditThis = False Exit Sub errLoad: MsgBox Err.Number & Err.Description End Sub Private Sub Form_Unload(Cancel As Integer) Set f_rsCustomer = Nothing End Sub Private Sub lstEdit_DblClick() On Error GoTo errLstDblClick Dim Custdata As String If lstEdit.Text <> "" Then Custdata = lstEdit.List(lstEdit.ListIndex) pos = InStr(Custdata, " ") f_Custid = Val(Left(Custdata, pos - 1)) If g_status = "Delete" Then Dim DeleteResponse As Integer DeleteResponse = MsgBox("Are you sure you want to delete Customer Id " & f_Custid, vbYesNo, "DeleteCustomer") If DeleteResponse = vbNo Then Exit Sub Else g_conn.Execute "Delete from CustomerDetails where CustomerId =" & f_Custid lstEdit.RemoveItem lstEdit.ListIndex lblDisplay.Caption = "Customer Detail with Customer Id " & f_Custid & " Deleted" Exit Sub End If ElseIf g_status = "Edit" Then Dim rsCust As New ADOR.Recordset Dim SQlStr As String SQlStr = "Select * from CustomerDetails where CustomerId = " & f_Custid rsCust.Open SQlStr, g_conn, adOpenForwardOnly, adLockReadOnly, adCmdText If rsCust.EOF = False Then txtCustomerId.Text = rsCust("CustomerId")
txtCustomerName.Text = rsCust("CustomerName") txtAddress1.Text = rsCust("Address1") txtAddress2.Text = rsCust("Address2") txtCity.Text = rsCust("City") txtPhoneNo.Text = rsCust("Phone") txtEmailId.Text = rsCust("EmailId") txtVisitDate.Text = rsCust("VisitDate") Call EnableTextBox(frmCustomer) fraEdit.Visible = False txtCustomerId.SetFocus cmdSave.Enabled = True txtVisitDate.Locked = False End If End If End If Exit Sub errLstDblClick: MsgBox Err.Number & Err.Description End Sub Private Sub lstEdit_KeyDown(KeyCode As Integer, Shift As Integer) If g_status = "Edit" Then If KeyCode = vbKeyF3 Then Call lstEdit_DblClick End If ElseIf g_status = "Delete" Then If KeyCode = vbKeyF4 Then Call lstEdit_DblClick End If End If End Sub Private Sub txtCustomerName_KeyPress(KeyAscii As Integer) If InStr(1, "1234567890,/?()+_-&^%$#@!`~", Chr(KeyAscii)) >= 1 Then KeyAscii = 0 End If End Sub Private Sub txtVisitDate_GotFocus() If Trim(txtVisitDate.Text) = "" Then txtVisitDate.Text = Format(Now, "dd/mm/yyyy") End Sub
Login Form
Menu List Form
Main Form
Display Items Form
Billing Form
Bill Summary
Billing Report
