Fill Data in Combo Box Control

[hariharanmca]

1. Front-end is VB 6.0
2. Back-end MS Access or Sql Server 2000
3. StrSql Query should contain only 1 or 2 fields ie. Data and Data Index Field
4. Microsoft ActiveX Data Object 2.7 or 2.8

[hariharanmca]

[CODE=vb]
'============== ========= Filling Combo Controls with Qrys 24-01-2007
Public Function FillComboBox(pS trQry As String, cmbFillCombo As ComboBox) As Boolean
On Error GoTo Err1
'Declaration Part
Dim rstTemp As New ADODB.Recordset
Dim lngInc As Long
'Clear combo Box
cmbFillCombo.Cl ear
'Open and execute the given query
Set rstTemp = objCon.Execute( pStrQry)
If rstTemp.RecordC ount > 0 Then
' If field Count is 2 ie. field 0 should data which you need to show
' And field 1 should data Index which you need to Store in Foreign Key
If rstTemp.Fields. Count = 2 Then
For lngInc = 1 To rstTemp.RecordC ount
cmbFillCombo.Ad dItem rstTemp.Fields( 0)
cmbFillCombo.It emData(cmbFillC ombo.NewIndex) = rstTemp.Fields( 1)
rstTemp.MoveNex t
Next lngInc
ElseIf rstTemp.Fields. Count = 1 Then ' If field Count is 1 ie. field only 0 should data which you need to show
For lngInc = 1 To rstTemp.RecordC ount
cmbFillCombo.Ad dItem rstTemp.Fields( 0)
rstTemp.MoveNex t
Next lngInc
End If
End If
' Return True if Error Not Thrown
FillComboBox = True
Exit Function
Err1:
' Return False if Error Thrown
FillComboBox = False
MsgBox Err.Number & " - " & Err.Description , vbCritical, "Error Message"
End Function[/CODE]

This routine assumes a currently open connection, objCon

[CODE=vb]
Publice objCon As ADODB.Connectio n[/CODE]

At project startup pass the connection string to that objCon
and open that connection with your own provider like DSN provider or Jet 4.0

For Jet 4.0
-------------------
[CODE=vb]objCon.Connecti onstring =" Provider=Micros oft.Jet.OLEDB.4 .0;Data Source=<Databas e Path>\<file Name.mdb>;Persi st Security Info=False"
objCon.Open()[/CODE]
For DSN
--------------
[CODE=vb]objCon.Open " DSN=<DsnName>", "<Username> ", "Password"[/CODE]