Results 1 to 8 of 8

Thread: Fill combobox in one shot only

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Mar 2005
    Location
    Italy-Napoli
    Posts
    2,147

    Fill combobox in one shot only

    Possible to fill a combobox with this array value?
    Code:
    Set RS = New ADODB.Recordset
        RS.CursorLocation = adUseClient
        SQL = "SELECT * FROM COMUNI UNION SELECT * FROM STATI"
        RS.Open Source:=SQL, _
                ActiveConnection:=CON, _
                CursorType:=adOpenForwardOnly, _
                LockType:=adLockReadOnly
        RS.Sort = ("PROVINCIA, DESCRIZIONE")
    
        RS.MoveFirst
        Erase strDBRows()
        strDBRows = RS.GetRows()
        RS.Close
        Set RS = Nothing
        
        Me.CCOMNASC.List = strDBRows<<<<

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    4,524

    Re: Fill combobox in one shot only

    No, you need to write code to fill the standard listbox and combobox

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,330

    Re: Fill combobox in one shot only

    Sounds like a job for the DataCombo.

    Code:
        Set Fruits = New ADODB.Recordset
        With Fruits
            .CursorLocation = adUseClient
            .Open "Fruits.txt", _
                  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='.';" _
                & "Mode=Read;Extended Properties='Text'", _
                  adOpenStatic, _
                  adLockReadOnly, _
                  adCmdTableDirect
        End With
        With DataCombo1
            Set .RowSource = Fruits
            .ListField = "Fruit"
            .BoundColumn = "ID"
        End With
    This example only uses one half of the data-binding capabilities of a DataCombo.

    Name:  sshot.png
Views: 102
Size:  4.1 KB

    It makes no attempt to deal with foreign locales, so you might have to tweak the contents of the text data source to deal with decimal-is-comma locales. You might also compensate with a schema.ini that specifies invariant-locale CSV punctuation.
    Attached Files Attached Files
    Last edited by dilettante; May 9th, 2021 at 06:20 PM.

  4. #4

    Thread Starter
    PowerPoster
    Join Date
    Mar 2005
    Location
    Italy-Napoli
    Posts
    2,147

    Re: Fill combobox in one shot only

    Quote Originally Posted by dilettante View Post
    Sounds like a job for the DataCombo.

    Code:
        Set Fruits = New ADODB.Recordset
        With Fruits
            .CursorLocation = adUseClient
            .Open "Fruits.txt", _
                  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='.';" _
                & "Mode=Read;Extended Properties='Text'", _
                  adOpenStatic, _
                  adLockReadOnly, _
                  adCmdTableDirect
        End With
        With DataCombo1
            Set .RowSource = Fruits
            .ListField = "Fruit"
            .BoundColumn = "ID"
        End With
    This example only uses one half of the data-binding capabilities of a DataCombo.

    Name:  sshot.png
Views: 102
Size:  4.1 KB

    It makes no attempt to deal with foreign locales, so you might have to tweak the contents of the text data source to deal with decimal-is-comma locales. You might also compensate with a schema.ini that specifies invariant-locale CSV punctuation.
    2 QUESTION...

    1) how to clear the datacombo before to fill?
    2) i need to fill the datacombo with 2 fields

    my test code:

    With Me.CCOMNASC1
    Set .RowSource = RS
    .ListField = RS.Fields(1).Name & "-" & RS.Fields(0).Name
    .BoundColumn = "DESCRIZIONE"
    End With
    Last edited by luca90; May 10th, 2021 at 05:17 AM.

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,330

    Re: Fill combobox in one shot only

    Clear? Perhaps you want to Refresh it? Not sure what you are trying to do.

    ListField is one field name. What you are trying makes no sense. If you want values from two fields combined, then concatenate in the SQL query.

    Read the details about DataCombo in the documentation. BoundColumn needs to be some field that can be used to derive a bookmark, usually a primary key in the lookup table.

  6. #6

    Thread Starter
    PowerPoster
    Join Date
    Mar 2005
    Location
    Italy-Napoli
    Posts
    2,147

    Re: Fill combobox in one shot only

    Quote Originally Posted by dilettante View Post
    Clear? Perhaps you want to Refresh it? Not sure what you are trying to do.

    ListField is one field name. What you are trying makes no sense. If you want values from two fields combined, then concatenate in the SQL query.

    Read the details about DataCombo in the documentation. BoundColumn needs to be some field that can be used to derive a bookmark, usually a primary key in the lookup table.
    My progess...

    Code:
    SQL = "SELECT DESCRIZIONE & ' - ' & CODICE & ' - (' & PROVINCIA & ')' AS RIGA FROM COMUNI"
    Set RS = New ADODB.Recordset
    RS.Open SQL, CON, adOpenForwardOnly, adLockReadOnly
    
    With DataCombo1
            Set .RowSource = RS
            .ListField = "RIGA"
            .BoundColumn = "RIGA"
            .Refresh
        End With
    ...the code work, without error, but dont fill the datacombo!

  7. #7
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,330

    Re: Fill combobox in one shot only

    schema.ini {in the same folder as the data file, only here to define the data as tab-delimited text}
    Code:
    [DeezeAndDoze.txt]
    Format=TabDelimited
    DeezeAndDoze.txt
    Code:
    Deeze	Doze
    Egg	Drop
    Chicken	Wing
    Purple	Underground
    Left	Wobblecrank
    Super	Aphid
    Form1.frm
    Code:
    Option Explicit
    
    Private Lookup As ADODB.Recordset
    
    Private Sub DataCombo1_Change()
        MsgBox DataCombo1.Text
    End Sub
    
    Private Sub Form_Load()
        Set Lookup = New ADODB.Recordset
        With Lookup
            .CursorLocation = adUseClient
            .Open "SELECT Deeze & ' ' & Doze AS DeezeDoze FROM [DeezeAndDoze.txt]", _
                  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='.';" _
                & "Mode=Read;Extended Properties='Text'", _
                  adOpenStatic, _
                  adLockReadOnly, _
                  adCmdText
        End With
        With DataCombo1
            Set .RowSource = Lookup
            .ListField = "DeezeDoze"
        End With
    End Sub
    
    Private Sub Form_Unload(Cancel As Integer)
        Lookup.Close
    End Sub
    Name:  sshot.png
Views: 49
Size:  2.0 KB


    Normally you'd be fetching the data from a lookup table in a database. The text file is here just to make a simple demo.

    The lookup table would normally have an ID field, to be assigned as DataCombo.ListField and used to link a main table record to the lookup table. But you don't seem to be using proper data binding anyway, and probably don't even have a normalized database. So this is a degenerate example that only uses the "back half" of DataCombo data binding, merely to populate the DataCombo and then try to treat it as a simple "dumb ComboBox."

  8. #8
    PowerPoster
    Join Date
    Feb 2006
    Posts
    22,330

    Re: Fill combobox in one shot only

    This demo also uses DataCombo1.Style = dbcDropdownList but if you want other styles then don't trigger on the Change event.

Posting Permissions

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



Click Here to Expand Forum to Full Width