SQL

[edp1959]

SQL - what's wrong here

I have a field (Count) on my form (NN_ON) that displays the number of records returned by the SQL below. The parameter for the SQL comes from a field on my form (NN_ON) called (srID).

I get a runtime error 3061 "To few parameters expected 1", if the WHERE clause is directed to the field (srID) as below.

Dim Database As DAO.Database
Dim rst As DAO.Recordset
Dim dbs As Database
Dim rstSQl As String
Dim MyF As Form

Set dbs = CurrentDB
Set MyF = [Form_NN_ON]

rstSQl = "SELECT tblStockClerk.[stockroomID], tblStockClerk.[stock clerk]" _
& "FROM tblStockClerk WHERE tblStockClerk.[stockroomID] = MyF.srID;"
Set rst = dbs.OpenRecordset(rstSQl)
rst.MoveFirst
Do While Not rst.EOF
rst.MoveNext
Loop
Forms![NN_ON]![Count] = rst.RecordCount

If I substitute an actual stockroom ID number in the WHERE clause i.e. WHERE tblStockClerk.[stockroomID] = 11410, it works fine and returns the correct record count.

What am I missing?

 

[dcx693]

Try the rstSQL like this:
rstSQl = rstSQl = "SELECT tblStockClerk.[stockroomID], tblStockClerk.[stock clerk]" _
& "FROM tblStockClerk WHERE tblStockClerk.[stockroomID] = " & MyF.srID & ";"Not too sure about the syntax exactly, but the key is to pull out the reference to the form field from the quotation marks.

I rewrote your code below (noticed that your method of going through each record to populate the recordset might be a bit inefficient):

Dim dbs As Database
Dim rst As DAO.Recordset
Dim strSQl As String

    Set dbs = CurrentDb
    
    strSQl = "SELECT tblStockClerk.[stockroomID], tblStockClerk.[stock clerk]" _
    & "FROM tblStockClerk WHERE tblStockClerk.[stockroomID] = " & Forms![NN_ON]![srID] & ";"
    
    Set rst = dbs.OpenRecordset(strSQl)
    
    rst.MoveLast
    
    Forms![NN_ON]![Count] = rst.RecordCount

rst.Close
Set rst=Nothing
Set dbs=Nothing

 

[edp1959]

Works great! Thanks for the help.

This forum is the best!