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
 

[zmbd]

Ok,
1) (...) & string1 & "=" & Application (...) this is more than likely your issue in that Application is a token word the refers to, well, the program and you've not used it correctly.

2) once you get the SQL line fixed, you might insert at line11 debug.print sql if your code crashes press <ctrl+g>, C&P the resulting string to a post here

3) please read and follow everything within the following link. Remember that you may have to repeat the compile and fix error steps several times until you can compile without any errors: >> Before Posting (VBA or SQL) Code.

4) The articles on Form Filter and Cascasde will provide you with a lot of information and may very well help you solve the issue.
From the Bytes > Sitemap > Microsoft Access / VBA Insights
42.Cascaded Form Filtering
156.Cascading Combo/List Boxes
168.Example Filtering on a Form.