steps to use this code
1.Open ActiveX control project
2.Add textbox and List box
3.compile the Control
4.Add the control to ur form
5.Configure the properties on this control.
ie connectionString
field
table
This control is filled all the values from field "field" from table "table" with the given connection string (use sql server back end)
5.in form load event
call filllistbox
that's all
I am coming up with improved version very soon
please wait for some more days
-arun
Option Explicit
Private Const m_WIDTH = 2000
Private Const m_HEIGHT = 1500
Private m_ConnString As String
Private m_Table As String
Private m_Field As String
Private m_IDField As String
Private Sub Text1_Change()
Dim str As String
Dim strLst As String
Dim Coll() As String
Dim FlagEqual As Boolean
Dim i As Integer
i = 0
If Text1 = "" Then
List1.Selected(0) = True
Exit Sub
Else
str = Trim(Text1)
While i < List1.ListCount
strLst = Left(Trim(List1.List(i)), Len(str))
If StrComp(UCase(str), UCase(strLst), vbTextCompare) = False Then
FlagEqual = True
End If
If FlagEqual Then
List1.Selected(i) = True
Exit Sub
End If
i = i + 1
Wend
End If
End Sub
Private Sub Usercontrol_Initialize()
Text1.Height = 300
Text1.Width = m_WIDTH
Text1.Top = 0
Text1.Left = 0
List1.Left = 0
List1.Top = 300
List1.Width = m_WIDTH
List1.Height = m_HEIGHT - 300
UserControl.Height = m_HEIGHT
UserControl.Width = m_WIDTH
UserControl.Height = m_HEIGHT
UserControl.Width = m_WIDTH
End Sub
Public Property Get Table() As Variant
Table = m_Table
End Property
Public Property Let Table(ByVal vNewValue As Variant)
m_Table = vNewValue
UserControl.PropertyChanged
End Property
Public Property Get Field() As Variant
Field = m_Field
End Property
Public Property Let Field(ByVal vNewValue As Variant)
m_Field = vNewValue
UserControl.PropertyChanged
End Property
Public Property Get ConnectionString() As Variant
ConnectionString = m_ConnString
End Property
Public Property Let ConnectionString(ByVal vNewValue As Variant)
m_ConnString = vNewValue
UserControl.PropertyChanged
End Property
Private Sub Usercontrol_ReadProperties(PropBag As PropertyBag)
m_ConnString = PropBag.ReadProperty("connstr")
m_Field = PropBag.ReadProperty("Fld")
m_Table = PropBag.ReadProperty("Tab")
m_IDField = PropBag.ReadProperty("idfld")
End Sub
Private Sub Usercontrol_Resize()
Text1.Width = UserControl.Width
List1.Width = UserControl.Width
Text1.Height = 300
If UserControl.Width >= 300 Then
List1.Height = UserControl.Height - 300
Else
List1.Height = m_HEIGHT
End If
End Sub
Private Sub Usercontrol_WriteProperties(PropBag As PropertyBag)
PropBag.WriteProperty "connstr", m_ConnString
PropBag.WriteProperty "Fld", m_Field
PropBag.WriteProperty "Tab", m_Table
PropBag.WriteProperty "IdFld", m_IDField
End Sub
Public Function FillListBox() As Boolean
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
Dim i As Integer
On Error Resume Next
FillListBox = False
If ConnectionString = "" Then
Exit Function
End If
If Table = "" Then
Exit Function
End If
i = 0
sql = "SELECT " & m_Field & " FROM " & m_Table & " order by " & m_Field
cn.Open m_ConnString
Set rs = cn.Execute(sql)
While Not rs.EOF
List1.AddItem rs.Fields(0).Value, i
i = i + 1
rs.MoveNext
Wend
FillListBox = True
End Function
Public Property Get IDField() As Variant
IDField = m_IDField
End Property
Public Property Let IDField(ByVal vNewValue As Variant)
m_IDField = vNewValue
UserControl.PropertyChanged
End Property
Comments