ms-access

How to have the result of a query in a textbox?

I have a form which has 2 combo fields and one text box. I want the text box to show the result of a query. The query takes values from both combo boxes.

I was able to form the query but not to get its value in the text box. The query produces a single value (1 row, 1 column).

Solution

  • Text boxes don't have a recordsource like a combobox, you can set them to equal the result of a function though, something like this:

    Public Function CountChildData()

Dim rs As Recordset

    Set rs = CurrentDb.OpenRecordset("SELECT Count(childid) AS Result FROM ChildData WHERE formid = " & Me.cmbFormid & "")

    CountChildData = rs!Result

    rs.close
    set rs = nothing

End Function

    Then you can set your textbox's Control Source property to =CountChildData()