combo box, selection, sql

[forums1167]

I am creating a combo box with a button. In the below vba code, I am getting the string from the combo box. Then, I am trying to use SQL to the corresponding distro lists in the table (same row). This VBA code below is getting a Run-time error '438' Object doesn't support this property or method and it points to the SQL statment in the debugging screen. Any help is greatly appreciated.

[FONT=Times New Roman][FONT=Times New Roman][SIZE=3][FONT=Times New Roman]Private Sub Command11_Click()[/FONT][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][FONT=Times New Roman]Dim cnn As ADODB.Connection[/FONT][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][FONT=Times New Roman] Dim rs As ADODB.Recordset[/FONT][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][FONT=Times New Roman] Dim SQL As String[/FONT][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][FONT=Times New Roman] Dim vFld As Variant[/FONT][/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3][FONT=Times New Roman]string1 = Combo27.Value[/FONT][/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3][FONT=Times New Roman]SQL = "SELECT [" & DistributionLists & "]  as Fld" & _[/FONT][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][FONT=Times New Roman]" FROM " & DistroLists & " WHERE " & string1 & "=" & Application[/FONT][/SIZE][/FONT]
 
 
[FONT=Times New Roman][SIZE=3][FONT=Times New Roman] rs.Open SQL[/FONT][/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3][FONT=Times New Roman] vFld = rs!Fld[/FONT][/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3][FONT=Times New Roman] Set cnn = Nothing[/FONT][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][FONT=Times New Roman] Set rs = Nothing[/FONT][/SIZE][/FONT]
 
 
 
[FONT=Times New Roman][SIZE=3][FONT=Times New Roman]MsgBox (vFld)[/FONT][/SIZE][/FONT]
 
 
[FONT=Times New Roman][SIZE=3][FONT=Times New Roman]End Sub[/FONT][/SIZE][/FONT]
[/FONT]

 

[pbaldy]

I don't use ADO a lot, but you haven't set or used cnn. You can use this to make sure the SQL string is coming out right:

http://www.baldyweb.com/ImmediateWindow.htm