AddItem,Recordset and a varialble??

[lostandfound]

Hi again,
In my previous thread regarding Combo box at runtime I had not forseen the problems I would experience introducing a variable to the code.

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 " & Text1.Text & " FROM table1"
    Set myRS = mycomm.Execute
    
    Do Until myRS.EOF = True
    Combo1.AddItem myRS!Text1.Text
    
    myRS.MoveNext
     Loop
     
Set myRS = Nothing
Set mycomm = Nothing
Set myconn = Nothing

End Sub

Can someody tell me if and how I can introduce a variable in the line :-

Combo1.AddItem myRS!Text1.Text

which in the current format throws up a compile error.
Thanks again:confused:

[cometburn]

Check this out..

Dim rs As ADODB.Recordset
    Dim adoConn As ADODB.Connection
    dim Str as string

    Set adoConn = New ADODB.Connection
    adoConn.ConnectionString = sqlcon 'sqlCon is your connection string
    adoConn.Open
    
    Str = "SELECT " & Text1.Text & " FROM table1"
    Set rs = New ADODB.Recordset
    rs.ActiveConnection = adoConn
    rs.LockType = adLockOptimistic
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenDynamic
           
    rs.Open Str
    While Not rs.EOF
        [B]Combo1.AddItem myRS!<Your field>[/B] 'a Recordset followed by a field
    rs.MoveNext
    Wend

[choudhuryshouvi]

Can somebody tell me if and how I can introduce a variable

what does it mean? do you wish to declare a variable here, assign a value to it and then pass it as a parameter for adding to the combo list?

of course you can declare variables at any point within your code editor except inside a statement. as what have you supplied in the next line it has a common syntax error. try like this,

Dim sName as String

sName=trim(Text1.text)
Combo1.AddItem sName

check to see if this performs solution to your question or not...

regards
Shouvik

[lostandfound]

The AddItem statement is the problem.
I have tried :-

Combo1.AddItem.myRS1!Text1.Text

The database connected to in order to obtain myRS1 consists of multiple fields each containing different lists for the combo box.
Text1.text refers to the field in myRS1 required to populate the combo box.
So in other words the variable I refer to is Text1.text which requires no prior declaration.
VB doesn't appear to like the fact that instead of using the actual field name, I have tried to use the contents of textbox1.

[lostandfound]

As mentioned in the original thread :-
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