Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2013
    Posts
    5

    Unanswered: combo box, selection, sql

    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.



    Code:
    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

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    - What are DistributionLists , DistroLists , string1?
    - Application is a reserved word that represents the Application object (i.e. Access itself). You can't concatenate an object to a string, except if its default property is a string, which is not the case for the Application object.
    - To retrieve a single value from a table, you should use the DLookUp() function instead of opening a Recordset, and an ADODB Recordset moreover.
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can we see the actual SQL not the VBA that creates the SQL

    Im suspicious of the variable application, what is it, what does it store how is it DIMensioned
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2013
    Posts
    5
    Hi Sinndho, I appreciate the reply.
    I have changed my code to the following:

    Code:
    
    Private Sub Command11_Click()
    
    Dim dl1 As Variant
    Dim string1 As Variant
      
    
    string1 = Combo29.Value
    
    
       
    dl1 = DLookup("[DistributionLists]", "DistroLists", "[Application1]=string1")
    
    MsgBox (dl1)
    
    
    End Sub
    I received the error:
    Run-time error '2471'
    The expression you entered as a query parameter produced this error: "The object doesn't contain the Automation object 'string1'"
    DistributionLists=field inside DistroLists table
    Application1=field inside DistroLists table
    DistroLists=table with fields ID, Application1, DistributionLists
    string1=a string, used to get the combobox item selected
    I have changed Application to Application1, thanks for noticing that.
    Last edited by forums1167; 06-21-13 at 07:49.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Try:
    Code:
    dl1 = DLookup("DistributionLists", "DistroLists", "Application1 = '" & string1 & "'")
    Have a nice day!

  6. #6
    Join Date
    Jun 2013
    Posts
    5
    Thanks that worked!
    Appreciate your help!

  7. #7
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •