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

smtazulislam

Member
Local time
Today, 07:45
Joined
Mar 27, 2020
Messages
806
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
Code:
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,
Code:
        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

Super Moderator
Staff member
Local time
Today, 05:45
Joined
Feb 19, 2013
Messages
16,731
have you tried setting the columnheads property of the combo to true?
 

smtazulislam

Member
Local time
Today, 07:45
Joined
Mar 27, 2020
Messages
806
It appears that the layout consists of multiple column headers and some space between the table records.
see the attached.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 00:45
Joined
May 21, 2018
Messages
8,685
I think I did the same thing here

For the headers I did this so you cannot select them
Code:
Private Sub cmboParts_BeforeUpdate(Cancel As Integer)
  If Me.cmboParts = -1 Then
   Cancel = True
   Me.cmboParts.Undo
  End If
End Su
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 00:45
Joined
May 21, 2018
Messages
8,685
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

Member
Local time
Today, 07:45
Joined
Mar 27, 2020
Messages
806
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
Code:
cmbo.AddItem rs!TableName & ";" & "-----" & rs!FieldName
Perfect worked.
Code:
 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

Member
Local time
Today, 07:45
Joined
Mar 27, 2020
Messages
806
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

You've got your good things, and you've got mine.
Local time
Today, 00:45
Joined
May 21, 2018
Messages
8,685
One thing is you cannot have leading spaces. All values are trimmed.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:45
Joined
Feb 19, 2013
Messages
16,731
Perhaps show a picture to demonstrate what you mean by ‘doesn’t look quite right’ and what ‘right’ looks like
 

smtazulislam

Member
Local time
Today, 07:45
Joined
Mar 27, 2020
Messages
806
Perhaps show a picture to demonstrate what you mean by ‘doesn’t look quite right’ and what ‘right’ looks like
Here is it..
 

Attachments

  • Screenshot 2024-02-10 222057.png
    Screenshot 2024-02-10 222057.png
    45.3 KB · Views: 48

CJ_London

Super Moderator
Staff member
Local time
Today, 05:45
Joined
Feb 19, 2013
Messages
16,731
you can't increase the row height in a combo. You could try the following to offset the field record

Code:
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
1707956503492.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:45
Joined
May 7, 2009
Messages
19,249
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.
 

Attachments

  • combo_gap.accdb
    724 KB · Views: 56

smtazulislam

Member
Local time
Today, 07:45
Joined
Mar 27, 2020
Messages
806
you can't increase the row height in a combo. You could try the following to offset the field record

Code:
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
 

Attachments

  • Screenshot 2024-02-15 084824.png
    Screenshot 2024-02-15 084824.png
    8 KB · Views: 43
  • Combo box.accdb
    512 KB · Views: 57
Last edited:

Users who are viewing this thread

Top Bottom