Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211

    Too few parameters. Expected 1.

    I keep getting "Too few parameters. Expected 1." message each time a record is clicked and an error pops up and goes to VBA code line CurrentDb.OpenRecordset strSQL for debugging.



    Code:
    Private Sub btnFindRecord_Click()
    'On Error Resume Next
        Dim Permission As String
        Dim Staff As String
        Dim CaseGroup As String
        Dim ManagerGroup As String
        Dim DepartmentGroup As String
        Dim tClient As String
        Dim aCase As String
        Dim strSQL As String
        Permission = [Forms]![frmLogin]![txtPermissions]
        Staff = [Forms]![frmLogin]![txtStaffNumber]
        CaseGroup = [Forms]![frmLogin]![txtCaseGroup]
        ManagerGroup = [Forms]![frmLogin]![txtManagerGroup]
        DepartmentGroup = [Forms]![frmLogin]![txtDepartmentGroup]
        tClient = [Forms]![frmViewMain]![txtClientNum]
        aCase = "SELECT tblCase.[CaseNum] FROM tblCase WHERE tblCase.[ClientNum] Like '" & tClient & "' "
        strSQL = "SELECT ID, CaseNum, ClientNum, DateCreated, CaseStatus, AssignedCaseGroup, AssignedStaff " _
                & "FROM tblCase " _
                & "WHERE CaseNum LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR ClientNum LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR CreatedBy LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR DateCreated LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR StartDate LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR EndDate LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR Urgency LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR CaseStatus LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR Service LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR CaseSubmitted LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR DateSubmitted LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR SubmissionMethod LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR Outcome LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR ClientFileRefNum LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR HourlyRate LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR DailyRate LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR FlatRate LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR Budget LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR HoursEstimate LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR HoursActual LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR AssignedCaseGroup LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR AssignedStaff LIKE '*" & Me!txtRecordSearchBox & "*' " _
                & "OR Notes LIKE '*" & Me!txtRecordSearchBox & "*' "
        If Permission = "Admin" Then
            CurrentDb.OpenRecordset strSQL
        ElseIf Permission = "Manager" Then
            If Not IsNull(tClient) Then
                CurrentDb.OpenRecordset strSQL
                DoCmd.SetFilter , "[CaseNum] Like '" & aCase & "' AND [AssignedDepartmentGroup] Like '" & DepartmentGroup & "' AND [AssignedManagerGroup] Like '" & ManagerGroup & "' AND [AssignedCaseGroup] LIKE '" & CaseGroup & "' OR [AssignedStaff] LIKE '" & Staff & "'"
            Else
                Beep
                MsgBox ("Selected CLIENT not associated with any CASE."), vbOKOnly, "Warning"
            End If
        ElseIf Permission = "HR" Then
            If Not IsNull(tClient) Then
                CurrentDb.OpenRecordset strSQL
                DoCmd.SetFilter , "[CaseNum] Like '" & aCase & "' AND [AssignedDepartmentGroup] Like '" & DepartmentGroup & "' AND [AssignedManagerGroup] Like '" & ManagerGroup & "' AND [AssignedCaseGroup] LIKE '" & CaseGroup & "' OR [AssignedStaff] LIKE '" & Staff & "'"
            Else
                Beep
                MsgBox ("Selected CLIENT not associated with any CASE."), vbOKOnly, "Warning"
            End If
        ElseIf Permission = "User" Then
            If Not IsNull(tClient) Then
                CurrentDb.OpenRecordset strSQL
                DoCmd.SetFilter , "[CaseNum] Like '" & aCase & "' AND [AssignedDepartmentGroup] Like '" & DepartmentGroup & "' AND [AssignedManagerGroup] Like '" & ManagerGroup & "' AND [AssignedCaseGroup] LIKE '" & CaseGroup & "' OR [AssignedStaff] LIKE '" & Staff & "'"
            Else
                Beep
                MsgBox ("Staff member not associated with any CASE."), vbOKOnly, "Warning"
            End If
        ElseIf Nz(Permission, "") = "" Or Nz(Permission, "") = 0 Then
            Beep
            modEventLog.Tracker "Unauthorized Case Access Attempt - Permissions Null or 0"
            DoCmd.Quit
        ElseIf Permission <> "Admin" Or Permission <> "Manager" Or Permission <> "User" Or Permission <> "HR" Then
            Beep
            modEventLog.Tracker "Unauthorized Case Access Attempt - Permissions Not Recognized"
            DoCmd.Quit
        End If
        Me!
    [listSearch].RowSource = strSQL
        Me!
    [listSearch].Requery
    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Use this to see the finished SQL:

    http://www.baldyweb.com/ImmediateWindow.htm

    odds are something is spelled wrong.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    and an error pops up on VBA code line "CurrentDb.OpenRecordset strSQL"
    Is this a separate message? Your post could be interpreted that way, plus I don't see how this code makes a whole lot of sense. You don't SET a recordset object to any object variable, thus you can't refer to any properties or methods of that recordset. The SetFilter method applies to the active query, form, report or table, so I don't see the point in those lines either since there's no such (apparent) active object. Maybe I'm missing something around the rest of the code, but the parameters error has a few possible reasons for the prompt.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    The issue was a field name was wrong. I changed it and no more errors appear.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    CurrentDb.OpenRecordset strSQL is wrong syntax for opening a recordset object. As Micron noted, there are no recordset object variables declared and set. I did a test. The line will execute but since there is no recordset object to manipulate, the code is meaningless.

    DAO:

    Set rs = CurrentDb.OpenRecordset(strSQL)

    ADO:

    rs.Open strSQL, CurrentProject.Connection

    There is also support for establishing a connection object variable instead of repeatedly referencing CurrentDb or CurrentProject.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It will compile but should trigger run-time error if try to execute
    I will open a recordset without error if the sql and all else is OK, but you won't be able to do anything with it the way the code is written. While this code might provide the desired outcome (which appears to be to set the row source for a control) there is a lot of code in between that seems to be of no use except maybe to provide the impetus for generating a message. There's no recordset movement, count, loop, data retrieval - nothing. There's an invocation to set a filter on (apparently) nothing and that's the one that has me most mystified.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You read my post too quickly. I already edited to change those comments.

    I expect SetFilter command is acting on whatever object currently has focus.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The focus would have to be on the form at the highest level, or the button that's being clicked on that form because that's where this code resides. Either way, I don't get why invoking the method doesn't cause an error since the name of that "thing" isn't being passed as a parameter. That's how I interpret the syntax for the method on the M$ pages, which didn't appear to be an optional parameter. Regardless, it seems to be another portion of the code that serves no purpose here, especially if a filter is being assigned before the source has been established by setting the recordsource to the sql statement. In the end, I guess it doesn't matter since all this doesn't seem to be of any interest outside of you and me.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    SetFilter doesn't have an argument to identify a 'thing', it just acts on whatever has focus. For instance, code could change focus to another object then run the SetFilter. I've never used this.

    Unlike Filter and FilterOn which require an object to be identified.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    The strSQL evaluates a text box where user searches something after the btnFindRecord button is clicked. The strSQL and filter results appear in a List Search box for user to select the record that reflects search criteria. The record selected from List Search box appears on the form when a record is clicked by user.

    I will reevaluate the open recordset command and see if there is a more appropriate way of executing strSQL based on aforementioned parameters. As of right now the strSQL query works as intended.

    I am running MS Access 2013, is it better to run on DAO or ADO for compatibility moving forward?

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I use DAO most of the time. I think the point was that you never use the recordset you're opening.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Quote Originally Posted by pbaldy View Post
    I use DAO most of the time. I think the point was that you never use the recordset you're opening.
    Would the following work.

    Current:
    CurrentDb.OpenRecordset strSQL

    Changed to:
    DoCmd.RunSQL strSQL

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No, you can only "run" an action query: append, update or delete. What is it you expect that recordset to accomplish?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    After changing CurrentDbOpenRecordset strSQL to DoCmd.RunSQL strSQL it did not work so I changed it back and now I get another error with the following.

    Code:
        aCase = "SELECT CaseNum FROM tblCase WHERE ClientNum Like '" & tClient & "' "
    Code:
    DoCmd.SetFilter , "[CaseNum] Like '" & aCase & "' AND [AssignedDepartmentGroup] Like '" & DepartmentGroup & "' AND [AssignedManagerGroup] Like '" & ManagerGroup & "' OR [AssignedStaff] Like '" & Staff & "'"
    Click image for larger version. 

