Data Bound ActiveX Control and Data Repeater

Data Bound ActiveX Control and Data Repeater

ActiveX Control Interface Wizard

Each ActiveX Control contains an interface – properties, methods, and events.  ActiveX Control Interface Wizard is used to assist the author (creator) of ActiveX Control in creating properties, methods and events.

ActiveX Control interface Wizard can do the following:

  • Create property procedures for user-defined properties
  • Map user-defined properties to standard properties of Constituent controls.
  • Take care of property persistence using ReadProperties and WriteProperties

Let us create a new project to understand how to create interface for Number control using ActiveX Control Interface Wizard. Let us call this control as WizNumber.

Here are the steps to create WizNumber, which is same as Number control but built using ActiveX Control Interface Wizard:

  1. Start a new project and select ActiveX Control as the type of the project.
  2. Change Name of the UserControl to
  3. Place a textbox on UserControl

Note: We concentrate on how to use ActiveX Control Interface Wizard and not on how to write code to accomplish the task. So I do not explain how to write code for WizNumber control (because I have already done with Number control in the previous chapters and there is no need to do that again) instead we will understand how we can use Wizard to create interface.

  1. Invoke ActiveX Control Interface Wizard from Add-Ins If it is not available, use Add-In Manger to load the Wizard into Visual Basic IDE. Please see the section “Loading Property Page Wizard” in chapter 28 for detail regarding how to add an Add-in into Visual Basic IDE.
  2. Click on Next to skip the Introduction screen.

It pre-selects a list of standard properties, methods, and events that are commonly used. If you do not want them, you can unselect them by selecting unwanted names and clicking < (less than) button.

If you want to select some more from the Available Names, select the name that you want to add and click on > (greater than) button.

  1. Click on Next without unselecting or selecting anything.
  2. Wizard displays Create Custom Interface Members
  3. Click on New button to add a member.(Figure 29.1)
  4. In Add Custom Member dialog enter the name of the member and type of the member.
  5. Repeat the process to enter the following members.

 

Property                      Value

                                  MinLength

                                  MaxLength

Method                    Clear

Event                        InvalidChar

                                InvalidLength

Figure 29.1: Adding a Custom member

After you have added all members, the Wizard should look like the following.(Figure 29.2)

Figure 29.2: ActiveX Control Wizard after custom members are created.

11. Click on Next button to move to Set Mapping page, shown in figure 29.3.

Mapping enables user-defined properties to be mapped to standard properties.  Let us map MaxLength property to MaxLength property of textbox.

12. Select MaxLength property in Public Name and select Text1 as control and MaxLength as Member.

13. Repeat the process for Value Map it to Text property of textbox. See figure 29.3.

14. Once mapping is over click on Next to move to Set Attributes page.

15. Select MinLength property and change the remaining as shown in figure 29.4.

16. Click on Next button and then click on Finish

Figure 29.3: Mapping custom properties to standard properties.

Figure 29.4: Settings the attributes of MinLength property.

The code written by ActiveX Control Interface Wizard is given in listing 29.1 without any editing. Particularly concentrate on the following:

  • InitProperties Event
  • ReadProperties Event
  • WritePropeties Event
  • Property Procedures for user-defined properties

‘Default Property Values:

Const m_def_BackColor = 0

Const m_def_ForeColor = 0

Const m_def_Enabled = 0

Const m_def_BackStyle = 0

Const m_def_BorderStyle = 0

Const m_def_MinLength = 0

‘Property Variables:

Dim m_BackColor As Long

Dim m_ForeColor As Long

Dim m_Enabled As Boolean

Dim m_Font As Font

Dim m_BackStyle As Integer

Dim m_BorderStyle As Integer

Dim m_MinLength As Integer

‘Event Declarations:

Event Click()

Event DblClick()

Event KeyDown(KeyCode As Integer, Shift As Integer)

Event KeyPress(KeyAscii As Integer)

Event KeyUp(KeyCode As Integer, Shift As Integer)

Event MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

Event MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

Event MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

Event InvalidChar()

Event InvalidLenth()

 

‘WARNING! DO NOT REMOVE OR MODIFY THE FOLLOWING COMMENTED LINES!

‘MemberInfo=8,0,0,0

Public Property Get BackColor() As Long

BackColor = m_BackColor

End Property

 

Public Property Let BackColor(ByVal New_BackColor As Long)

m_BackColor = New_BackColor

