How to add data to Combo Box using VBA

[JrMontgom]

I have been trying to add a series of dates from a table (table has duplicate dates so I need to use VBA to exclude duplicates before adding to combo box). I am using the following code:

Private Sub Form_Open(Cancel As Integer)
'fill cbxDate with unique dates found in tblCEF_Data
Dim rst As ADODB.Recordset
Dim strQry As String
Dim intCt As Integer
Dim i As Integer
Dim dTmp As Date
Dim strDate As String
Dim strList As String




On Error GoTo ErrorHandler
Const CALLER As String = " Form_frmGetQry:Form_Open "


strQry = "SELECT tblCEF_Data.Date_Of_Rpt" & _
                " FROM tblCEF_Data;"


Set rst = New ADODB.Recordset
   With rst
        .ActiveConnection = CurrentProject.Connection
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open strQry, Options:=adCmdText
   End With
   
   If rst.RecordCount > 0 Then
        intCt = rst.RecordCount
        'init the list
        Me.CbxDate.RowSourceType = "Value List"  'need this to use AddItem
         Me.CbxDate.RowSource = vbNullString
         
        
        
    With rst
        .MoveFirst
        
        For i = 1 To intCt
                Debug.Print !Date_Of_Rpt
                Debug.Print dTmp
                
                If !Date_Of_Rpt = dTmp Then
                    'do nothing duplicate
                Else
                    'not dupe add then reset var date
                    strDate = CStr(!Date_Of_Rpt)
                    strList = strDate
                    Me.CbxDate.AddItem item:=strDate
                    dTmp = !Date_Of_Rpt
                    strList = strList & ";"
                    Debug.Print Me.CbxDate.Value
                End If
        
            
            .MoveNext
        Next i
       
        
        
     End With
     Debug.Print Me.CbxDate.RowSource
    
   Else
   
   End If


Cleanup:
'put this in Cleanup:
If Not rst Is Nothing Then
        If rst.State = adStateOpen Then rst.Close
        Set rst = Nothing
    End If
   
   Exit Sub


ErrorHandler:
    MsgBox Err.Description & vbCrLf & _
           Err.Number & vbCrLf & _
           "Called By :" & CALLER & vbCrLf & _
           Err.Source, VbMsgBoxStyle.vbCritical, "Could not add new name to data base" & vbCrLf & _
            "Module Name: = " & ModuleName
            
            
                
    GoTo Cleanup








End Sub

When I open the form with the cbx the code runs but no data is visible in cbx.

[NTC]

ummmmmm.....well I see things differently. A combobox has a record source. One option is the source be a table or a query of a table(s). A query can be made DISTINCT as it's property so that there are no duplicates.

Adding records to the combobox is adding records to the table that is the ultimate source of data for the combobox.

[ItsMe]

Have you tried using Group By in your select query? Then you could jus assign the SQL to the Rowsource of the combobox.

strQry = "SELECT tblCEF_Data.Date_Of_Rpt" & _
" FROM tblCEF_Data" & _
" GROUP BY tblCEF_Data.Date_Of_Rpt;"

Me.CbxDate.RowSource = strQry

[JrMontgom]

ummmmmm.....well I see things differently. A combobox has a record source. One option is the source be a table or a query of a table(s). A query can be made DISTINCT as it's property so that there are no duplicates.

Adding records to the combobox is adding records to the table that is the ultimate source of data for the combobox.

[Reply]
I agree and when I tried to get the data using a SQL with distinct the Query produces the records I want but for some reason the combo box is blank

SELECT DISTINCT tblCEF_Data.Date_Of_Rpt
FROM tblCEF_Data
WHERE (((tblCEF_Data.DataID)>0))
ORDER BY tblCEF_Data.Date_Of_Rpt;

the query gives me 6 distinct dates which the user will use to aid him/her in selecting the subset of data he/she wants to see . However when I open the Form containing the cbx there are no dates shown, the cbx is blank!
I make the
1. Me.recordsource = the SQL seen above
2.Set the column to 1
3.Set Me.RowSourceType = "table/Query" .
Why don't the 5 dates show up in the Combo Box? If I don't use the Distinct code I get duplicate values for the dates which is why I want to use the DISTINCT code in the SQL. Any help would be appreciated. The form and all code compile.

[JrMontgom]

Have you tried using Group By in your select query? Then you could jus assign the SQL to the Rowsource of the combobox.

strQry = "SELECT tblCEF_Data.Date_Of_Rpt" & _
" FROM tblCEF_Data" & _
" GROUP BY tblCEF_Data.Date_Of_Rpt;"

Me.CbxDate.RowSource = strQry

[Reply]
Yes and it makes no difference from using Distinct I get the query to run, I get the 6 valid date records but the cbx remains blank
[/Reply]

[ItsMe]

....... I get the 6 valid date records but the cbx remains blank

How do you know you get the six valid date records if the cbx remains blank?

[JrMontgom]

How do you know you get the six valid date records if the cbx remains blank?

I run the query used in the recordsource in the Query window. I also counted the recordcount of the results and got a figure =6. I also reviewed each record using Debug.print for each [Date_Of_Rpt].

[ItsMe]

Maybe I don't know what it is you are trying to do...

If you place the following in a blank form with a combo box named "CbxDate" .....

Give your combo some property settings

Column Count: 1
Column Width: 2"
Bound Column: 1

Place the following in the form's current event or load event or a control button click event or....

strQry = "SELECT tblCEF_Data.Date_Of_Rpt" & _
" FROM tblCEF_Data" & _
" GROUP BY tblCEF_Data.Date_Of_Rpt;"

Me.CbxDate.RowSource = strQry
Me.CbxDate.Requery

[JrMontgom]

Oops sorry to take up your time. I had the column count on the Format tab set to 2 and the dimensions 0,1.5 hence since I was only extracting one field and that column was set to 0 (Don't display). Mea culpa, mea culpa mea maxima culpa. Thanks for your help

[ItsMe]

Glad you have things sorted out. The whole adodb thing threw me off. Didn't consider the combobox settings.