Performing an SQL query in VBA

  • Thread starter Thread starter jthorsvik
  • Start date Start date
J

jthorsvik

I need to perform a SQL query in VBA and display the result in a
textbox. First let me explain my situation. I have a form with two
text boxes, one is called txtLName and the other txtNum. I also have a
table called People. I want to run the following query in the
txtLName_Change() event and display the result in txtNum.

SELECT COUNT(LName) AS Num FROM People WHERE LName LIKE
'Me![txtLName]*';

Eventually the value returned will go to an if statement, but for now
just getting it to a textbox is great. Thank you, Jon
 
Problem fixed!

Dim rs As Recordset
Dim SQL As String
SQL = "SELECT COUNT(LName) AS Num FROM People WHERE LName LIKE '" &
Me!txtLName.text & "*';"
Set rs = CurrentDb.OpenRecordset(SQL)
txtNum.Value = rs!Num

Thank you anyway, Jon
 
You don't really need to establish a recordset object for this; you can use
the DCount function. In the Change event procedure (I assume you want the
result to be progressive as characters are entered, otherwise the
AfterUpdate event procedure would be more appropriate) of the txtLName
control assign the return value to the txtNum control:

Me.txtNum = DCount("*", "People", "LName Like """ & Me.txtLName & "*""")

Contrary to what is sometimes said, benchmarking has shown that using domain
aggregation functions is more efficient than establishing a recordset when a
single value is to be obtained. Where values from more than one row in a
recordset are required, however, establishing a recordset would be more
efficient than multiple domain function calls.

Ken Sheridan
Stafford, England
 
Back
Top