PropertyChanged “BackColor”

End Property

 

‘WARNING! DO NOT REMOVE OR MODIFY THE FOLLOWING COMMENTED LINES!

‘MemberInfo=8,0,0,0

Public Property Get ForeColor() As Long

ForeColor = m_ForeColor

End Property

 

Public Property Let ForeColor(ByVal New_ForeColor As Long)

m_ForeColor = New_ForeColor

PropertyChanged “ForeColor”

End Property

 

‘WARNING! DO NOT REMOVE OR MODIFY THE FOLLOWING COMMENTED LINES!

‘MemberInfo=0,0,0,0

Public Property Get Enabled() As Boolean

Enabled = m_Enabled

End Property

 

Public Property Let Enabled(ByVal New_Enabled As Boolean)

m_Enabled = New_Enabled

PropertyChanged “Enabled”

End Property

 

‘WARNING! DO NOT REMOVE OR MODIFY THE FOLLOWING COMMENTED LINES!

‘MemberInfo=6,0,0,0

Public Property Get Font() As Font

Set Font = m_Font

End Property

 

Public Property Set Font(ByVal New_Font As Font)

Set m_Font = New_Font

PropertyChanged “Font”

End Property

 

‘WARNING! DO NOT REMOVE OR MODIFY THE FOLLOWING COMMENTED LINES!

‘MemberInfo=7,0,0,0

Public Property Get BackStyle() As Integer

BackStyle = m_BackStyle

End Property

 

Public Property Let BackStyle(ByVal New_BackStyle As Integer)

m_BackStyle = New_BackStyle

PropertyChanged “BackStyle”

End Property

 

‘WARNING! DO NOT REMOVE OR MODIFY THE FOLLOWING COMMENTED LINES!

‘MemberInfo=7,0,0,0

Public Property Get BorderStyle() As Integer

BorderStyle = m_BorderStyle

End Property

 

Public Property Let BorderStyle(ByVal New_BorderStyle As Integer)

m_BorderStyle = New_BorderStyle

PropertyChanged “BorderStyle”

End Property

 

‘WARNING! DO NOT REMOVE OR MODIFY THE FOLLOWING COMMENTED LINES!

‘MemberInfo=5

Public Sub Refresh()

 

End Sub

 

‘WARNING! DO NOT REMOVE OR MODIFY THE FOLLOWING COMMENTED LINES!

‘MappingInfo=Text1,Text1,-1,Text

Public Property Get Value() As String

Value = Text1.Text

End Property

 

Public Property Let Value(ByVal New_Value As String)

Text1.Text() = New_Value

PropertyChanged “Value”

End Property

 

‘WARNING! DO NOT REMOVE OR MODIFY THE FOLLOWING COMMENTED LINES!

‘MemberInfo=7,0,0,0

Public Property Get MinLength() As Integer

MinLength = m_MinLength

End Property

 

Public Property Let MinLength(ByVal New_MinLength As Integer)

m_MinLength = New_MinLength

PropertyChanged “MinLength”

End Property

 

‘WARNING! DO NOT REMOVE OR MODIFY THE FOLLOWING COMMENTED LINES!

‘MappingInfo=Text1,Text1,-1,MaxLength

Public Property Get MaxLength() As Long

MaxLength = Text1.MaxLength

End Property

 

Public Property Let MaxLength(ByVal New_MaxLength As Long)

Text1.MaxLength() = New_MaxLength

PropertyChanged “MaxLength”

End Property

 

‘WARNING! DO NOT REMOVE OR MODIFY THE FOLLOWING COMMENTED LINES!

‘MemberInfo=14

Public Function Clear() As Variant

 

End Function

 

‘Initialize Properties for User Control

Private Sub UserControl_InitProperties()

m_BackColor = m_def_BackColor

m_ForeColor = m_def_ForeColor

m_Enabled = m_def_Enabled

Set m_Font = Ambient.Font

m_BackStyle = m_def_BackStyle

m_BorderStyle = m_def_BorderStyle

m_MinLength = m_def_MinLength

End Sub

 

‘Load property values from storage

Private Sub UserControl_ReadProperties(PropBag As PropertyBag)

 

m_BackColor = PropBag.ReadProperty(“BackColor”, m_def_BackColor)

m_ForeColor = PropBag.ReadProperty(“ForeColor”, m_def_ForeColor)

m_Enabled = PropBag.ReadProperty(“Enabled”, m_def_Enabled)

