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.

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

[rpeare]

throw in a debug.print sql the SQL generation line and see what it's giving you, cut and paste that code to the SQL window of a query in Access and see what happens, you'll likely get a better (more usable) error message than in your vb code.

SQL = "SELECT [" & DistributionLists & "]  as Fld  FROM " & DistroLists & " WHERE " & string1 & "=" & Application
debug.print sql

I suspect it's something to do with your 'Application' unless that's a field on your form, application may be a reserved word and that's why you may be having trouble with it. Try changing your field name to fldApplication and your SQL statement to

SQL = "SELECT [" & DistributionLists & "]  as Fld  FROM " & DistroLists & " WHERE " & string1 & "=" & fldApplication