Combo Box with Date columns from a Table

[India_Tech_Geek]

I am working with MS ACCESS 2000.

I want to display a combo box in a form which will display the column names of a particular table. I know that I can set the combo's RowSourceType to Field List and then set the RowSource to the table I want to display the fields for.

Now, if I want only the date fields to be displayed (ie the columns of the table which have Date/Time as their Data Type) in the combo-box, how can I get this done..? Is there any way I can filter out all the other columns?

 

[Jon K]

You will need to use VBA to build a field list for the RowSource of the combo box.


First you can make a reference to the "Microsoft ADO Ext. 2.5 for DDL and Security" (when the code window is open, choose menu Tools, References... and select the reference.)

Then put this ADOX code in the On Open event of the form (replacing with the correct TableName and ComboBoxName):-

Private Sub Form_Open(Cancel As Integer)

    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim col As ADOX.Column
    Dim sDateFlds As String
    
    Set cat.ActiveConnection = CurrentProject.Connection
    Set tbl = cat.Tables("[b]TableName[/b]")
    
    For Each col In tbl.Columns
      If col.Type = adDate Then
          sDateFlds = sDateFlds & col.Name & ";"
      End If
    Next col
    
    Me.[b]comboBoxName[/b].RowSourceType = "Value List"
    Me.[b]comboBoxName[/b].RowSource = sDateFlds
    
    Set cat = Nothing
    Set tbl = Nothing
 
End Sub

.

 

[India_Tech_Geek]

Thanks A Lot

Thanks Jon.. :_)
It worked..