Microsoft word - student_projects-ii.doc

Assignment - II
Given below are fourteen projects from the chapters 6-10 and Chap.12 in Murach’s Visual Basic 6. These projects are designed to give you a chance to apply your new skills as you develop specific database-related applications. In general, these projects are slightly easier to develop than the applications in the book. That way, you should be able to do them with relative ease. In addition, many of the project descriptions include both basic specifications and enhancements. It is recommended that you try these enhancements as well. Kindly submit (one set per group), any three projects, each from a different chapter number, along with enhancements (wherever mentioned), positively by 17:00 hrs. Mon., 30th July (for sec-C & D) and by 17:00 hrs. Thurs., 2nd Aug. (for sec-A & B) NB: The submission* should contain soft copies of ‘.exe’ file and ‘code’ and ‘Form as Text’. Best Wishes… and Happy Visual Coding…!! *Submitting 'copied' ones, may possibly fetch you some marks, but would not, perhaps, aid in your learning. So, it's in your own interest to try all these nine projects, both individually and within your group, and then submit the four projects. The specifications for the MMABooks database
that’s used for the section 2 and 3 projects
• All of these tables are stored in an Access 97 database named MMABooks.
• This database is stored on the homepage along with this assignment
Books table (one row for each book)
Field name
Data type
Required
Description
Authors table (one row for each author or co-author)
Field name
Data type
Required
Description
ReceiptTran table (one row for each shipment received)
Field name
Data type
Required Description
Printers table (one row for each inventory transaction)
Field name
Data type
Required Description
Related tables that aren’t used in the current set of projects
SalesTran table (one row for each inventory transaction)
Field name
Data type
Required
Description
BookAuthor table (one row for each book/author or book/coauthor
combination)
Field name
Data type
Required
Description
Project 6-1: Maintain the Authors table (bound)
Description
• Develop a bound form for maintaining the data in the Authors table. Operation
• The user can use the navigation buttons on the ADO data control to display the data that’s in the records in the Authors table. And the user can change the data in any of the records, then click on one of the navigation buttons to update the record. • To add a record to the table, the user can click on the Next button in the data control when the current record is already the last record in the file. This displays a blank form that the user can enter the new data into. When the user moves to another record, the new record is added to the file. • This application doesn’t provide for deleting records because this is permanent data. Specifications
• Use this SQL statement in the data control’s RecordSource property to retrieve the records in Name sequence: SELECT * FROM Authors ORDER BY Name. • To display the phone number data with the parentheses and hyphen, you need to set the DataFormat property of the phone number textbox to this Custom format: (000) 000-0000. Note that this format will be used only for displaying existing records. It will not be used for entering new records. Enhancements
• Display the current record position and total number of records in the text area of the data control as shown above (Record 4 of 8). To do that, you can code a procedure for the MoveComplete event of the data control that stores the AbsolutePosition and RecordCount properties of the control in its Caption property. • Add a procedure that lets the user cancel the addition of a new record by pressing the Escape key. To do that, you can code a Form_Keydown procedure (see page 249). To start, this procedure should check the EditMode property of the recordset to determine if a record is being added. If so, it should use the CancelUpdate method to cancel the addition and the MoveLast method to move to a valid record position after the cancellation. Project 6-2: Maintain the Books table (bound)
Description
• Develop a bound form for maintaining the data in the Books table. Operation
• The user can use the navigation buttons located on the ADO data control to display the data that’s in the records in the Books table. And the user can change the data in any of the records, then click on one of the navigation buttons to update the record. • To add a record to the table, the user can click on the Next button in the data control when the current record is already the last record in the file. This displays a blank form that the user can enter the new data into. When the user moves to another record, the new record is added to the file. • This application doesn’t provide for deleting records because this is permanent data. Specifications
• Use this SQL statement in the data control’s RecordSource property to retrieve the records in Title sequence: SELECT * FROM Books ORDER BY Title. • To display the ISBN number with hyphens as shown above, you need to set the DataFormat property of the ISBN number text box to this Custom format: &-&&&&&&-&&-&. And to display the unit price as shown above, you need to set the DataFormat property of the unit price text box to Currency. Note that these formats will be used only for displaying existing records. They will not be used for entering new records. Enhancements
• Modify the RecordSource property of the data control so it retrieves only the fields • Display the current record position and total number of records in the text area of the data control as shown above (Record 4 of 8). To do that, you can code a procedure for the MoveComplete event of the data control that stores the AbsolutePosition and RecordCount properties of the control in its Caption property. • Add a procedure that lets the user cancel the addition of a new record by pressing the Escape key. To do that, you can code a Form_Keydown procedure (see page 249). To start, this procedure should check the EditMode property of the recordset to determine if a record is being added. If so, it should use the CancelUpdate method to cancel the addition and the MoveLast method to move to a valid record position after the cancellation. • Change the text box for the ISBN Number to a masked edit box so that existing numbers are displayed correctly and an input mask is used for numbers in new records. Be sure to set the PromptInclude property of the masked edit box to False so that the prompt characters aren’t included in the Text property of the control. Project 6-3: Maintain the Authors table (unbound)
Description
• Develop an unbound form for maintaining the data in the Authors table. Operation
• The user can use the navigation buttons to display the data that’s in the records in the Authors table. These navigation buttons are command buttons. • If the user changes the data in any of the records, the Update button becomes enabled. Then, the user can update the record by clicking on that button. Or, the user can cancel the update by pressing the Escape key. • To add a record to the table, the user can click on the Add button. This displays a blank form that the user can enter the new data into and enables the Update button. Then, the user can click on the Update button to add the record to the file or press the Escape key to cancel the addition. • This application doesn’t provide for deleting records because this is permanent data. Specifications
• Use this SQL statement to retrieve the records in Name sequence: SELECT * FROM • To format the 10-digit phone number as shown above, you need to use a masked edit box with this mask: (###) ###-####. Be sure to set the PromptInclude property of this control to False so the prompt characters aren’t included in the control’s value. • When you assign the EMailAddress field to its text box, you need to code it txtEmail = rsAuthors!EMailAddress & "" Then, if the field is null (it isn't required), an empty string is placed in the text box. Without that coding, this message is displayed when a null value is assigned to a text box: “Invalid use of null.” Project 6-4: Maintain the Books table (unbound)
Description
• Develop an unbound form for maintaining the data in the Books table. Operation
• The user can use the navigation buttons to display the data that’s in the records in the Authors table. These navigation buttons are command buttons. • If the user changes the data in any of the records, the Update button becomes enabled. Then, the user can update the record by clicking on that button. Or, the user can cancel the update by pressing the Escape key. • To add a record to the table, the user can click on the Add button. This displays a blank form that the user can enter the new data into and enables the Update button. Then, the user can click on the Update button to add the record to the file or press the Escape key to cancel the addition. • This application doesn’t provide for deleting records because this is permanent data. Specifications
• Use this SQL statement in the data control’s RecordSource property to retrieve the records in Title sequence: SELECT * FROM Books ORDER BY Title. • To format the 10-digit ISBN number as shown above, you need to use a masked edit box with this mask: &-&&&&&&-&&-&. Be sure to set the PromptInclude property of this control to False so the prompt characters aren’t included in the control’s value. • To format the unit price as shown above, you need to use the Format function with Enhancement
• Modify the SQL statement for the recordset so it retrieves only the fields that are Project 7-1: Display author and book data (bound)
Description
• Develop a bound form that displays the data for any author in the Authors table and for any book in the Books table that the author was the primary author for. Operation
• When the application starts, no data is displayed on the form. The user can use the first combo box on the form to select an author and display the data for that author. Then, the user can use the second combo box to select any book that the author was the primary author for and display the data for that book. • This form does not provide for updating, adding, or deleting records. Specifications
• The Authors and Books tables are related by the AuthorID field. This is the primary key in the Authors table and a foreign key in the Books table. For each book, the AuthorID field contains the ID of the book’s primary author. • This program uses four data controls: one for the Author combo box, one for the Books combo box, one for the author data, and one for the book data. • To make this application run as efficiently as possible, set the properties of the data controls used to display the author and book data as follows: CursorLocation to adUseClient; CursorType to adOpenStatic; LockType to adLockReadOnly; and Mode to adModeRead. • To format the phone number, ISBN number, unit price, YTD sales, and total sales, use the DataFormat properties of the text boxes as described in projects 6-1 and 6-2. • If you select a book title that contains an apostrophe, an error will occur. To avoid this problem for now, don't select titles that contain apostrophes. Enhancement
• Fix the apostrophe problem by using the Replace function that’s illustrated in the Project 7-2: Add new book records (bound)
Description
• Develop a bound form for adding records to the Books table. Develop a second bound form for adding a new author to the Authors table if the new book record has a new primary author. Operation
• When the application starts, the controls on the Books form are empty. Then, the user can enter the data for a new book into this form. • The user can select an existing author from the combo box on the Books form or click on the New button to display the Authors form. After entering the data for a new author, the user can click on the OK button to return to the Books form, complete the entries for the new book, and click on the Add button to add the book. • The user can press the Escape key while using the Authors form to cancel an author • Since the records in the tables are related by the AuthorID field, you can’t add a new book record without a valid entry in this field. • Both forms should include an error-handling routine that displays the error number, description, and source of any errors that occur as the result of an operation that’s performed on the recordset associated with the data control. Specifications
• To format the phone number, ISBN number, and unit price, use the DataFormat properties of the text boxes as described in projects 6-1 and 6-2. • Due to a timing problem that's related to ADO, you need to code a delaying loop like this after you use the Update method in the Click event procedure for the OK button on the form that's used for adding a new author: Dim lngLoop As Long For lngLoop = 1 To 20000000 Next lngLoop Use a To value (like 20000000) that is large enough to slow down the update so the application works, but not so large that the application is delayed unnecessarily. Project 8-1: Display author and book data (unbound)
Description
• Develop an unbound form that displays the data for any author in the Authors table and for any book in the Books table that the author was the primary author for. Operation
• When the application starts, no data is displayed on the form. The user can use the first combo box on the form to select an author and display the data for that author. Then, the user can use the second combo box to select any book that the author was the primary author for and display the data for that author. • This form does not provide for updating, adding, or deleting records. Specifications
• The Authors and Books tables are related by the AuthorID field. This is the primary key in the Authors table and a foreign key in the Books table. For each book, the AuthorID field contains the ID of the book’s primary author. • This program uses three parameter queries: one to retrieve the book titles for a selected author, on to retrieve the data for the selected author, and one to retrieve the data for a selected book. • When you assign the EMailAddress field to its text box, you need to code it txtEmail = rsAuthors!EMailAddress & "" Then, if the field is null (it isn't required), an empty string is placed in the text box. • To format the phone number, ISBN number, unit price, YTD sales, and total sales, Project 8-2: Add new book records (unbound)
Description
• Develop an unbound form for adding records to the Books table. Develop a second unbound form for adding a new author to the Authors table if the new book record has a new primary author. Operation
• When the application starts, the controls on the Books form are empty. Then, the user can enter the data for a new book into this form. • If the primary author is a previous author, the user can select the author from the combo box on the Books form. Otherwise, the user can click on the New button to display the Authors form. Then, the user can enter the data for the new author, click on the OK button to return to the Books form, complete the entries for the new book, and click on the Add button to add the book to the Books table. • The user can press the Escape key while using the Authors form to cancel an author • Since the records in the tables are related by the AuthorID field, you can’t add a new book record without a valid entry in this field. Specifications
• Declare a global connection object for the MMABooks database and a global recordset object for the Authors recordset in a standard module. Then, use these objects as required in the Books and Authors forms. • To format the phone and ISBN numbers, you need to use masked edit boxes. Be sure to set the PromptInclude properties of these controls to False. Project 8-3: Process receipt transactions
Description
• Develop an unbound form for processing book receipt transactions. After the user enters the data for a receipt, the application should use that data to update all related tables. Operation
• When the application starts, the controls on the form are empty. Then, the user can enter the data for one receipt to inventory at a time. To process the data for each receipt, the user clicks on the Add button. • If the user presses the Escape key, the entries in the form are cleared so the user can start the entries for another transaction. Specifications
• The tables that are maintained are the ReceiptTran, Books, and Printers tables. • For each receipt, one record is added to the ReceiptTran table. Besides the entered data, the program divides the total cost by the receipt quantity and stores the result in the UnitCost field. • For each receipt, the related record in the Books table is updated by adding the receipt quantity to the OnHandBalance field. • For each receipt, the related record in the Printers table is updated by adding the receipt quantity to the YTDSalesUnits and TotalSalesUnits fields and by adding the total cost to the YTDSalesDollars and TotalSalesDollars fields. Enhancements
• Use transaction processing to update the three tables. In other words, if an error occurs while processing one of the three tables for a receipt, the updates for all three tables should be rolled back. • Use the Format function to set the receipt date to the current date when that textbox Project 9-1: Display printer and receipt data
Description
• Use the Data Environment Designer to develop a bound form that displays printer Operation
• When the application starts, the data for the first printer is displayed. Within the grid, there’s one line for each receipt record that’s related to that vendor. • To display the data for another vendor, the user selects a new vendor from the combo Specifications
• To build this form the easiest way, you need to set up two Command objects. The Printers Command object should retrieve the data for the selected printer, and the ReceiptTran Command object should retrieve the receipt transaction information for the selected printer. The ReceiptTran Command object should be a child of the Printers Command object, and the two command objects should be related by the PrinterID field. • To format the data grid as shown above, you need to use the techniques presented in Project 10-1A: Prepare a current book list
Description
• Use the Data Environment Designer and the Data Report Designer to prepare a report like the one above from the data in the Books table. Operation
• When you start the project, the report is previewed. • To print the report, the user can click on the Print button in the upper left corner. Specifications
• Start a new project, but don’t do anything with the form that the project contains. Instead, develop the report and set the Startup Object property of the project so the report is previewed when the project is started. • Sort the rows in the report by book title. • Set the DataFormat property of the ISBN number, unit price, and on hand text boxes so their values are printed as shown above. Enhancements
• Add a grand total of the on hand column at the bottom of the report. Project 10-1B: Prepare a printers and receipts summary
Description
• Use the Data Environment Designer and the Data Report Designer to add a report like the one above to your 10-1A project. This report should get its data from the Printers and ReceiptTran tables. Operation
• When you start the project, the report is previewed. • To print the report, the user can click on the Print button in the upper left corner. Specifications
• After you add this report to your 10-1A project, set the Startup Object property of the project so this report is previewed when the project is started. • Add a grand total of the receipt quantity and total cost columns at the bottom of the • Set the DataFormat property of the receipt date, receipt quantity, total cost, and summary text boxes so their values are printed as shown above. Enhancements
• Modify the report so it includes just the receipt data for the current year. Project 10-1C: Prepare a form for printing reports
Description
• Build a form for previewing and printing the reports that you developed for projects Operation
• To preview a report, the user clicks on an option button and then the Print button. Specifications
• Build the form shown above in your 10-1A project. The first two option buttons represent the reports that you developed for projects 10-1A and 10-1B. The last two option buttons represent reports that you haven’t developed yet. • If a user chooses the third or fourth option button, this message should be displayed: “This report hasn’t been developed yet.” • After you add this form to your project, change the Startup Object property of the project so this form is displayed when the project is started. Project 12-1: Create a multiple-document interface
Description
• Add two or more of the forms that you created in earlier projects to a new project that contains one parent form. Then, change your old forms to child forms, and enhance the user interface by adding menus. Specifications
• Add File, Application, and Window menus to the parent form. The File menu should contain only an Exit command. The Application menu should let you display any of the child forms. And the Window menu should let you switch from one open form to another. • Change the Exit buttons on the child forms to Close buttons. • Allow only one instance of each form. Enhancements
• Add a toolbar to the parent form. • Add a status bar to the parent form. • Add help information.

Source: http://dilbert.iiml.ac.in/~vivekg/itm/Student_Projects-II.pdf