Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    333

    Unanswered: Insert Query Result into Text Box

    I have a query that is designed to return only one value. How can I input that value into a text box on a form? I currently use a hidden listbox thats row source is set to the query. After the row source is updated, I select the only value in the list box and place it in my text box. This works but there has to be an easier way.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you know the query is designed to return one record but access doesn't: you can't feed a SELECT to a text box because the query could in principle return 2 billion records.

    two possibilities:
    query -> recordset -> textbox
    or
    dlookup()

    dlookup() is designed to return zero or one field and access knows that: you can feed a textbox directly.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95

    Findfirst

    Another method is findfirst, retrieves the first hit in your table, smt like this:

    Public Function GetValue(Num As Long) As Long

    Dim dbcurr As Database
    Set dbcurr = CurrentDb

    Dim rsRecords As Recordset
    Set rsRecords = dbcurr.OpenRecordset("tblmain", dbOpenDynaset)

    rsRecords.FindFirst "Value = " & Num

    If Not rsRecords.NoMatch Then
    GetValue = rsRecords!Number.Value
    Else: GetValue = vbNullString
    End If
    Set rsRecords = Nothing

    End Function


    This code searches in table 'tblmain' for the first row where the statement 'Value = " &num ' is true. If a hit is found, the code retrieves the value (from that row) in column 'number' (getvalue = rsRecords!Number.Value) and returns to where from this code was called. If after this, you put a line like 'txtfield = GetValue(whateverID)' in the code, the txtfield will display that value.

    gl

    -- for all clarity: The above replaces your SQL-code, so it might not be what you want but it works anyway

  4. #4
    Join Date
    May 2004
    Posts
    159
    Quote Originally Posted by campster
    I have a query that is designed to return only one value. How can I input that value into a text box on a form? I currently use a hidden listbox thats row source is set to the query. After the row source is updated, I select the only value in the list box and place it in my text box. This works but there has to be an easier way.
    You are making it more complicated than it needs to be.
    Use the listbox.columns(x, x) property and set a string to that. Then you can use the result. as you found out you can't set anything to the listbox.value property directly because it doesn't have anything other than null untill a select is made from the listbox. When there is only one value to show then go to it directly with the column property - (0, 0) will reference the first and only value.

Posting Permissions

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