0

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

4
Contributors
4
Replies
5
Views
8 Years
Discussion Span
Last Post by lostandfound
0

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
0

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?

0

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 topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.