Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2014
    Posts
    37

    Question Unanswered: Use VBA to find Combobox Bound Column name?

    Is there a way to find the Column Name (fieldname) for the BoundColumn of any combobox, given the bound column number (cboControl.BoundColumn = x)?

    Is there a way to find the Column Name (Fieldname) for the first VisibleColumn used in any combobox?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's possible. Here's a solution:
    Code:
        Dim var As Variant
        Dim strColumnList As String
        
        strColumnList = Replace(Me.Combo_1.RowSource, "select ", "")
        strColumnList = Left(strColumnList, InStr(strColumnList, " FROM") - 1)
        var = Split(strColumnList, ", ")
        MsgBox "Bound Column: " & var(Me.Combo_1.BoundColumn - 1)  ' First Column is Column 0.
        MsgBox "Name of third column: " & var(2) ' First Column is Column 0.
    Have a nice day!

  3. #3
    Join Date
    Sep 2014
    Posts
    37
    Awesome, I think that's the logic I need. I will try an implement a solution based on that and report back. THANKS!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Sep 2014
    Posts
    37

    Working: Combobox Bound Column Field name and Visual Column Field Name

    Here is working code that finds the the column name and field name for a combobox based on the bound column's .oldvalue. This is working for me. Thanks for your help.

    Code:
        Dim strSQL As String
        Dim ColumnArray As Variant 'holds the combobox column list
        Dim WidthArray As Variant 'holds the combobox column list
        Dim strColumnList As String 'holds the list of all columns
        Dim strBoundColumnName As String
        Dim strVisibleColumnList As String
        Dim strVisibleColumnName As String
        Dim i As Integer
    
                                If ctl.ControlType = acComboBox Then
                                
                                    'instead of storing the bound column value of a combo box, store the visible information
                                    'to find the visible value for the combobox's .oldvalue field we need to look it up.
    
                                    'get the controls bound column name
                                    
                                    'check to see if the control rowsource is a querystring or a named query. Get the columnnames and store in an array.
                                    If InStr(ctl.RowSource, "SELECT ") > 0 Then
                                        'rowsource is a query string
                                        strSQL = ctl.RowSource
                                        strColumnList = Replace(ctl.RowSource, "select ", "")
                                        strColumnList = Left(strColumnList, InStr(strColumnList, "FROM") - 1)
                                        ColumnArray = Split(strColumnList, ", ")
                                    Else
                                        'rowsource is a named query, get the name
                                        strColumnList = ctl.RowSource
                                        'get the querystring
                                        strSQL = CurrentDb.QueryDefs(strColumnList).sql
                                        strColumnList = Replace(strSQL, "select ", "")
                                        strColumnList = Left(strColumnList, InStr(strColumnList, "FROM") - 1)
                                        ColumnArray = Split(strColumnList, ", ")
                                    End If
                                    strBoundColumnName = ColumnArray(ctl.BoundColumn - 1)
                                    'MsgBox "the name of the bound column is: " & ColumnArray(ctl.BoundColumn - 1)
                                    
                                    'Get the controls first visbile column name
                                    strVisibleColumnList = ctl.ColumnWidths
                                    WidthArray = Split(strVisibleColumnList, ";")
                                    For i = LBound(WidthArray) To UBound(WidthArray)
                                        If WidthArray(i) <> "0" Then
                                            strVisibleColumnName = ColumnArray(i)
                                            Exit For
                                        End If
                                    Next
                                    
                                    'Use combo querystring to lookup recordset contianing values
                                    Set rstControl = New ADODB.Recordset
                                    rstControl.Open strSQL, cnn, adOpenDynamic
                                    rstControl.Find Right(strBoundColumnName, Len(strBoundColumnName) - InStr(strBoundColumnName, ".")) & " = " & CLng(ctl.OldValue)
                                    
                                    'get old field name using .oldvalue [Table].[Field] format
                                    strVisibleColumnName = Right(strVisibleColumnName, Len(strVisibleColumnName) - InStr(strVisibleColumnName, "."))
                                    
                                    'remove brackets from fieldname
                                    strVisibleColumnName = Replace(strVisibleColumnName, "[", "")
                                    strVisibleColumnName = Replace(strVisibleColumnName, "]", "")
                                    ![OldValue] = rstControl.Fields(strVisibleColumnName).Value & " (" & ctl.OldValue & ")"
                                    
                                    'clean up
                                    rstControl.Close
                                    Set rstControl = Nothing
                          
                                    ![NewValue] = ctl.Text & " (" & ctl.Value & ")"
                                Else
                                    ![OldValue] = ctl.OldValue
                                    ![NewValue] = ctl.Value
                                End If

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Another, perhaps more elegant, solution would consist in using a Querydef object and its Fields collection.

    a) If the RowSource property is a SQL expression:
    Code:
    Dim qdf As DAO.Querydef
    Set qdf = Currentdb.CreateQuerydef("")
    qdf.SQL = Me.Combo_1.RowSource
    StrBoundColumnName = qdf.Fields(Me.Combo_1.BoundColumn-1).Name
    strThirdColumnName = qdf.Fields(2).Name
    b) If the RowSource property is the name of a query:
    Code:
    Dim qdf As DAO.Querydef
    Set qdf = Currentdb.Querydefs(Me.Combo_1.RowSource)
    StrBoundColumnName = qdf.Fields(Me.Combo_1.BoundColumn-1).Name
    strThirdColumnName = qdf.Fields(2).Name
    c) If the RowSource property is the name of a table:
    Code:
    Dim qdf As DAO.Querydef
    Set qdf = Currentdb.Querydefs("")
    qdf.SQL = "SELECT * FROM " & Me.Combo_1.RowSource
    StrBoundColumnName = qdf.Fields(Me.Combo_1.BoundColumn-1).Name
    strThirdColumnName = qdf.Fields(2).Name
    Last edited by Sinndho; 09-23-14 at 19:30. Reason: Correction: must specify the .Name property of the Fields collection (no default property).
    Have a nice day!

  7. #7
    Join Date
    Sep 2014
    Posts
    37
    I like that...


    is there an elegant test for the rowsource type? (SQL, query, or table?)?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's not so elegant can be hidden

    1. A Class Module (Cls_Combo):
    Code:
    '
    ' Class Module Cls_Combo
    ' ----------------------
    '
    Option Compare Database
    Option Explicit
    
    Private m_qdf As DAO.QueryDef
    Private m_Combo As Combobox
    
    Public Sub Initialize(ByVal Combo As Combobox)
    
        Dim varType As Variant
        
        Set m_Combo = Combo
        If InStr(m_Combo.RowSource, "SELECT") > 0 Then  ' RowSource is a SQL expression.
            Set m_qdf = CurrentDb.CreateQueryDef("")
            m_qdf.SQL = m_Combo.RowSource
        Else
            varType = DLookup("Type", "MSysObjects", "Name = '" & m_Combo.RowSource & "'")
            Select Case varType
                Case 1, 4                               ' RowSource is a Table.
                    Set m_qdf = CurrentDb.CreateQueryDef("")
                    m_qdf.SQL = "SELECT * FROM " & m_Combo.RowSource & ";"
                Case 5                                  ' RowSource is a Query.
                    Set m_qdf = CurrentDb.QueryDefs(m_Combo.RowSource)
                Case Else                               ' Unknown or unhandled RowSource Type.
                    ' Error handler: Unknown or unhandled RowSource Type.
            End Select
        End If
        
    End Sub
    
    Public Property Get BoundColumnName() As String
    
        BoundColumnName = m_qdf.Fields(m_Combo.BoundColumn - 1).Name
        
    End Property
    
    Public Property Get ColumnName(ByVal ColumnIndex As Long) As String
    
        If ColumnIndex >= 0 And ColumnIndex <= m_qdf.Fields.Count Then
            ColumnName = m_qdf.Fields(ColumnIndex - 1).Name
        Else
            ' Error handler: Invalid column index.
        End If
        
    End Property
    
    Private Sub Class_Terminate()
    
        m_qdf.Close
        Set m_qdf = Nothing
        Set m_Combo = Nothing
        
    End Sub
    2. Example of use:

    a) A form with a ComboBox (Combo_1) and 2 Command Buttons (Command_GetBoundColName and Command_GetThirdColumnName)

    b) The code in the Form Module:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_clsCombo As Cls_Combo
    
    Private Sub Command_GetBoundColName_Click()
    
        MsgBox m_clsCombo.BoundColumnName
        
    End Sub
    
    Private Sub Command_GetThirdColumnName_Click()
    
        MsgBox m_clsCombo.ColumnName(3)
        
    End Sub
    
    Private Sub Form_Close()
    
        Set m_clsCombo = Nothing
        
    End Sub
    
    Private Sub Form_Load()
    
        Set m_clsCombo = New Cls_Combo
        m_clsCombo.Initialize Me.Combo_1
        
    End Sub
    Have a nice day!

  9. #9
    Join Date
    Sep 2014
    Posts
    37
    Geez... you just showed me how much I suck. That's sweet.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by slynde View Post
    Geez... you just showed me how much I suck. That's sweet.
    No, you don't. There is always something new that's worth to be learned. And that's true for everyone of us.
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •