Populate a combo box at runtime.

[lostandfound]

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

[cometburn]

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

[ROBBOHORT]

I have found that

Combo1.AddItem myRS![9706]

will work

Good Luck

[choudhuryshouvi]

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?

[lostandfound]

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