Project 5: Accessing Databases

In this lab, you will build a customer order information application. The main form of the application displays customer information, while a second form displays the order history for a selected customer.

Exercises

In this exercise, you will use the Data Form Wizard to create a form that will display customer information.

In this exercise, you will use the Data Form Wizard to design a form that will display order information. Then, you will add a command button to the frmCustomers form to show the FrmOrders form.

In this exercise, you will link the two forms together so that only the orders for a selected customer are displayed in the FrmOrders form.

In this exercise, you will review the code created by the Data Form Wizard.

Exercise 1: Creating a Customer Information Form with the Data Form Wizard

In this exercise, you will use the Data Form Wizard to create a form that will display customer information.

Start a new Visual Basic project.

If the Data Form Wizard is not a menu item on the Add-Ins menu, use the Add-In Manager to load the Data Form Wizard add-in:

1. On the Add-Ins menu, click Add-In Manager.

2. Select Data Form Wizard in the Available Add-Ins list., and click OK.

1. On the Add-Ins menu, click Data Form Wizard.

2. Click Next on the Data Form Wizard introduction screen.

            (if asked to load a profile for your settings, select (none) and click next).

3. Select Access from the list of database types, and click Next.

4. Browse for the Nwind.mdb database (most likely in c:\Program Files\Microsoft Visual Studio\Vb98) and click Next.

            (if you can't find Nwind.mdb in the default directory, use the 'Find' command from Windows Explorer).

5. Name the form: frmCustomers.  Select Single record and make sure ADO Data Control is selected, click Next.

6. In the Record Source drop-down list box, select Customers.

7. Copy the Customer ID and the Company Name fields from the Available Fields list to the Selected Fields list, and click Next.

8. Click Select All and then click Next.

9. Leave the default profile name of None, and click Finish.

10. Click OK on the Data Form Created dialog box.

In this application, we will not be using the default form that Visual Basic created.

1. In the Project Explorer window, select Form1.

2. On the Project menu, click Remove Form1.

3. On the Project menu, click Project1 Properties.

4. Set the Startup Object to the frmCustomers form you just created, and then click OK.

1. Save the customer form by selecting Save frmCustomers As on the File menu.

2. Save the project by selecting Save Project As on the File menu.

3. Test your application. Use the forward and backward buttons on the Data control to cycle through some records in the database. Change some fields of the database and click the Update button. Click Close to end the application.

Exercise 2: Creating an Order Information Form with the Data Form Wizard

In this exercise, you will use the Data Form Wizard to design a form that will display order information. Then, you will add a command button to the frmCustomers form to show the frmOrders form.

1. Use the Data Form Wizard to access the Nwind.mdb database table 'Orders'.

2.  Name the form: frmOrders.  Instead of using the 'Single Record' form layout, choose Grid (Datasheet) for this form.

3.  Copy the Customer ID, Order ID, Order Date, and Required Date fields from the Available Fields list to the Selected Fields list, and click Next.

4. Leave the default profile name of None, and click Finish.

1. Widen the frmCustomers form and add an Orders command button:

2. In the Click event of the Orders command button, display the frmOrders form as modal:

frmOrders.Show vbModal

1. Save the frmOrders form by clicking Save frmOrders As on the File menu.

2. Run the application. Click the Orders button, and scroll through some of the records in the Orders table and try resizing the form. Click Close on the FrmOrders form to return to the frmCustomers form.

3. Close the running application.

Exercise 3: Creating a SQL Statement

In this exercise, you will link the two forms together so that only the orders for a selected customer are displayed in the frmOrders form.

1. Open the Code Editor window for frmOrders.

2. In the General Declarations section, create a Public String variable, gstrCustomerID.

This variable will be set from the frmCustomers form.

3. In the Form_Load event procedure for frmOrders:

1. View the frmCustomers form.

2. Edit the Click event procedure for the Orders button.

3. Set the Public variable in frmOrders, gstrCustomerID, equal to the current text in the Customer ID field before showing the frmOrders form:

frmOrders.gstrCustomerID = txtFields(0).Text

1. Save your project.

2. Run your application. Cycle through a few of the companies, clicking Orders. Are all of the records displayed in the frmOrders form for the selected company? Click Close on the frmOrders form to return to the frmCustomers form.

3. Close the running application.

Exercise 4: Reviewing Code Created by the Data Form Wizard

The Data Form Wizard adds code as well as controls to the form. In this exercise, you will review the code created by the Data Form Wizard.

1. Look at the code for the Add, Delete, Refresh, Update, and Close buttons.

2. Look at the code written for the Data control event procedures: Error, Reposition and Validate.

1. Add a Find command button to frmCustomer:

2. In the Click event of the Find command button: