Add field item data create left-side gap (1 Viewer)

[smtazulislam]

Hi, There have any ways to display multiple table Name (Table1, Table2, Table3) within there Field (Field1, Field2, Field3) in the combo box list,
But Table name is readonly/Header, its can't select to insert.
Table1
Field1
Table2
Field2
Table3
Field3

Private Sub Combo0_AfterUpdate()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strItem As String
    Dim currentTable As String
    Dim isFirstTable As Boolean
    
    ' Define your SQL query to get field names from multiple tables
    strSQL = "SELECT 'Table1' AS TableName, Field1 AS FieldName FROM Table1 " & _
             "UNION SELECT 'Table1', Field1 FROM Table1 " & _
             "UNION SELECT 'Table2', Field2 FROM Table2 " & _
             "UNION SELECT 'Table3', Field3 FROM Table3 " & _
             "ORDER BY TableName" ' Ensure the records are sorted by table name
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
   
    ' Loop through the recordset and add items to the combo box
    Do While Not rs.EOF
        If currentTable <> rs("TableName") Then
            ' Add a separator to distinguish different tables
            If Not isFirstTable Then
                Me.Combo0.AddItem "-----------------------"
            Else
                isFirstTable = False
            End If
            
            ' Store the current table name
            currentTable = rs("TableName")
            
            ' Add the table name as a non-selectable header
            Me.Combo0.AddItem rs("TableName")
        End If
        'Here neeed some code adding,
        ' Add field name as an item with left-side gap
        Me.Combo0.AddItem "    "  & rs("FieldName")
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

This line need to do something,

        Me.Combo0.AddItem "    "  & rs("FieldName")

Setting the Form,
LimitToList: Set this property to Yes.
ColumnCount: Set this property to 1
BoundColumn: Set this property to 0.

 

[CJ_London]

have you tried setting the columnheads property of the combo to true?

 

[smtazulislam]

It appears that the layout consists of multiple column headers and some space between the table records.
see the attached.

 

[Pat Hartman]

Nothing attached

 

[MajP]

I think I did the same thing here

Dropdown Combo box with Sub Headings

Hi All we have a combo box list showing numerous different products, to make it a little easier is there a way to put sub heading (ENGINE & BOSYWORK) within it that cannot be clicked on and maybe even Bold? There isn't enough on the list to warrant a second dropdown list to choose the type but...

www.access-programmers.co.uk

For the headers I did this so you cannot select them

Private Sub cmboParts_BeforeUpdate(Cancel As Integer)
  If Me.cmboParts = -1 Then
   Cancel = True
   Me.cmboParts.Undo
  End If
End Su

 

[MajP]

I added a blank row to the top in this example. Without it you cannot select the top row but it still shows as if it was selected. This gives a better effect.

 

[smtazulislam]

The code is functioning well, but I'm still facing an issue with creating adequate spacing. How can I generate a gap or free space without resorting to methods like underscore or underline?
I need to do something here

cmbo.AddItem rs!TableName & ";" & "-----" & rs!FieldName

Perfect worked.

 Dim cmbo As Access.ComboBox
    Dim rs As DAO.Recordset
    Dim tempHeader As String
    
    Set cmbo = Me.Combo0
    cmbo.RowSourceType = "Value List"
    cmbo.ColumnCount = 2
    cmbo.ColumnWidths = "0in;2in"
    
    ' SQL query to get field names from multiple tables
    strSQL = "SELECT 'Table1' AS TableName, Field1 AS FieldName FROM Table1 " & _
             "UNION SELECT 'Table1', Field1 FROM Table1 " & _
             "UNION SELECT 'Table2', Field2 FROM Table2 " & _
             "UNION SELECT 'Table3', Field3 FROM Table3 " & _
             "ORDER BY TableName"
    
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    
    Do While Not rs.EOF
        If rs!TableName <> tempHeader Then
            ' Add TableName as a non-selectable item (header) in bold
            cmbo.AddItem "-1;" & rs!TableName
            tempHeader = rs!TableName
        End If
        ' Add FieldName with left-space gap
        cmbo.AddItem rs!TableName & ";" & "-----" & rs!FieldName
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

 

[smtazulislam]

I added a blank row to the top in this example. Without it you cannot select the top row but it still shows as if it was selected. This gives a better effect.

I've managed to create some space in the top row, but it doesn't look quite right. It's not as I expected.

 

[MajP]

One thing is you cannot have leading spaces. All values are trimmed.

 

[CJ_London]

Perhaps show a picture to demonstrate what you mean by ‘doesn’t look quite right’ and what ‘right’ looks like

 

[smtazulislam]

Perhaps show a picture to demonstrate what you mean by ‘doesn’t look quite right’ and what ‘right’ looks like

Here is it..

 

[CJ_London]

you can't increase the row height in a combo. You could try the following to offset the field record

strSQL = "SELECT 'Table1' AS TableName 1 as SortOrder FROM Table1 " & _
             "UNION SELECT '     ' & Field1, 2 FROM Table1 " & _
             "UNION SELECT 'Table2', 3 FROM Table2 " & _
             "UNION SELECT '     ' & Field2, 4 FROM Table2 " & _
              "UNION SELECT 'Table3', 5 FROM Table3 " & _
             "UNION SELECT '     ' & Field3, 6 FROM Table3 " & _
             "ORDER BY SortOrder"

you'll get something like this

 

[arnelgp]

can you use some "hacks" and use commandbars.
this will only work if you have small number of combo items on the list.
see form1 demo.
note the the combo is resized it's with to only show the down-arrow button.
beside the combo is a regular textbox.
and On Top of the combobox-down arrow button is a command button (Transparent, Position->bring to Front).
the combo's Tab Stop property is Set to No.

 

[smtazulislam]

you can't increase the row height in a combo. You could try the following to offset the field record

strSQL = "SELECT 'Table1' AS TableName 1 as SortOrder FROM Table1 " & _
             "UNION SELECT '     ' & Field1, 2 FROM Table1 " & _
             "UNION SELECT 'Table2', 3 FROM Table2 " & _
             "UNION SELECT '     ' & Field2, 4 FROM Table2 " & _
              "UNION SELECT 'Table3', 5 FROM Table3 " & _
             "UNION SELECT '     ' & Field3, 6 FROM Table3 " & _
             "ORDER BY SortOrder"

you'll get something like this
View attachment 112538

Its not work with me.
Edit : update the ScreenShot & Db