Custom Project Displaying the Contents of the Current and Previous Record by Using the Clone Method

1. In the Northwind database, create a form in Design view based on the Customers table (see Figure 16-4). Follow the steps below to set up the form and its control properties.

Figure 16-4: This custom form is used to demonstrate how you can use recordset cloning to read the contents of the previous record.

2. Use the Combo Box Wizard to add an unbound combo box to your form. In the Combo Box Wizard's first screen, choose the third option button: Find a record on my form based on the value I selected in my combo box, and click Next. The fields available in the Customers table should appear. Select CustomerId and CompanyName and click Next. In the next Wizard dialog, adjust the width of the combo box column to fit the company name and click Finish. Now you should see the combo box placed on your form.

Creating and Manipulating Databases with ADO

3. Set the following properties for the form's controls (properties of controls that are not listed below do not need to be set for this application to work):

Object

Property

Setting

Label1 (in front of the combo box)

Caption Tag

Select Company Name cbo

Combo0 (created by the Combo Box Wizard)

Name Tag

CboCompany cbo

Label2

Caption Tag

Previous Record PrevRec

Label3

Caption

Current Record

Rectangle: Box1

Tag

PrevRec

Label4 (in front of Text box 1)

Caption Tag

Customer ID: PrevRec

Control Source

CustIdPrev PrevRec should be blank

Label5 (in front of Text box 2)

Caption Tag

Company Name: PrevRec

Control Source

CompanyPrev PrevRec should be blank

Label6 (in front of Text box 3)

Caption Tag

Contact Name: PrevRec

Control Source

ContactPrev PrevRec should be blank

Label7 (in front of Text box 4)

Caption Tag

Contact Title: PrevRec

Control Source

TitlePrev PrevRec should be blank

Label8 (in front of Text box 5)

Caption

Customer ID:

Control Source

CustomerID CustomerID

Label9 (in front of Text box 6)

Caption

Company Name:

Control Source

CompanyName CompanyName

Label10 (in front of Text box 7)

Caption

Contact Name:

Control Source

ContactName ContactName

Label11 (in front of Text box 8)

Caption

Contact Title:

Control Source

ContactTitle ContactTitle

Part II

4. Select the form by choosing Edit | Select Form and activate the form's properties sheet to set the following properties:

Property Name

Setting

Caption

Record Comparison

Scroll Bars

Neither

Record Selectors

No

Navigation Buttons

No

5. Save the form as frmCompare.

6. Click the Combo Box control on the form to select it. Activate the Event tab in the properties sheet and notice that the entry [Event Procedure] appears next to the AfterUpdate event name. Click on this entry and then click the Ellipsis button (with three dots) to activate the Code window. Take a look at the procedure that the Combo Box Wizard has written:

Private Sub Combo0_AfterUpdate()

' Find the record that matches the control. Dim rs As Object

Set rs = Me.Recordset.Clone rs.FindFirst "[CustomerID] = '" &Me![Combo0] &

If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub

Notice that the wizard has created a clone of the form's recordset before calling the FindFirst method to locate the customer record based on the entry selected in the combo box. To ensure that the form's record is in sync with the entry selected in the combo box, the last line of code moves the form's bookmark to the same location as the recordset clone's bookmark as long as we are not at the end of file (EOF). If you comment out this last line of code, you will notice that selecting an entry in the combo box does not move the record to the selected company.

Notice that because we changed the properties of the combo box after we used the Combo Box Wizard, this code will not work.

7. Replace the references to Combo0 in the code prepared by the Combo Box Wizard with the name you assigned to the combo box's Name property. After the modification, your code should look like this:

Private Sub cboCompany_AfterUpdate()

' Find the record that matches the control. Dim rs As Object

Set rs = Me.Recordset.Clone rs.FindFirst "[CustomerID] = '" & Me![cboCompany] &

If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub

Creating and Manipulating Databases with ADO

8. After making the change in step 7, activate the combo box's properties sheet and ensure that the entry [Event Procedure] is displayed on the Event tab next to the AfterUpdate event of the combo box.

9. Save the current changes to the form.

10. Test your form by opening it in Form view. Selecting a company name from the combo box should fill the text boxes placed under the Current Record label with the selected company's data.

11. Revise the cboCompany_AfterUpdate() event procedure as shown below to have the form also display the data from the previous record.

Private Sub cboCompany_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Dim c As Control

On Error GoTo ErrHandle

Set rs = Me.Recordset.Clone rs.FindFirst "[CustomerID] = '" & Me![cboCompany] &

If Not rs.EOF Then Me.Bookmark = rs.Bookmark ' Move to the previous record in the clone ' so that we can load the previous records' ' data in the form's text boxes rs.MovePrevious If Not rs.BOF Then

For Each c In Me.Controls c.Visible = True

Next

Me.CustIdPrev = rs.Fields(0).Value Me.CompanyPrev = rs.Fields(1) Me.ContactPrev = rs.Fields(2) Me.TitlePrev = rs.Fields(3)

Else

For Each c In Me.Controls If c.Tag = "PrevRec" Then c.Visible = False End If

Next End If ExitHere:

Exit Sub ErrHandle:

MsgBox Err.Number & ":" & Err.Description Resume ExitHere End Sub

The revised cboCompany_AfterUpdate procedure ensures that the controls used to display the contents of the previous record are visible whenever the selected record is not the first record. The procedure uses the controls' Tag property to allow easy selection of controls that need to be hidden or made visible.

Part II

Before we start working with this custom project, let's write a Form_Load event procedure to ensure that only the combo box and its label are visible when the form is opened.

12. In the Form Design view, choose Edit | Select Form. In the form's properties sheet activate the Event tab, and click the Build button next to the On Load event name. In the Choose Builder dialog box, select Code Builder and click OK.

13. Enter the code for the Form_Load event procedure as shown below:

Private Sub Form_Load() Dim c As Control

For Each c In Me.Controls If c.Tag <> "cbo" Then c.Visible = False End If

Next End Sub

14. Compile your VBA project by choosing the first option in the Debug menu.

15. Save and close your form.

16. Reopen the frmCompare form and test it by choosing various company names from the combo box.

Think of other ways to improve this form. For example, add a set of controls and write additional code to display the next record.

0 0

Post a comment