Set m_Font = PropBag.ReadProperty(“Font”, Ambient.Font)

m_BackStyle = PropBag.ReadProperty(“BackStyle”, m_def_BackStyle)

m_BorderStyle = PropBag.ReadProperty(“BorderStyle”, m_def_BorderStyle)

Text1.Text = PropBag.ReadProperty(“Value”, “Text1”)

m_MinLength = PropBag.ReadProperty(“MinLength”, m_def_MinLength)

Text1.MaxLength = PropBag.ReadProperty(“MaxLength”, 0)

End Sub

 

‘Write property values to storage

Private Sub UserControl_WriteProperties(PropBag As PropertyBag)

Call PropBag.WriteProperty(“BackColor”, m_BackColor, m_def_BackColor)

Call PropBag.WriteProperty(“ForeColor”, m_ForeColor, m_def_ForeColor)

Call PropBag.WriteProperty(“Enabled”, m_Enabled, m_def_Enabled)

Call PropBag.WriteProperty(“Font”, m_Font, Ambient.Font)

Call PropBag.WriteProperty(“BackStyle”, m_BackStyle, m_def_BackStyle)

Call PropBag.WriteProperty(“BorderStyle”, m_BorderStyle, m_def_BorderStyle)

Call PropBag.WriteProperty(“Value”, Text1.Text, “Text1”)

Call PropBag.WriteProperty(“MinLength”, m_MinLength, m_def_MinLength)

Call PropBag.WriteProperty(“MaxLength”, Text1.MaxLength, 0)

End Sub

Listing  29.1: Code written by ActiveX Control Interface Wizard.

You can customize the code written by a Active Control Interface Wizard. The code written by the wizard is a good starting point to start creating an ActiveX control.

Working with Data Repeater

A DataRepeater is a container of a databound user control. It displays multiple instances of user control and also scrolls the instances, if required.  Each instance of the control represents a single record from the associated recordset.

The following are the major steps in using a DataRepeater.

  1. Create a data bound ActiveX control to deal with the required data.
  2. Load data bound ActiveX Control into the project using Components dialog box.
  3. Place a DataRepeater control into the project.
  4. Change RepeatedControlName property of DataRepater to the required ActiveX Control.
  5. Add a data source, such as the ADO Data Control, to the form, and connect it to a data provider.
  6. Change DataRepeater control’s DataSource property to the data source.
  7. Right-click the DataRepeater control and click DataRepeater Properties.
  8. Select RepeaterBindings tab and set the PropertyName to an appropriate DataField, and click the Add Repeat the process for each data bound property of ActiveX control.

Let us understand the entire process by creating a DataRepater to display the details of products from PRODUCTS table. Each row of the repeater will display details of one product. User is allowed to view and change the details. The following are the major steps involved in this:

  • Create an ActiveX Control that displays details of Products using five textboxes (prodno, proddes, qoh, rpu, type). This control contains four databound properties, where each property corresponds to a field in products table.
  • Create a connection to Products Table of PRODUCTS.MDB database using an ADODC.
  • Place a DataRepeater control on the form and connect it to ActiveX Control and ADODC.

Creating ActiveX Control – ProductControl

The following are the steps required creating the required ActiveX Control, which is bound to PRODUCTS table of PRODUCT.MDB.

  1. Start a new project and select ActiveX Control as the type of the project.
  2. Invoke UserControl designer and place five text boxes and change the following properties.

 

Object Property

Value

UserControl Name Productcontrol
BorderStyle 1-fixed single
Text1 Name TxtProdno
Text “”
Text2 Name TxtProddesc
Text “”
Text3 Name TxtQoh
Text “”
Text4 Name TxtRpu
Text “”
Text5 Name TxtPtype
Text “”

Creating required properties

Invoke  ActiveX Control Interface Wizard to create the following properties.

  • Prodno
  • Proddesc
  • Qoh
  • Rpu
  • Ptype

Map each property to text property of the corresponding text box.  Here is the table showing the mapping information. If you are not familiar with using ActiveX Control Interface Wizard, please refer to chapter 28.

Property of the control Control to map to Property of mapped control
Prodno Txtprodno Text
Prodesc Txtproddesc Text
Qoh Txtqoh Text
Rate Txtrate Text
Ptype Txtptype Text

ActiveX Control Interface Wizard adds property procedures (Property Set and Property Get), ReadProperties event procedure and WriteProperties event procedure to the control.

