I want to share something with the forum as my thanks for the help I have received from the Forum members and Rich.  I developed a handy function (and some related helper functions) to solve a common task that I have related to ComboBoxes that get their values from another table.  

A typical example would be a salesRegionCmb that gives the user the option of picking a Sales Region from a salesRegionTbl.  The ComboBox Control would show the user a text list of regions [salesRegion] from the table, but would store only the primary key index [salesRegionID] from the table.  Often there is a need to get the text value that the user sees displayed rather than the key value, such in MsgBoxes or journal entries.  The typical way to deal with this is to use a DLookup to find salesRegion in salesRegionTbl where the key = salesRegionCmb.  This can be a lot of typing (and potential mistyping) especially if these kinds of ComboBox lookups are desired many times in the VBA code.  Another issue is if you change the configuration of the ComboBox and what columns it uses, you would need to find and retouch every DLookup statement.

I created a Public Function ComboBoxDisplayText(ctrl As control) As String, which allows you just pass the name of the control, and get the DisplayText that the user would see with the function taking care of working out the details of how to do that based on the ComboBox settings.  Instead of using the DLookup method, you can use ComboBoxDisplayText(Me.salesRegionCmb).  The function works very well for most ComboBox configurations, but may need to be tweaked if the SQL for the ComboBox is some unusually complex query.  I also have not tested how this works with list ComboBoxes that do not draw from a table, but in the meantime, this is very useful for the most common usage of ComboBoxes.

Public Function ComboBoxDisplayText(ctrl As control) As String
' This function returns the string display value of a ComboBox control based on
' its current value and its ComboBox properties (RowSource, BoundColumn, ColumnWidths)
'   USAGE: ComboBoxDisplayText(Me.Controls("MyControl"))
'
' It returns "" if not a ComboBox or if there is no value
' .ColumnWidths is used to find which fields to including in the output string, but no attempt
' is made to adjust actual widths. Non-zero values determine if the field is to be diplayed.
' If more than one field is to be displayed, they are separated in order by " "
'
'  This code relies on some other helper functions that are included and can also be used separately:
'       ComboBoxSourceSelectString(ctrl As control) As String
'           Returns the SELECT portion of the SQL in the control's RowSource
'       ComboBoxSourceTableName(ctrl As control) As String
'           Returns the FROM portion of the SQL string (including any JOIN info)in the control's RowSource
'       ComboBoxSourceWhereClause(ctrl As control) As String
'           Returns the WHERE clause of the SQL string (or "" if not present)in the control's RowSource
'       ComboBoxBoundFieldName(ctrl As control) As String
'           Returns the Field Name in the RowSource that the control' value is bound to
'
'  NOTE the parsing of SQL in the RowSource is relatively simplistic, and could require some
'  tweaking for more complex SQL queries.
'
' This code is free open source, has NO Warranty, and can be used by anyone without restriction.
' Created By: Brian A. Crawford, [email protected]
'
    Dim strSQL As String
    Dim rs As Recordset
    Dim i As Integer
    Dim strDisplayText As String
    Dim colWidths() As String

    ' Initialize return value
    ComboBoxDisplayText = ""

    ' Check if control is a ComboBox and its value is not null
    If TypeOf ctrl Is ComboBox And Not IsNull(ctrl.value) Then
        ' Check if the bound column and row source types are valid
        If ctrl.boundColumn >= 1 And ctrl.ColumnCount >= ctrl.boundColumn And ctrl.RowSourceType = "Table/Query" And ctrl.rowSource <> "" Then
            ' Construct the SQL query
            strSQL = "SELECT " & ComboBoxSourceSelectString(ctrl) & " FROM " & ComboBoxSourceTableName(ctrl)
            If ComboBoxSourceWhereClause(ctrl) = "" Then
                strSQL = strSQL & " WHERE " & ComboBoxBoundFieldName(ctrl) & " = " & ctrl.value
            Else
                strSQL = strSQL & " WHERE " & ComboBoxSourceWhereClause(ctrl) & " AND " & ComboBoxBoundFieldName(ctrl) & " = " & ctrl.value
            End If

            ' Open a recordset using the SQL query
            Set rs = CurrentDb.OpenRecordset(strSQL)

            ' Check if recordset is not empty
            If Not (rs.EOF And rs.BOF) Then
                rs.MoveFirst
                ' Split the column widths string into an array
                colWidths = Split(ctrl.columnWidths, ";")
                ' Concatenate non-hidden column values
                For i = 0 To UBound(colWidths)
                    If colWidths(i) <> "0" Then
                        If strDisplayText <> "" Then strDisplayText = strDisplayText & " "
                        strDisplayText = strDisplayText & rs.Fields(i).value
                    End If
                Next i
                ' Assign return value
                ComboBoxDisplayText = strDisplayText
            End If

            ' Close the recordset
            rs.Close
            Set rs = Nothing
        End If
    End If
