Hi all,
What I need to do is populate a combo box at runtime according to specific inputs from other forms.The code listed below is a seperate trial and works OK. I would like to know if I am overdoing the code thing for what I am achieving.

Private Sub Form_Load()

    Set myconn = New ADODB.Connection
    myconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\Gareth Powell\Desktop\db2.mdb"
    myconn.Open
    Set mycomm = New ADODB.Command
    Set mycomm.ActiveConnection = myconn
    mycomm.CommandType = adCmdText
    mycomm.CommandText = "SELECT res FROM table1"
    Set myRS = mycomm.Execute
    
    Do Until myRS.EOF = True
    Combo1.AddItem myRS!res
    myRS.MoveNext
     Loop
     
Set myRS = Nothing
Set mycomm = Nothing
Set myconn = Nothing

End Sub

Secondly, in the SQL statement and AddItem statements where I have used column name 'res' , ideally I will want to use column names which are numbers eg 7609.
However, when I use a numerical column name it results in a

compile error expected:end of statement error

Any suggestions gratefully received, as always!!:-/
Cheers

Lets assume that you already have the recordset with the records inside it.

this is how i populate a combobox...

rs.Movefirst

   Do While Not rs.EOF

        cbo.AddItem (rs.Fields(<fieldname here))) 
        rs.MoveNext

   Loop

Secondly, in the SQL statement and AddItem statements where I have used column name 'res' , ideally I will want to use column names which are numbers eg 7609.

why do you need to do that?
what is the problem if the column name remains alphabetically?

Each column refers to a part number.

Thanks for the suggestion RobboHort but I didn't have any luck with the square brackets.

The way I am getting around the problem for now is to have a different table for each number with a field in each table named with a constant string ie FAULT
This way the variable is referred to to in the SQL statement but not in the AddItem

mycomm.CommandText = "SELECT fault FROM " & frmMain!txtMI.Text
ComMode.AddItem myRS!fault
This article has been dead for over six months. Start a new discussion instead.