Name:	Capture.PNG 
Views:	11 
Size:	12.5 KB 
ID:	37225

  15. #15
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Quote Originally Posted by pbaldy View Post
    No, you can only "run" an action query: append, update or delete. What is it you expect that recordset to accomplish?
    The strSQL evaluates a text box (1) where user searches something after the btnFindRecord Button (2) is clicked. The strSQL filters results that appear in a List Search Box (3) for user to select the record that reflects search criteria. The record selected from List Search Box (3) appears on the form (4) when a record is clicked by user.

    Attachment 37228

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Too Few Parameters Expected 9
    By RunTime91 in forum Access
    Replies: 11
    Last Post: 05-31-2018, 07:10 PM
  2. Too Few Parameters. Expected 1
    By flamesofdoom in forum Programming
    Replies: 1
    Last Post: 02-15-2013, 02:23 PM
  3. Too few parameters. expected 1.
    By seth1685 in forum Programming
    Replies: 1
    Last Post: 01-11-2012, 08:08 AM
  4. Too few parameters expected 4
    By forestgump in forum Programming
    Replies: 1
    Last Post: 04-15-2011, 09:10 AM
  5. Too few parameters. Expected 2.
    By PPCMIS2009 in forum Access
    Replies: 0
    Last Post: 01-28-2009, 01:02 PM

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