End Function


Public Function ComboBoxSourceWhereClause(ctrl As control) As String
' This function returns the WHERE clause of the SQL string (or "" if not present)in the control's RowSource
'   USAGE: ComboBoxSourceWhereClause(Me.Controls("MyControl"))
'
' It returns "" if not a ComboBox or if there is no value
'
'  NOTE the parsing of SQL in the RowSource is relatively simplistic, and could require some
'  tweaking for more complex SQL queries.
'
' This code is free open source, has NO Warranty, and can be used by anyone without restriction.
' Created By: Brian A. Crawford, [email protected]
'

    Dim strWherePart As String
    Dim strRemainingPart As String

    ' Initialize return value
    ComboBoxSourceWhereClause = ""

    ' Check if control is a ComboBox and RowSource is not empty
    If TypeOf ctrl Is ComboBox And ctrl.RowSourceType = "Table/Query" And ctrl.rowSource <> "" Then
        ' Check if RowSource is a SQL SELECT statement
        If InStr(1, ctrl.rowSource, "SELECT ", vbTextCompare) > 0 And InStr(1, ctrl.rowSource, "WHERE ", vbTextCompare) > 0 Then
            ' Get the part of the SQL statement after WHERE
            strRemainingPart = Trim(Mid(ctrl.rowSource, InStr(1, ctrl.rowSource, "WHERE ", vbTextCompare) + Len("WHERE ")))
            ' Check if there is an ORDER BY clause
            If InStr(1, strRemainingPart, "ORDER BY ", vbTextCompare) > 0 Then
                strWherePart = Trim(Left(strRemainingPart, InStr(1, strRemainingPart, "ORDER BY ", vbTextCompare) - 1))
            Else
                strWherePart = strRemainingPart
            End If
            ' Assign return value
            ComboBoxSourceWhereClause = strWherePart
        End If
    End If
End Function

Public Function ComboBoxSourceTableName(ctrl As control) As String
' This function returns the FROM portion of the SQL string (including any JOIN info)in the control's RowSource
'   USAGE: ComboBoxSourceTableName(Me.Controls("MyControl"))
'
' It returns "" if not a ComboBox or if there is no value
'
'  NOTE the parsing of SQL in the RowSource is relatively simplistic, and could require some
'  tweaking for more complex SQL queries.
'
' This code is free open source, has NO Warranty, and can be used by anyone without restriction.
' Created By: Brian A. Crawford, [email protected]
'

    Dim strFromPart As String
    Dim strRemainingPart As String

    ' Initialize return value
    ComboBoxSourceTableName = ""

    ' Check if control is a ComboBox and RowSource is not empty
    If TypeOf ctrl Is ComboBox And ctrl.RowSourceType = "Table/Query" And ctrl.rowSource <> "" Then
        ' Check if RowSource is a SQL SELECT statement
        If InStr(1, ctrl.rowSource, "SELECT ", vbTextCompare) > 0 And InStr(1, ctrl.rowSource, "FROM ", vbTextCompare) > 0 Then
            ' Get the part of the SQL statement after FROM
            strRemainingPart = Trim(Mid(ctrl.rowSource, InStr(1, ctrl.rowSource, "FROM ", vbTextCompare) + Len("FROM ")))
            ' Check if there is an ORDER BY clause
            If InStr(1, strRemainingPart, "ORDER BY ", vbTextCompare) > 0 Then
                strFromPart = Trim(Left(strRemainingPart, InStr(1, strRemainingPart, "ORDER BY ", vbTextCompare) - 1))
            ' Check if there is a WHERE clause
            ElseIf InStr(1, strRemainingPart, "WHERE ", vbTextCompare) > 0 Then
                strFromPart = Trim(Left(strRemainingPart, InStr(1, strRemainingPart, "WHERE ", vbTextCompare) - 1))
            Else
                strFromPart = strRemainingPart
            End If
            ' Assign return value
            ComboBoxSourceTableName = strFromPart
        Else
            ' If RowSource is not a SQL SELECT statement, return it as is
            ComboBoxSourceTableName = ctrl.rowSource
        End If
    End If
End Function

