none
Runtime Error 3061 Too Few parameters. Expected 1.

    Question

  • good Morning. I have a bit of code that is not functioning as I expect it.  If I copy the SQL statement into a query it returns the values expected. However, the "set rs" line always highlights and I receive the error above. I can change the SQL statement and delete the "where" and it works fine as well, it just returns too many values. Any help is appreciated. 

    Private Sub Form_Load()

           Dim cControl As Control
         
        For Each cControl In Me.Controls
            If cControl.Name Like "Text*" Then cControl = vbNullString
        Next
       'sets up database for available attenedees
       Dim db As DAO.Database
       Dim rs As DAO.Recordset
       Dim strSQL As String, strItem As String, dlook As String
       Dim qdf As QueryDef
       
     
       strSQL = "SELECT qryActiveEmployees.LngEmpID FROM qryCurrentEventAttendees RIGHT JOIN qryActiveEmployees ON qryCurrentEventAttendees.[strlngEmpID] = qryActiveEmployees.[LngEmpID] WHERE (((qryCurrentEventAttendees.strlngEmpID) Is Null))"

       
         Set db = CurrentDb()
         Set rs = db.OpenRecordset(strSQL)
       
       Do Until rs.EOF
          
                
               strItem = rs.Fields(LngEmpID).Value
        
        'looks up the ID and returns a name
        dlook = DLookup("strFullName", "tblEmployees", "[strlngEmpID]=" & strItem)

    Monday, September 10, 2012 1:33 PM

