Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Unanswered: Results of SQL query displyed in a Text box

    On Form load I am trying to populate a text box with the results of a SQL query. I have got this far, so far, but am obviously missing the magical word.

    Can anyone help?

    Private Sub Form_Load()
    Dim SQL1 As String
    Dim strMsg As String

    strMsg = SQL1

    SQL1 = "SELECT Ac_Event.Event_date, Ac_Event.Descr "
    SQL1 = SQL1 + "FROM Ac_Event "
    SQL1 = SQL1 + "WHERE (((Ac_Event.Event_date)=Date()));"

    txtEvent.SetFocus
    txtEvent.Text = (strMsg)

    End Sub


    Many thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it wont happen!

    a query can return a 2GB dataset...
    ...access can't tell in advance so it will NOT let you put it in a textbox!

    browse help for DLOOKUP()

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Izyrider is correct in that you can't paste a query into a textbox. I'm guessing you're trying to do something like this (note this is ADO code)...

    Private Sub Form_Load()

    Dim SQL1 As String
    Dim strMsg As Variant

    Dim rs as adodb.recordset
    set rs = new adodb.recordset

    SQL1 = "SELECT Ac_Event.Event_date, Ac_Event.Descr "
    SQL1 = SQL1 + "FROM Ac_Event "
    SQL1 = SQL1 + "WHERE (((Ac_Event.Event_date)=Date()));"
    rs.Open SQL1, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    if rs.eof and rs.bof then
    strMsg = "No records"
    else
    rs.movefirst
    do while not rs.eof
    strMsg = strMsg & rs!Event_date & " - " & rs!Descr & vbCrLf
    (or if you are setting it up to populate the rowsource of a listbox)
    strMsg = strMsg & rs!Event_date & ";" & rs!Descr & ";"
    rs.movenext
    loop
    end if

    rs.close
    set rs = nothing

    txtEvent.SetFocus
    txtEvent.Text = strMsg
    (or instead of the above)
    me!txtEvent = strMsg
    (or if populating the rowsource of a listbox)
    me.MylistBoxName.Rowsource = strMsg

    End Sub

    AND make sure the ControlSource of txtEvent is blank (Note - you could also do this with a listbox (which is the usual way), setting the Row Source Type property to a Value list and populating the Rowsource with strmsg (ex: me.MyListboxName.Rowsource = strMsg instead of me!txtEvent = strMsg.)

    Lastly, why not make a listbox instead of a textbox and set the Row Source Type property to a Table/Query, putting the actual query in the rowsource? It might save you some hassle (and you wouldn't need any code.) Listboxes were designed to do exactly what you're trying to do.
    Last edited by pkstormy; 10-03-07 at 11:16.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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