Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2007
    Posts
    108

    Unanswered: Assigning SQL result to string in VBA?

    How do I assign the result of SELECT query to a variable in VBA?

    I have following code:

    Private Sub Slika_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)

    Dim qrystr As String
    Dim trazenikamenac As String

    qrystr = "SELECT Kamenci.Kamenac"
    qrystr = qrystr & " FROM Kamenci "
    qrystr = qrystr & "WHERE (((Kamenci.Top)<" & Y & ") AND ((Kamenci.Bottom)>" & Y & ") "
    qrystr = qrystr & "AND ((Kamenci.Left)<" & X & ") AND ((Kamenci.Right)>" & X & "));"

    Text26.Value = qrystr

    End Sub


    If used as such Text26 control takes on a correctly put together SQL statement which, when copied into SQL view on new query, produces correct results!

    I would like to assign that result to variable <trazenikamenac>

    Text26.controlsource = qrystr

    doesn't do the trick although it says SQL statements can be used with controlsource property!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    can't!

    use DLookup() to get one value occasionally - it produces one field x one or zero rows so it readily sits in a textbox but it is relatively slow so should not be used inside SQL as a sort of "subquery" nor in a much-repeating loop.

    otherwise you can go from SQL to recordset to textbox (see my reply yesterday to your unbound question) or you can use your SQL as .rowsource for a list or combo.
    you can even cheat by using your SQL to feed a hidden combo and pushing the combo.value into the textbox ...but that is so ugly i am ashamed to mention it.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can!

    But you have to open the query in a recordset object, retrieve the values and THEN assign them to controls.
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2007
    Posts
    108
    What happens when you use DLookup and WHERE condition is satisified in more than one record thus returning more rows?

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    DLookup (DXxxxxx for that matter) returns one value

    it will arbitrarily decide which value to return if several records match your criteria. not always the same one, and also not random, just arbitrary.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Apr 2007
    Posts
    108
    Funny function, eh?

    I uncovered recordset object, but it isn't very intuitive and well documented so I'll stick with Dlookup with precise criteria for my limited needs!

    P.S. Why so many parenthesis in WHERE part of the statement when it works without them?

  7. #7
    Join Date
    Apr 2007
    Posts
    108
    Funny function, eh?

    I uncovered recordset object, but it isn't very intuitive and well documented so I'll stick with Dlookup with precise criteria for my limited needs!

    P.S. Why so many parenthesis in WHERE part of the statement when it works without them?

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    Dim strSQL As String
    Dim rs As Recordset
    Dim cn As Database
    
        strSQL = "SELECT Count(*) FROM myTable"
        
        Set cn = CurrentDb
        
        Set rs = cn.OpenRecordset(strSQL, dbOpenDynaset)
        
        If Not rs.EOF Then
            MsgBox "number of records = " & rs.Fields(0)
        Else
            MsgBox "no records returned (or error?)"
        End If
           
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    George
    Home | Blog

Posting Permissions

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