Answers

  • Since you appear to be selecting from stored queries -- qryActiveEmployees and qryCurrentEventAttendees -- I'd guess that one or both of those queries is using a form-control reference somewhere, possibly as a criterion.  Such references are treated as parameters, and while Access will evaluate such references when a query is run through the user interface, the DAO OpenRecordset method doesn't do that; you have to supply the parameter values your self.

    You could do it by assigning the SQL string to a temporary querydef, evaluating the parameters, and then opening a recordset from the querydef.  Like this:

        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim qdf As DAO.QueryDef
        Dim prm As DAO.Parameter
        Dim strSQL As String, strItem As String, dlook As String
      
        strSQL = "SELECT qryActiveEmployees.LngEmpID FROM qryCurrentEventAttendees RIGHT JOIN qryActiveEmployees ON qryCurrentEventAttendees.[strlngEmpID] = qryActiveEmployees.[LngEmpID] WHERE (((qryCurrentEventAttendees.strlngEmpID) Is Null))"
     
       
        Set db = CurrentDb()
        Set qdf = db.CreateQueryDef("", strSQL)
        
        For Each prm In qdf.Parameters
            prm.Value = Eval(prm.Name)
        Next prm
    
        Set rs = qdf.OpenRecordset()


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, September 10, 2012 4:33 PM
  • I used your code and it accepts it, but now it is throwing out a 3265 "Item not found in this collection" error where, "strItem=rs.fields(lngEmpID).value" is set. I apologize, this is really new to me. My last venture into coding was a Qbasic class in college

    No need to apologize; you'll get up to speed in no time.

    In the assignment to strItem, I think you mean to be pulling the value of the recordset field named "LngEmpID".  In that case, where you are using a string as an index into the recordset's Fields collection, you need to refer to it like this:

        strItem = rs.Fields("LngEmpID").Value

    Alternatively, you can write this:

        strItem = rs!LngEmpID

    (which doesn't need the quotes).

    I hope there is more code that you haven't posted, because at the moment it isn't clear what you are trying to do, or why you are looping through a recordset.  It may be a good idea for you to explain what the goal of this code is.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by WGruetz Monday, September 10, 2012 8:30 PM
    • Unmarked as answer by WGruetz Monday, September 10, 2012 8:31 PM
    • Marked as answer by WGruetz Monday, September 10, 2012 8:31 PM
    Monday, September 10, 2012 5:54 PM

All replies

  •    
     
       strSQL = "SELECT qryActiveEmployees.LngEmpID FROM qryCurrentEventAttendees RIGHT JOIN qryActiveEmployees ON qryCurrentEventAttendees.[strlngEmpID] = qryActiveEmployees.[LngEmpID] WHERE (((qryCurrentEventAttendees.strlngEmpID) Is Null))"
     

    Hi WGruetz,

    Instead of test on

        WHERE qryCurrentEventAttendees.strLngEmpID IS NULL

    try

        WHERE qryActiveEmployees.LngEmpID IS NULL

    Imb.

    Monday, September 10, 2012 2:01 PM
  • Thanks, but it returns the same error, and it would also return a different set of records than what I am seeking.   I had originally set this up as a query, and used code to simply import the queried values:  

    strSQL="select lngEmpID from qryCurrentEventAttendeesNot"

    The query on it's own works great, but when I use the VBA to retrieve the data/run the query to populate a list it gets buggy.

    Monday, September 10, 2012 2:55 PM
  • It might be due to you joining the tables with a text on one side and a long on the other. What about this:

    ON CLng(qryCurrentEventAttendees.[strlngEmpID]) = qryActiveEmployees.[LngEmpID]


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, September 10, 2012 3:02 PM
  • Thanks, but it returns the same error, and it would also return a different set of records than what I am seeking.   I had originally set this up as a query, and used code to simply import the queried values:  

    Hi WGruetz,

    In first instance I thought that the condition for the RIGHT JOIN was wrong, therefore the change to the other ID. But after a closer look it is alright, as what you already had with the resulting recordset.

    Go for Bill's advice.

    Imb.

    Monday, September 10, 2012 3:41 PM
  • I tried this and it responds with the same error. The field LNGEmpID is an autonumber indicating a unique employee and strlngEmpID is also a number. I can get the SQL to run in a query by itself. I'm not sure if that means anything. There is also a lot of other code on the page, but nothing that I could think of that would cause an error, especially since this on on a form load.
    Monday, September 10, 2012 3:50 PM
  • Since you appear to be selecting from stored queries -- qryActiveEmployees and qryCurrentEventAttendees -- I'd guess that one or both of those queries is using a form-control reference somewhere, possibly as a criterion.  Such references are treated as parameters, and while Access will evaluate such references when a query is run through the user interface, the DAO OpenRecordset method doesn't do that; you have to supply the parameter values your self.

    You could do it by assigning the SQL string to a temporary querydef, evaluating the parameters, and then opening a recordset from the querydef.  Like this:

        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim qdf As DAO.QueryDef
        Dim prm As DAO.Parameter
        Dim strSQL As String, strItem As String, dlook As String
      
        strSQL = "SELECT qryActiveEmployees.LngEmpID FROM qryCurrentEventAttendees RIGHT JOIN qryActiveEmployees ON qryCurrentEventAttendees.[strlngEmpID] = qryActiveEmployees.[LngEmpID] WHERE (((qryCurrentEventAttendees.strlngEmpID) Is Null))"
     
       
        Set db = CurrentDb()
        Set qdf = db.CreateQueryDef("", strSQL)
        
        For Each prm In qdf.Parameters
            prm.Value = Eval(prm.Name)
        Next prm
    
        Set rs = qdf.OpenRecordset()


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, September 10, 2012 4:33 PM
  • I think that you are on to something. The queries are looking for a "tempvars" that is form controlled. I used your code and it accepts it, but now it is throwing out a 3265 "Item not found in this collection" error where, "strItem=rs.fields(lngEmpID).value" is set. I apologize, this is really new to me. My last venture into coding was a Qbasic class in college

      For Each prm In qdf.Parameters
            prm.Value = Eval(prm.Name)
        Next prm
    
        Set rs = qdf.OpenRecordset()
        
       
       Do Until rs.EOF
          
                
               strItem = rs.Fields(LngEmpID).Value         
        
        'looks up the ID and returns a name
        dlook = DLookup("strFullName", "tblEmployees", "[strlngEmpID]=" & strItem)

    Monday, September 10, 2012 5:36 PM
  • I used your code and it accepts it, but now it is throwing out a 3265 "Item not found in this collection" error where, "strItem=rs.fields(lngEmpID).value" is set. I apologize, this is really new to me. My last venture into coding was a Qbasic class in college

    No need to apologize; you'll get up to speed in no time.

    In the assignment to strItem, I think you mean to be pulling the value of the recordset field named "LngEmpID".  In that case, where you are using a string as an index into the recordset's Fields collection, you need to refer to it like this:

        strItem = rs.Fields("LngEmpID").Value

    Alternatively, you can write this:

        strItem = rs!LngEmpID

    (which doesn't need the quotes).

    I hope there is more code that you haven't posted, because at the moment it isn't clear what you are trying to do, or why you are looping through a recordset.  It may be a good idea for you to explain what the goal of this code is.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by WGruetz Monday, September 10, 2012 8:30 PM
    • Unmarked as answer by WGruetz Monday, September 10, 2012 8:31 PM
    • Marked as answer by WGruetz Monday, September 10, 2012 8:31 PM
    Monday, September 10, 2012 5:54 PM
  •       
                
               strItem = rs.Fields(LngEmpID).Value         
        

    Hi WGRuetz,

    In this way you search for a field with a fieldname that is equal to the value of the variable LngEmpID, which is not found.

    So you can better use:  strItem = rs(Fields("LngEmpID").Value,

    or in short:  strItem = rs("LngEmpID").

    Then you search for a field with the name "LngEmpID".

    Imb.

    Monday, September 10, 2012 6:01 PM
  • So you can better use:  strItem = rs(Fields("LngEmpID").Value


    You've a typo there, Imb.  Should be rs *dot* Fields: rs.Fields("LngEmpID").Value

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, September 10, 2012 6:07 PM
  • So you can better use:  strItem = rs(Fields("LngEmpID").Value


    You've a typo there, Imb.  Should be rs *dot* Fields: rs.Fields("LngEmpID").Value

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thank you, Dirk,

    You are right! rs.Fields(Index) needs the dot, whereas rs(Index) not.

    Sometimes editing free text is much harder then programming. In the latter case the compiler is helping you.

    Imb.

    Monday, September 10, 2012 7:05 PM
  • Sometimes editing free text is much harder then programming. In the latter case the compiler is helping you.


    I know what you mean.  When typing up any but the simplest code examples, I usually find it easier to start up Access and get into the VB editor, type my code, then copy & paste it into the forum window.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, September 10, 2012 7:32 PM
  • I know what you mean.  When typing up any but the simplest code examples, I usually find it easier to start up Access and get into the VB editor, type my code, then copy & paste it into the forum window.

    Hi Dirk,

    That is what I also do in many cases. Though not many enough apparently.

    But wasn't this "any but the simplest code examples"?

    Nevertheless, thank you for your excellent systematic corrections.

    Imb.

    Monday, September 10, 2012 7:58 PM
  • But wasn't this "any but the simplest code examples"?

    Indeed it was, and exactly the sort of thing that catches me, too.  Please don't think I was being critical -- just conversational.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, September 10, 2012 8:18 PM