Function PopulateComboboxFromSQL(sSQL As String, objCombobox As MSForms.ComboBox, _
Optional bShowError As Boolean = False, _
Optional iSelectedItem As Long = -1, _
Optional sFont As String = "Verdana", _
Optional lFontSize As Long = 10, _
Optional BoundCol As Long = 1, _
Optional ColWidths As String, _
Optional LstWidth As Variant, _
Optional iStyle As fmStyle = 2, _
Optional iShowDropButtonWhen As fmShowDropButtonWhen = 1) As Long
'
' fmStyle has 2 value:
' fmStyleDropDownCombo, value 0, allow user enter new value to combobox
' fmStyleDropDownList, value 2, does not allow user enter new value to combobox
'
' fmShowDropButtonWhen has 3 value
' fmShowDropButtonWhenAlways, value 2, always show DropButton
' fmShowDropButtonWhenFocus, value 1, when focus only
' fmShowDropButtonWhenNever, value 0, never show DropButton
'Note that:
'BoundColumn : Cot lay du lieu ve/Column to get data
'ColumnCount : is defined by rst.Fields.Count
'ColumnWidths : is the string to define the width of each column
'LstWidth : is the width of the list
'
'Setting ColumnWidths:
'90;72;90 > The first column is 90 points (1.25 inch); the second column is 72 points (1 inch); the third column is 90 points.
'6 cm;0;6 cm > The first column is 6 centimeters; the second column is hidden; the third column is 6 centimeters. Because part of the third column is visible, a horizontal scroll bar appears.
'1.5 in;0;2.5 > in The first column is 1.5 inches, the second column is hidden, and the third column is 2.5 inches.
'2 in;;2 > in The first column is 2 inches, the second column is 1 inch (default), and the third column is 2 inches. Because only half of the third column is visible, a horizontal scroll bar appears.
'(Blank) > All three columns are the same width (1.33 inches).
'
'
' _ Check Is Nothing. Ex: If RsData Is Nothing Then ...
' + Check Empty If RsData.BOF And RsData.EOF Then ...
' + Check the quantity of records if you want to know before other action
' If RsData.Recordcount>0 Then
'
' If rsData Is Nothing Then
' 'Recordset chua duoc khoi tao
' MsgBox "The recordset did not create.",vbOKOnly, mcsAppName
' ElseIf rsData.EOF And rsData.BOF Then
' 'Khong co record nao thoa dieu kien cua cau SQL
' MsgBox "There are no any data to export.", vbOKOnly, mcsAppName
' Else
' 'Xuat du lieu ra
' Call RecordsetToRange(rsData, "REPORT")
' End If
'How to get back the value from ComboBox, when the user click onto the ComboBox?
'We will use the property List and ListIndex to get them back.
'The ListIndex property will return the order of item that you have selected
'and remember that the ListIndex will start with 0,1,2,...
'And the List property will return the item in which column that you want.
'also remember that this property start from 0,1,2,...
'so, let say, you want to get the value of the selected item, column 3,
'the code as following
' Msgbox ComboBox1.List(ComboBox1.ListIndex, 2)
Dim Rst As Object
Dim rcArray As Variant
On Error GoTo ErrorHandler
'Check the object name
If TypeName(objCombobox) <> "ComboBox" Then GoTo ErrorHandler
Set Rst = SqlGetRecordset(gcnAccess, sSQL, , , bShowError)
'Check status of rst
If Rst Is Nothing Then
'Do nothing
ElseIf Rst.EOF And Rst.BOF Then
'There are no any records according to this condition
'so Do nothing
Else
'Filling in the combobox
rcArray = Rst.getrows
With objCombobox
.Clear
.ColumnCount = Rst.fields.Count
If LBound(rcArray, 2) = UBound(rcArray, 2) Then
.AddItem rcArray(LBound(rcArray, 1), LBound(rcArray, 2))
.List(0, 1) = rcArray(LBound(rcArray, 1) + 1, LBound(rcArray, 2))
Else
.List = TransposeDim(rcArray)
End If
.ListIndex = iSelectedItem
.BoundColumn = BoundCol
.Font = sFont
.Font.Size = lFontSize
If Not IsMissing(LstWidth) Then
.ListWidth = LstWidth
End If
.ColumnWidths = ColWidths
.Style = iStyle
.ShowDropButtonWhen = iShowDropButtonWhen
End With
End If
PopulateComboboxFromSQL = 1
ErrorExit:
Set Rst = Nothing
Exit Function
ErrorHandler:
PopulateComboboxFromSQL = 0
If bCentralErrorHandler(mcsModName, "PopulateComboboxFromSQL", , False) Then
Stop
Resume
Else
Resume ErrorExit
End If
End Function
