How to add Query results to Combobox, Error with Combobox Items - Access VBA

Question

I currently try to add query values to my ComboBox in Access VBA. But it seems like they are always in a wrong format / wrongly added to the Combobox.

    Dim query As String
    Dim projectnr As String
    Dim projectint As Integer
    Dim rs As DAO.Recordset
    Dim counter As Long
    
    counter = 0
    'Get all Projectsnumbers
    query = "SELECT [tblExProjekte].[exwID], [tblExProjekte].[exwProjektnummer] FROM tblExProjekte WHERE Not IsNull([tblExProjekte].[exwProjektnummer]) ORDER BY [exwProjektnummer] DESC;" 'Query get Projectnumbers
    Set rs = CurrentDb.OpenRecordset(query)
    rs.MoveFirst
    If Not (rs.EOF And rs.BOF) Then 'Check if Entry Exists from SELECT
        Do Until rs.EOF = True 'Loop all Projects
            projectnr = rs("exwProjektnummer")
            If Not IsEmpty(projectnr) And StartsWith(projectnr, "640") Then 'Check if Project
                projectcombobox.AddItem rs("exwProjektnummer") 'Add Item to ComboBox
            End If
            rs.MoveNext
            counter = counter + 1
        Loop
    End If

I get results like this, in the Combobox:

"64067923;'';64067721;'';64067405;'';64067255;'';64067234;'';64066905;'';64066875;'';64066861;'';64066860;'';64066676;'';64066674;'';64066673;'';64066636;'';64066426;'';64066373;'';64066220;'';64066209;'';64066190;'';64066169;'';64066127;'';64066090;'';"

on Access directly it seems that there are a lot of empty values in the Box enter image description here

since im new into access vba, i would be glad for any help

I currently try to add query values to my ComboBox in Access VBA. But it seems like they are always in a wrong format / wrongly added to the Combobox.

Answer 1

Hy Tommy,

In the above case, it's easier to create a query directly in your combobox. It's more maintainable and no code is needed. You can do this by going to the combobox and select Rowsource. In dutch at my side.

The Rowsource type should be : Table/Query

When you select the rowsource option; a query designer will open. Here you can then select your table and select the rows you want to show / use.

Lets say you want to show the projectnummer and use the id field. You can go to the format field of the combobox and configure it as shown bellow :

Columns is set to 2 Column width is set to 0;3 cm This means the first will be 0 cm ( not visible ) and the second 3 cm ( visible )

Result :

Answer 2

rs("exwProjektnummer") returns a recordset field, which can do funky things when adding this object directly to a collection.

Use

projectcombobox.AddItem rs("exwProjektnummer").Value

or since you have it in a string variable anyway,

projectcombobox.AddItem projectnr

But if your code isn't greatly simplified and your query is constant as shown, Mathias is correct:
just use the query as row source.