Public Function ComboBoxBoundFieldName(ctrl As control) As String
' This function returns the Field Name in the RowSource that the ComboBox's value is bound to
'   USAGE: ComboBoxBoundFieldName(Me.Controls("MyControl"))
'
' It returns "" if not a ComboBox or if there is no value
'
' For simplicity, this string strips away any field alias ("AS " references)
'
'  NOTE the parsing of SQL in the RowSource is relatively simplistic, and could require some
'  tweaking for more complex SQL queries.
'
' This code is free open source, has NO Warranty, and can be used by anyone without restriction.
' Created By: Brian A. Crawford, [email protected]
'
    Dim arrFields() As String
    Dim strSelectPart As String
    Dim intBoundColumn As Integer
    Dim strFieldName As String

    ' Initialize return value
    ComboBoxBoundFieldName = ""

    ' Check if control is a ComboBox and RowSource is not empty
    If TypeOf ctrl Is ComboBox And ctrl.RowSourceType = "Table/Query" And ctrl.rowSource <> "" Then
        ' Check if RowSource is a SQL SELECT statement
        If InStr(1, ctrl.rowSource, "SELECT ", vbTextCompare) > 0 And InStr(1, ctrl.rowSource, "FROM ", vbTextCompare) > 0 Then
            ' Get the part of the SQL statement between SELECT and FROM
            strSelectPart = Trim(Mid(ctrl.rowSource, Len("SELECT ") + 1, InStr(1, ctrl.rowSource, "FROM ", vbTextCompare) - Len("SELECT ") - 1))
            ' Split the SELECT part into fields
            arrFields = Split(strSelectPart, ",")
            ' Get the bound column index (assume it's a valid integer)
            intBoundColumn = CInt(ctrl.boundColumn)
            ' Check if the bound column index is valid
            If intBoundColumn > 0 And intBoundColumn <= UBound(arrFields) + 1 Then
                ' Get the bound field name (remove AS alias if exists)
                strFieldName = arrFields(intBoundColumn - 1)
                If InStr(1, strFieldName, " AS ", vbTextCompare) > 0 Then
                    ' Check if "AS " is within square brackets
                    If Not (InStr(1, strFieldName, "[") < InStr(1, strFieldName, " AS ", vbTextCompare) And InStr(1, strFieldName, " AS ", vbTextCompare) < InStr(1, strFieldName, "]")) Then
                        strFieldName = Trim(Left(strFieldName, InStr(1, strFieldName, " AS ", vbTextCompare) - 1))
                    End If
                End If
                ' Assign return value
                ComboBoxBoundFieldName = strFieldName
            End If
        Else
            ' If RowSource is not a SQL SELECT statement, return it as is
            ComboBoxBoundFieldName = ctrl.rowSource
        End If
    End If
End Function

Public Function ComboBoxSourceSelectString(ctrl As control) As String
' This function returns the FROM portion of the SQL string (including any JOIN info)in the control's RowSource
'   USAGE: ComboBoxSourceSelectString(Me.Controls("MyControl"))
'
' It returns "" if not a ComboBox or if there is no value
' For simplicity, this string strips away any field alias ("AS " references)
'  NOTE the parsing of SQL in the RowSource is relatively simplistic, and could require some
'  tweaking for more complex SQL queries.
'
' This code is free open source, has NO Warranty, and can be used by anyone without restriction.
' Created By: Brian A. Crawford, [email protected]
'
    Dim strSelectPart As String
    Dim strFields() As String
    Dim strField As String
    Dim i As Integer

    ' Initialize return value
    ComboBoxSourceSelectString = ""

    ' Check if control is a ComboBox and RowSource is not empty
    If TypeOf ctrl Is ComboBox And ctrl.RowSourceType = "Table/Query" And ctrl.rowSource <> "" Then
        ' Check if RowSource is a SQL SELECT statement
        If InStr(1, ctrl.rowSource, "SELECT ", vbTextCompare) > 0 And InStr(1, ctrl.rowSource, "FROM ", vbTextCompare) > 0 Then
            ' Get the part of the SQL statement between SELECT and FROM
            strSelectPart = Trim(Mid(ctrl.rowSource, Len("SELECT ") + 1, InStr(1, ctrl.rowSource, "FROM ", vbTextCompare) - Len("SELECT ") - 1))
            ' Split the part into individual field names
            strFields = Split(strSelectPart, ",")
            ' Loop through each field name
            For i = LBound(strFields) To UBound(strFields)
                strField = Trim(strFields(i))
                ' Check if the field name contains AS
                If InStr(1, strField, " AS ", vbTextCompare) > 0 And Left(strField, 1) = "[" And Right(strField, 1) = "]" Then
                    ' Remove the AS part
                    strFields(i) = Trim(Left(strField, InStr(1, strField, " AS ", vbTextCompare) - 1))
                End If
            Next i
            ' Assign return value
            ComboBoxSourceSelectString = Join(strFields, ", ")
        Else
            ' If RowSource is not a SQL SELECT statement, return it as is
            ComboBoxSourceSelectString = ctrl.rowSource
        End If
    End If
End Function