Populate Combo Box from Table based on Field Type

Hi,

I have an access application that I allow users to import data and match it up with data in the application based on a unique identifier.  I'm building a dynamic filter that will allow users to pick which table column to pick then to enter a criteria to use for the filter.  So what I'm looking for is a way to populate an Access form combo box from an Access table with two criteria: 1) one combo box can not be date types, 2) one combo box needs to only display date types (so a valid date range can be used).

I've googled a few days and haven't quite found what I'm looking for.

This seems to be able to identify types in a table: http://bytes.com/topic/access/answers/601180-find-field-data-type-vba

This seems to be able to identify the names of a column:
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("Table1")
Dim fld As DAO.Field
For Each fld In rs1.Fields
    MsgBox (fld.Name)
Next
Set fld = Nothing

If needed, when the page opens it could create a table then i could create queries to record the column name and the column type then the combo boxes could then just have a query for the column types. There might be a more dynamic or elegant way to do this, but I'm not finding any examples that might show how to create a table of column names.
atljarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leigh PurvisDatabase DeveloperCommented:
Hi

You could do the same thing to a TableDef, there's no need to open a recordset to determine data types.
But data types are, indeed, what's stopping you from just using a Field List combo list type.

I see no problem with loading your table and querying on type.
Alternatively - load the combo directly, either using a Value list - or create a disconnected recordset iteratively filled with the TableDef field propertyies and assing that to the combos.
(You could filter once for non-date types and again for date types.)

There's no system table to read field data types - so an iterative method is about as elegant as you'll get. ;-)

Cheers
0
atljarmanAuthor Commented:
Thanks LPurvis.  Good to know that this works.  Can you show an example?
0
Leigh PurvisDatabase DeveloperCommented:
Of which? ;-)

Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim strList As String

    Set db = CurrentDb()
    Set tdf = db("TableName")
    
    For Each fld In tdf.Fields
        If fld.Type = dbDate Then
            strList = strList & ";" & fld.name
        End If
    Next
    
    Me.cboList.RowSourceType = "Value List"
    Me.cboList.RowSource = Mid(strList, 2)

Open in new window


And obviously, it'd be <> dbDate to include the non-date type ones.
You could include the data types in the list too if needed for the non date list (to distinguish between numeric and text types for subsequent criteria delimitation).

The recordset method is more fun - but also more effort for little gain.

You could have
   
Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim rst As New ADODB.Recordset
    
    rst.Fields.Append "FieldName", adVarChar, 100
    rst.Fields.Append "FieldDataType", adInteger
    rst.CursorLocation = adUseClient
    rst.LockType = adLockOptimistic
    rst.Open
    
    Set db = CurrentDb()
    Set tdf = db("TableName")
    
    For Each fld In tdf.Fields
        rst.AddNew
        rst!FieldName = fld.name
        rst!FieldDataType = fld.Type
    Next
    
    Me.cboList1.RowSourceType = "Table/Query"
    rst.Filter = "FieldDataType = " & dbDate
    Set Me.cboList1.Recordset = rst
    
    Me.cboList2.RowSourceType = "Table/Query"
    rst.Filter = "FieldDataType <> " & dbDate
    Set Me.cboList2.Recordset = rst

Open in new window


Which mixes and matches DAO and ADO - but that's fine.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

atljarmanAuthor Commented:
Ok.

So I created a new form and added a Como Box that I populated with the value "Test" and named it myField for the non date field.  I copied it and renamed the second field for the date myField2.  The table that I'm pulling from is called finalsheet.   In the OnLoad funciton of the form I put in:

Private Sub Form_Load()
Dim strtemp As String, strtemp2 As String
strtemp = fTableCaptions("finalsheet", "NoDates")
Me.myField.RowSource = strtemp
bFindLastKeySpace = False

strtemp2 = fTableCaptions("finalsheet", "DatesOnly")
Me.MyField2.RowSource = strtemp2
bFindLastKeySpace = False
End Sub

Open in new window


The I added a module and added this:
Public Function fTableCaptions(strTable As String, dateOnly As String) As String
On Error GoTo Err_fTableCaptions

    Dim i As Integer, fd As Object, strRet As String, strCaption As String, strType As String
    With CurrentDb
        With .TableDefs(strTable)
            For Each fd In .Fields
                On Error Resume Next
                strCaption = fd.Properties("Caption")
                '10 = Text, 8 = Date, 7 = Number
                strType = fd.Properties("Type")
                If Err <> 0 Then
                    strCaption = fd.Name
                    Err = 0
                End If
                On Error GoTo Err_fTableCaptions
                
                'If dateOnly is dates and value is a date or dateonly, then include in combobox
                'Or if dateOnly is NoDates and not of date type then include in combobox
                If (dateOnly = "DatesOnly" And strType = "8") Or dateOnly = "All" Then
                    strRet = strRet & IIf(Len(strRet) > 0, ";", vbNullString) & strCaption
                ElseIf dateOnly = "NoDates" And strType <> "8" Then
                    strRet = strRet & IIf(Len(strRet) > 0, ";", vbNullString) & strCaption
                End If

            Next fd
        End With
    End With

Exit_fTableCaptions:
    fTableCaptions = strRet
    Exit Function

Err_fTableCaptions:
    Select Case Err.Number
    Case Else
        MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
               "Description: " & Err.Description & vbNewLine & vbNewLine & _
               "Procedure: fTableCaptions" & vbNewLine & _
               IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
               "Module: basTest", , "Error: " & Err.Number
    End Select
    Resume Exit_fTableCaptions
        
End Function

Open in new window


Even though it says "Captions" here, the captions are blank so the field names appear?  I tested and the Name of the field works with "Name" or "Caption"  Just to test, I changed "Name" to "Description" then added a description to one of the fields and the Description appeared.

How would you changed the function above so that if the description existed, to add the Description to the combo box, add the name if it does not exist?  I think you helped me answer my question, but I thought maybe we could make a complete answer for someone else searching.  Another example I found which this is based can be found here: http://www.utteraccess.com/forum/Populate-Combobox-Field-t1969833.html&st=20

Not sure how long this link will be valid.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leigh PurvisDatabase DeveloperCommented:
Hi

The Desccription property simply doesn't exist unless it has been added to the field.  (It usually gets added through the Access UI.)
Consequently you'll get a runtime error referencing it for any field which doesn't have one.

The easiest method for this kind of thing is likely to use a dedicated function to check.
For example:
Function fGetDescription(objDataObject As Object) As String
On Error Resume Next

    fGetDescription = objDataObject.Properties("Description")
    If Len(fGetDescription) = 0 Then
        fGetDescription = objDataObject.Properties("Name")
    End If
    
End Function

Open in new window


It's delibertely vague, so you could pass a range of objects: a TableDef, a Field, a QueryDef...
You'd call out to that function rather than just assign a value in your procedure.
e.g.
strCaption = fGetDescription(fd)

Not vital of course.  But a useful method to add to.

Cheers
0
atljarmanAuthor Commented:
Thanks for your help on this.  Sorry that I took so long to award points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.