Adding PropertyChanged method

Whenever user changes any of these text boxes, the corresponding property should be marked as changed. For example, if user changed text in txtProdDesc control then the property that is mapped to this text box – ProdDesc – should be marked as changed.

Change event of the text box is used to mark property as changed using PropertyChanged method. The following is the code required for all five properties.

Private Sub txtProdno_Change()

PropertyChanged “prodno”

End Sub

 

Private Sub txtproddesc_Change()

PropertyChanged “proddesc”

End Sub

 

Private Sub txtPtype_Change()

PropertyChanged “ptype”

End Sub

 

Private Sub txtQoh_Change()

PropertyChanged “qoh”

End Sub

 

Private Sub txtRpu_Change()

PropertyChanged “rpu”

End Sub

Listing 29.1: Code for property procedures.

Making properties data bound

The five properties that we have added to ProductControl should be databound. To make these properties data bound, following the procedure given below.

 

  1. Select Tools -> Procedure Attributes
  2. In Procedure Attributes window select ProdNo from Name dropdown listbox.
  3. Enter “Returns/sets product number” as the description in Description
  4. Click on “Advanced>>“ button to expand Procedure Attributes window.

Figure 29.5: Procedure Attributes.

5. Turn on Property is data bound check box  in Data Binding

6. Also turn on Show in DataBindings collection at design time.

Figure 29.6: Advanced Attributes of a procedure

7. Then select Proddesc and change all the properties as mentioned above.

8. Repeat this process for qoh, rate and ptype properties also.

9. Then click on Ok button in Procedure Attributes window.

10. Change the following properties of ActiveX control project.

Project name               ProdCtrl         

Project Description      ActiveX Control for Product record by P.Srikanth.

11. Save the project and UserControl under following names

Project             ProductControl.vbp    

UserControl     ProductControl.ctl

Create ProductContol.ocx by selecting File -> Make ProdcutControl.ocx.

That is the end of creation of ActiveX control for product record. Now let us see how to use it in DataRepeater.

Using ProductControl in DataRepeater

We have so far seen how to create an ActiveX control for datarepeater. Now let us see how to create a project that uses DataRepeater and ProdcutControl controls.

  1. Start a new project and select Standard Exe as the type of the project.
  2. Load the following ActiveX controls using Project-> Components
  • Microsoft ADO Data Control 6.0 (OLEDB)
  • Microsoft DataRepeater Control 6.0 (OLEDB)
  1. Place ADODC and Data Repeater on the form and arrange them one below the other (see figure 29.7).
  2. Use ADODC to connect to “D:\VBBOOK\PRODCUTS.MDB.”
  3. Change the Type of Record Source to 2- adCmdTable and  select Table or Stored Procedure name to
  4. Change DataSource property of DataRepeater control to ADODC1.
  5. Select RepeatedControlName property and click on down arrow to invoke list of ActiveX controls.
  6. Select ProdCtrl.ProductControl from the list of ActiveX Controls.
  7. Right click on DataRepeater control and select properties from popup menu to invoke property pages.
  8. Select RepeaterBindings Tab to connect  properties of ActiveX Control ( ProductControl ) to fields of ADODC1.
  9. Select Prodno from Property Name drop down and also select Prodno from DataField drop down.
  10. Then click on Add button at the buttom.
  11. Then an entry is added to RepeaterBindings
  12. Do the same for remaining properties. The bindings are as follows.

 

ProdDesc         proddesc

Qoh                 qoh

Rpu                  Rpu

Ptype               ptype

Figure 29.7: Data Repeater containing ProductControl.

15. Once the process is complete then close the property pages of the DataRepeater control.

DataRepeater is used to display multiple occurrences of an ActiveX control. In the above example, DataRepeater displays five instances of ProductControl ActiveX control. You can make changes to the PRODUCTS table by changing data in ProductControl ActiveX control in DataRepeater.

Test Run

  1. Run the project using F5.

As soon as the project starts the form should look like figure 29.7. You can use scrollbar on the right to move up and down. You can also use Data Control to do the same.

  1. Move to product where product number is 6 and change the description to Oracle8i.

The ActiveX control marks ProdDesc property as changed (see code in listing 29.1).

  1. Move to a different record.

This will make the change made to ProdDesc to the underlying table.

  1. To test whether change has really been made, quit the project and restart it. If change is found then everything is working fine.