Assign query result to textbox in MS Access

I have two related questions. First, how do I assign the value of a query result to a textbox?

Here's the code I have, currently assigned to the click event of a button:

Private Sub SeatCount_Click()
Dim db As Database
Dim qdf As QueryDef
Dim sql As String
Dim rs As Recordset

sql = "SELECT Count([Switch Port Matrix].[End Device Type]) AS [CountOfEnd开发者_StackOverflow Device Type] FROM [Switch Port Matrix] WHERE ((([Switch Port Matrix].[End Device Type])=""Seat"" Or ([Switch Port Matrix].[End Device Type])=""6AB"") AND (([Switch Port Matrix].Enabled)=1)) GROUP BY [Switch Port Matrix].[Switch Name] HAVING ((([Switch Port Matrix].[Switch Name]) Like """ & Me![Switch Name] & """))"

Set db = CurrentDb()
With db
    On Error Resume Next
    .QueryDefs.Delete "SeatCount"
    Set qdf = .CreateQueryDef("SeatCount", sql)
    DoCmd.OpenQuery "SeatCount"
    .QueryDefs.Delete "SeatCount"
End With
db.Close
qdf.Close

End Sub

This works but it opens the query and displays the result in a datasheet. What I'd like to do is to display the result in a text box on the form rather than having to click a button and look at a datasheet. First, how do I retrieve the result as a string rather than executing the query to open a datasheet? What I've found on the web seems to point toward using dlookup rather than executing the query as I have it written, but from what I've found, dlookup seems suited for running simple queries and I haven't found anything indicating you can add more complex requirements such as count and groupby.

Second, how do I execute the query when the form is opened? I assume that I want to trigger it to run on an event but there doesn't seem to be an appropriate event available under the properties of the textbox.

I'm a network engineer, not a programmer or a database analyst, but I've dabbled in enough things that all of this sort of work gets dumped in my lap. If anyone can help out a struggling engineer, I'd certainly appreciate it.

In this case it is probably best to use DCount:

=DCount("End Device Type","Switch Port Matrix","[End Device Type] IN ('Seat','6AB') AND [Enabled]=1 AND [Switch Name] = '" & [Switch Name] & "'")

You can directly set the Control Source of a textbox to this statement.

However, for future reference:

Private Sub SeatCount_Click()
Dim db As Database
Dim rs As DAO.Recordset ''Requires reference to Microsoft DAO x.x Library
Dim sSQL As String

sSQL = "SELECT Count(s.[End Device Type]) AS CountOfEndDeviceType " _
    & "FROM [Switch Port Matrix] s " _
    & "WHERE s.[End Device Type] IN ('Seat,'6AB') AND s.Enabled=1 " _
    & "s.[Switch Name] = '" & Me![Switch Name] & "'"

Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL)

If rs.recordCount>0 Then
   Me.txtTextbox=rs!CountOfEndDeviceType
Else
   Me.txtTextbox="N/A"
End If

Set rs=Nothing
Set db=Nothing    
End Sub

Make a new form with the record source as this query without the count ,let give all the results , then in the form footer make a textbox and set its text to "= Count([Switch Port Matrix].[End Device Type])"

put this form as a subform to the form where you want to display the result and you can reference to it from the main form , you can find a similar idea in this link http://office.microsoft.com/en-us/access-help/about-calculating-a-total-in-a-subform-and-displaying-it-on-a-form-HP005187909.aspx?CTT=1