Results 1 to 10 of 10
  1. #1
    Voodeux2014 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Nov 2014
    Location
    Slidell, LA
    Posts
    130

    Too Few Parameters on .OpenRecordset()



    Code:
    Private Sub cmdUnapproveProvider_Click()Dim mydb As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL
    
    
    strSQL = Forms![frmMain].[lstTrainingProvideClassInfo].RowSource
    Set mydb = CurrentDb
    Set rst = mydb.OpenRecordset(strSQL)
    
    
    If rst.RecordCount = 0 Then
        MsgBox ("There are no records to act on")
        GoTo StopAllThisNonsense
    End If
    
    
    rst.MoveFirst
    While rst.EOF = False
        strSeatsUsed = DLookup("CountOfStudentID", "qrySeatsUsedByClassID", "ClassID=" & Me.lstTrainingProvideClassInfo)
        
        DoCmd.OpenForm "frmChangeSeatAvailability"
            [Forms]![frmChangeSeatAvailability]![cboClassSelection] = Me.lstTrainingProvideClassInfo
            [Forms]![frmChangeSeatAvailability]![txtCurApprovedSeats] = strSeatsUsed
        
        DoCmd.OpenQuery "qryUnapproveClass"
        
        DoCmd.Close acForm, "frmChangeSeatAvailability"
        rst.MoveNext
    Wend
    
    
    DoCmd.OpenQuery "qryUnapproveTrainingProvider"
    
    
    StopAllThisNonsense:
    rst.Close
    Set rst = Nothing
     
    End Sub
    I am getting the "Too Few Parameters. Expected 2" error on:

    Code:
    Set rst = mydb.OpenRecordset(strSQL)
    This is cross posted at another location. I will update a solution if found there before here.

    Also, if you happen to notice anything else that will be a problem, please bring it to my attention.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I would declare strSQL as a string rather than a variant

    Can you show us the value that gets assigned to strSQL when the code runs
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Voodeux2014 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Nov 2014
    Location
    Slidell, LA
    Posts
    130
    I was trying to do some other things while waiting for a response. strSQL is normally DIM as String.

    Here is the row source that it is being set to within the code (spaced out to make it easier to read):

    Code:
    SELECT tbl1Classes.ClassID, tbl1Classes.ClassName AS [Class Name]FROM tbl1Classes 
    INNER JOIN tbl3ApprovedGrantClasses 
    ON tbl1Classes.ClassID = tbl3ApprovedGrantClasses.ClassID
    WHERE (((tbl3ApprovedGrantClasses.TrainerID)=[Forms]![frmMain]![lstAuthorizedTrainers]) 
    AND ((tbl3ApprovedGrantClasses.GrantID)=[Forms]![frmMain]![cboGrant]) 
    AND ((tbl3ApprovedGrantClasses.Unapproved)=No));

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    To run a sql statement with parameters from vba, either concatenate the form control references or create a query def and pass the parameters to it. To concatenate,
    WHERE (((tbl3ApprovedGrantClasses.TrainerID)=[Forms]![frmMain]![lstAuthorizedTrainers]) becomes

    "WHERE (((tbl3ApprovedGrantClasses.TrainerID)='" & [Forms]![frmMain]![lstAuthorizedTrainers] & "')" The single quotes in red are nested on the assumption that the form control is passing a string, so you would omit them for a number (or use # for dates).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Voodeux2014 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Nov 2014
    Location
    Slidell, LA
    Posts
    130
    Thanks for the help. That did correct the error, but now I watch as it stepped through the code and it did not change the ClassID that it was working with. It did step through the code loop 3 times, as it should since there were three records returned. But it kept the same ClassID for each walk through.

    Here is the current code now:
    Code:
    Private Sub cmdUnapproveProvider_Click()Dim mydb As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    
    
    'strSQL = Forms![frmMain].[lstTrainingProvideClassInfo].RowSource
    strSQL = "SELECT tbl1Classes.ClassID, tbl1Classes.ClassName AS [Class Name] " & _
                "FROM tbl1Classes " & _
                "INNER JOIN tbl3ApprovedGrantClasses " & _
                "ON tbl1Classes.ClassID = tbl3ApprovedGrantClasses.ClassID " & _
                "WHERE (((tbl3ApprovedGrantClasses.TrainerID)= " & [Forms]![frmMain]![lstAuthorizedTrainers] & ") " & _
                "AND ((tbl3ApprovedGrantClasses.GrantID)= " & [Forms]![frmMain]![cboGrant] & ") " & _
                "AND ((tbl3ApprovedGrantClasses.Unapproved)=No));"
    
    
    Set mydb = CurrentDb
    Set rst = mydb.OpenRecordset(strSQL)
    
    
    If rst.RecordCount = 0 Then
        MsgBox ("There are no records to act on")
        GoTo StopAllThisNonsense
    End If
    
    
    rst.MoveFirst
    While rst.EOF = False
        strSeatsUsed = DLookup("CountOfStudentID", "qrySeatsUsedByClassID", "ClassID=" & Me.lstTrainingProvideClassInfo)
        
        DoCmd.OpenForm "frmChangeSeatAvailability"
            [Forms]![frmChangeSeatAvailability]![cboClassSelection] = Me.lstTrainingProvideClassInfo
            [Forms]![frmChangeSeatAvailability]![txtCurApprovedSeats] = strSeatsUsed
        
        DoCmd.OpenQuery "qryUnapproveClass"
        
        DoCmd.Close acForm, "frmChangeSeatAvailability"
        rst.MoveNext
    Wend
    
    
    DoCmd.OpenQuery "qryUnapproveTrainingProvider"
    
    
    StopAllThisNonsense:
    rst.Close
    Set rst = Nothing
     
    End Sub

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    it kept the same ClassID for each walk through.
    I don't see anything in the code that would alter the value being looked up by "ClassID=" & Me.lstTrainingProvideClassInfo. It seems to be on the form from which this event is triggered (Me), but it remains constant throughout the loop iterations.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Voodeux2014 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Nov 2014
    Location
    Slidell, LA
    Posts
    130
    Ok, but, how do I get it to where it will not remain constant?

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure how to answer that based on what you've revealed.
    The lookup function can only retrieve one value. If you make the criteria for that value the value of a form control, you will get the first (if there is more than one) record it finds that meets the criteria. So what I envision happening here is, upon clicking the button, you create a recordset (say 3 records), you open another form, set two controls to certain values, execution comes back to this sub and opens a query. I'm guessing that query gets parameters from the second form. As soon as the query runs, you close the form you just opened and repeat this 2 more times without pause, after which you open qryUnapproveTrainingProvider. So the first query runs 3x, but you probably only see the results of the last opening since this all likely happens in a short time.

    You have ClassID in the recordset, so if it differs from record to record and you want to use those variations, maybe you should be getting that value from the recordset instead of a Lookup on another query based on a form control value that does not change. Maybe it should be "ClassID=" & rst.Fields(0)) or rstFields("ClassID").
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Voodeux2014 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Nov 2014
    Location
    Slidell, LA
    Posts
    130
    I had to change it up a little bit to get the rst.Fields(0) to work properly. But it is now working. Thanks a bunch!

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're welcome.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 09-04-2014, 10:53 PM
  2. dbSeeChanges does not fix openrecordset error
    By lringstad in forum Access
    Replies: 1
    Last Post: 12-03-2013, 03:33 PM
  3. db.OpenRecordset error
    By jscriptor09 in forum Programming
    Replies: 4
    Last Post: 01-27-2012, 12:17 AM
  4. Replies: 8
    Last Post: 12-21-2011, 12:50 PM
  5. OpenRecordset vs. Query
    By crispy-bacon in forum Programming
    Replies: 7
    Last Post: 07-04-2011, 09:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums