count the number of rows in sql query result using access vba

Question

I am trying to count the number of rows in sql query result using access 2007 vba. What I have is a text box named AGN when a user put value on it check for this value then it bring back MsgBox if the the value is already inserted. What I try to do is :

Dim rs As DAO.Recordset
 Dim db As Database
  Dim strSQL As String

  Set db = CurrentDb

 strSQL = "SELECT agencies.[agency no] FROM agencies WHERE agencies.[agency no]= " &Me.AGN.Text

  Set rs = db.OpenRecordset(strSQL)

If rs.Fields.Count > 1 Then


MsgBox "this value is already here "

  End If

   Set rs = Nothing

When I insert any value on the textbox I got run time error 3061 (too few parameters)

[agency no] column is number type and the task that i want it is to check if there is any rows returned by the query — Mohammed AL Jakry
– Mohammed AL Jakry, Commented Apr 21, 2014 at 7:53

HansUp · Accepted Answer · 2014-04-21 13:57:25Z

The "too few parameters" error message generally means there is something in your SQL statement which Access doesn't recognize as a field, table, function or SQL keyword. In this case, it could happen if [agency no] is text rather than numeric data type. If that is the case, enclose the value of AGN with quotes when you build the SQL statement. (Or you could use a parameter query to avoid the need to quote the text value.)

strSQL = "SELECT a.[agency no] FROM agencies AS a" & vbCrLf & _
    "WHERE a.[agency no]= '" & Me.AGN.Value & "'"
Debug.Print strSQL

In case of trouble, go to the Immediate window and copy the output from Debug.Print. Then you can create a new query in the Access query designer, switch to SQL View and paste in the statement text for testing.

Once your SELECT is working, you can check whether or not the recordset is empty. When it is empty both its BOF and EOF properties are true. So to detect when it is not empty, check for Not (BOF And EOF) ...

With rs
    If Not (.BOF And .EOF) Then
        MsgBox "this value is already here "
    End If
End With

However you don't actually need to open a recordset to determine whether a matching row exists. You can check the value returned by a DCount expression.

Dim lngRows As Long
lngRows = DCount("*", "agencies", "[agency no]='" & Me.AGN.Value & "'")
If lngRows > 0 Then
    MsgBox "this value is already here "
End If

Notes:

I used AGN.Value instead of AGN.Text because the .Text property is only accessible when the control has focus. But I don't know where you're using that checking code, so unsure which is the proper choice for you.
Notice the similarities between the SELECT query and the DCount options. It's often easy to translate between the two.

Collectives™ on Stack Overflow

count the number of rows in sql query result using access vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related