title: Assign query result to textbox in MS Access
body: <p>I have two related questions.  First, how do I assign the value of a query result to a textbox?</p>
  
  <p>Here's the code I have, currently assigned to the click event of a button:</p>
  
  <pre><code>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 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 """ &amp; Me![Switch Name] &amp; """))"
  
  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
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>