INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see
3

getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see

getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see

(OP)
I'm using VBA to build a query based on responses to a number of fields on a form, two of which are listboxes. When the code executes, it appears to build the query correctly, but I get the subject error every time.

I've set a watch on the query, and when I copy the watch value and paste it directly into a new query, I get valid results.

Here is the watch value:
SELECT * FROM qryTSAcomments WHERE ([tsStatus] = 'I' OR [tsStatus] = 'A' OR [tsStatus] = 'M') AND ([Dep] = 40 OR [Dep] = 70 OR [Dep] = 90);

It tells me "Too few parameters, expected 2."

If I drop this into a new query window and run it as is, it gives me 342 results.


Cheryl dc Kern

RE: getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see

I've seen this error when a field name is incorrect.

RE: getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see

(OP)
I have, too, which is why I tried pasting it into a query and running it that way. Usually, if you do that, you get a more direct error response which tells you which field you typed wrong. But in this case, it runs just fine.

Cheryl dc Kern

RE: getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see

Try this way instead and see if you have a better luck:

SELECT * FROM qryTSAcomments
WHERE (tsStatus IN ('I', 'A', 'M'))
AND (Dep IN (40, 70, 90));

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see

(OP)
Andy:

This is actually the way I had it structure the query first, with the same result.

I'm having the listbox update a text field each time an item is selected or deselected to create the code which will be pulled by the VBA that builds the query. Here's one of the two:

CODE --> VBA

Private Sub lstDeps_Click()
Dim SelectedValues As String
Dim frm As Form
Dim varItem As Variant
Dim lstItems As Control
Set lstItems = Me!lstDeps
 
For Each varItem In lstItems.ItemsSelected
    If SelectedValues > "" Then
        SelectedValues = SelectedValues & " OR [Dep] = " & lstItems.ItemData(varItem)
    Else
       SelectedValues = "[Dep] = " & lstItems.ItemData(varItem)
    End If
Next varItem

Me!SelectedValues = SelectedValues
End Sub 
This was originally written to result in code to support Andy's suggestion, and was re-written when I couldn't get past this error.

Here's the code that pulls it all together when the user is through making selections and is ready to run the report:

CODE --> VBA

Private Sub cmdPull_Click()
Dim UserName As String
UserName = GetUserName()
    Dim strSql As String
    strSql = "SELECT * FROM qryTSAcomments"

    Dim WhereAnd As String
    WhereAnd = " WHERE "
    
    Dim strANDS As String
    strANDS = ""

    Select Case optJoSoOther
        Case Is = 1
            strANDS = strANDS & " WHERE [tsJob] = '" & Me.cbJob & "'"
            WhereAnd = " AND "
        Case Is = 2
            strANDS = strANDS & " WHERE [fsono] = '" & Me.cbSO & "'"
            WhereAnd = " AND "
    End Select
    
    Dim strStat As String
    Select Case optStat
        Case Is = 1
            strStat = ""
        Case Is = 2
        If IsNull(Me.txtSelectedStats) = True Then
                MsgBox ("You must either select 'Any Status' or at least one status out of the list.")
                Exit Sub
            Else
                strStat = "(" & Me.txtSelectedStats & ")"
            End If
    End Select
    
    If Len(strStat) > 0 Then
        If WhereAnd = " WHERE " Then
            strANDS = strANDS & " WHERE " & strStat
            WhereAnd = " AND "
        Else
            strANDS = strANDS & " AND " & strStat
        End If
    End If
    
    Dim strDept As String
    Select Case optDept
        Case Is = 1
            strDept = ""
        Case Is = 2
            If IsNull(Me.SelectedValues) = True Then
                MsgBox ("You must either select 'All Departments' or at least one department out of the list.")
                Exit Sub
            Else
                strDept = "(" & Me.SelectedValues & ")"
            End If
    End Select
    
    If Len(strDept) > 0 Then
        If WhereAnd = " WHERE " Then
            strANDS = strANDS & " WHERE " & strDept
            WhereAnd = " AND "
        Else
            strANDS = strANDS & " AND " & strDept
        End If
    End If
    
    Dim strKeys As String
    If Me.txtKeys > "" Then
        strKeys = "[Comment] LIKE '%" & Me.txtKeys & "%'"
    End If
        
    If Len(strKeys) > 0 Then
        If WhereAnd = " WHERE " Then
            strANDS = strANDS & " WHERE " & strKeys
        Else
            strANDS = strANDS & " AND " & strKeys
        End If
    End If
    
    If WhereAnd = " WHERE " Then
        strSql = strSql & ";"
    Else
        strSql = strSql & strANDS & ";"
    End If
    
    Dim dbs As Database
    Dim rs As Recordset
    Dim qdf As QueryDef
    
    Set dbs = CurrentDb()
    Set rs = dbs.OpenRecordset(strSql, dbOpenSnapshot)

    With dbs
        Set qdf = .CreateQueryDef(UserName, strSql)
        DoCmd.OpenQuery UserName
        .QueryDefs.Delete UserName
    End With
    dbs.Close
    qdf.Close
End Sub 

Am I missing a point where I should be forcing some sort of format? I just can't understand why the same query that errors when it hits the OpenQuery stage here runs fine if copied straight into a query window and run there.

Cheryl dc Kern

RE: getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see

Add a line of code that returns your full SQL and add DAO.

CODE --> vba

Dim dbs As DAO.Database
    Dim rs As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Debug.Print strSql 

Duane
Hook'D on Access
MS Access MVP

RE: getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see

(OP)
I made the changes recommended by dhookom and got the same results. With the settings in place on the form this time, the Immediate window showed:

SELECT * FROM qryTSAcomments WHERE ([tsStatus] = 'I' OR [tsStatus] = 'M' OR [tsStatus] = 'A');

Still "Too few parameters. Expected 2." in the error window, still retrieved results without editing when pasted into a new query window.

Cheryl dc Kern

RE: getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see

You have two references to controls on forms in qryTSAcomments.

Duane
Hook'D on Access
MS Access MVP

RE: getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see

(OP)
Yes, here's how the second one is set (pretty much the same as the first).

CODE --> VBA

Private Sub lstStats_Click()
Dim SelectedValues As String
Dim frm As Form
Dim varItem As Variant
Dim lstItems As Control
Set lstItems = Me!lstStats
 
For Each varItem In lstItems.ItemsSelected
    If SelectedValues > "" Then
        SelectedValues = SelectedValues & " OR [tsStatus] = '" & Left(lstItems.ItemData(varItem), 1) & "'"
    Else
       SelectedValues = "[tsStatus] = '" & Left(lstItems.ItemData(varItem), 1) & "'"
    End If
Next varItem

If Me.ckNoMans = False Then
    If Len(SelectedValues) > 0 Then
        SelectedValues = SelectedValues & " OR [tsStatus] = 'M'"
    Else
        SelectedValues = SelectedValues & "[tsStatus] = 'M'"
    End If
End If

Me!txtSelectedStats = SelectedValues
End Sub 

The CASE statements in the code above determine if that control needs to be used or not. So for instance, if the option to filter by Departments is set correctly, it would involve the selected values set by the departments listbox:

CODE --> VBA

Dim strDept As String
    Select Case optDept
        Case Is = 1
            strDept = ""
        Case Is = 2
            If IsNull(Me.SelectedValues) = True Then
                MsgBox ("You must either select 'All Departments' or at least one department out of the list.")
                Exit Sub
            Else
                strDept = "(" & Me.SelectedValues & ")"
            End If
    End Select
    
    If Len(strDept) > 0 Then
        If WhereAnd = " WHERE " Then
            strANDS = strANDS & " WHERE " & strDept
            WhereAnd = " AND "
        Else
            strANDS = strANDS & " AND " & strDept
        End If
    End If 

There's a similar section of code to deal with the option to filter based on status.

With both options set to be used, I run it again and get this Query:
SELECT * FROM qryTSAcomments WHERE ([tsStatus] = 'I' OR [tsStatus] = 'M' OR [tsStatus] = 'A') AND ([Dep] = 49 OR [Dep] = 54 OR [Dep] = 70);

Same results.

Cheryl dc Kern

RE: getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see

So what is the SQL view of qryTSAcomments? If that has more references to queries then dig deeper until you find a reference like:

FORMS!frmName!ControlName

Duane
Hook'D on Access
MS Access MVP

RE: getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see

Just a side note...
In your code you manipulate and build your strSQL and you have 10 places where you keep track of " WHERE " part of your Select.

Consider this approach:

CODE

Private Sub cmdPull_Click()
Dim UserName As String
UserName = GetUserName()
    Dim strSql As String
    strSql = "SELECT * FROM qryTSAcomments WHERE 1 = 1 "
... 

This way you don't have to worry about where to put WHERE any more smile

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see

(OP)
Andrzejek: Thanks for the suggestion, it helped me to clean up the code by several lines.

dhookom: The query this pulls from did, in fact, reference two date fields on the form. I'd put them in there to reduce the total number of records the built query had to evaluate. When I removed those references from the target query and built them into this one, it then ran fine, but very very slow. Is that slowness just the reality I have to live with, or is there a way to make this work more efficiently?

Here's the target query as it stood previously:

CODE -->

SELECT dbo_vwTSFullSheetNonPTOitemsWsos.tsJob, CInt([Dept]) AS Dep, CDate([effDate]) AS efDate, 
dbo_vwTSFullSheetNonPTOitemsWsos.tsOp, dbo_vwTSFullSheetNonPTOitemsWsos.tsWorkCenter, 
dbo_vwTSFullSheetNonPTOitemsWsos.tsStatus, dbo_vwTSFullSheetNonPTOitemsWsos.Hrs, 
dbo_vwTSFullSheetNonPTOitemsWsos.fsono, dbo_vwTSFullSheetNonPTOitemsWsos.Comment, 
dbo_vwTSFullSheetNonPTOitemsWsos.EmpNo, dbo_vwTSFullSheetNonPTOitemsWsos.LnameFirst
FROM dbo_vwTSFullSheetNonPTOitemsWsos
WHERE (((CDate([effDate])) Between CDate([Forms]![TS Application Jobs and Comments].[txtDateFrom].[value]) 
And CDate([Forms]![TS Application Jobs and Comments].[txtDateTo].[value]))); 

I basically removed the WHERE clause, moving it to the start of the built query, thus:

CODE --> VBA

Dim strSql As String
    strSql = "SELECT * FROM qryTSAcomments WHERE efDate BETWEEN #" & dtFrom & "# AND #" & dtTo & "#" 
and built on from there (without the efDate filter here, I would have gone with the 1 = 1 suggestion).

Cheryl dc Kern

RE: getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see

cdck,
It looks like all of the tables in qryTSAcomments are from SQL server. I would create a pass-through query to SQL server and dynamically change the SQL property:

CODE --> vba

' in the code of [Forms]![TS Application Jobs and Comments]
' assumes qryTSAcomments is your pass-through
Dim strSQL as String
strSQL = "SELECT tsJob, Convert(INT,Dept) Dep, Convert(DateTime,effDate) efDate, " & _
  "tsOp, tsWorkCenter, tsStatus, Hrs, fsono, Comment, EmpNo, LnameFirst " & _
  "FROM vwTSFullSheetNonPTOitemsWsos " & _
  "WHERE Convert(DateTime,effDate) Between '" & Me.[txtDateFrom] & "' And '" & Me.[txtDateTo] & "'"
Currentdb.QueryDefs("qryTSAcomments").SQL = strSQL 

You will be very impressed by the speed gain.

Duane
Hook'D on Access
MS Access MVP

RE: getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see

(OP)
dhookom:

This is a front-end which multiple users will be accessing, possibly at the same time. If I set the pass-through query and then pull from it in each user's instance of the database as they execute the process, will it cause conflicts if it happens to fire at the same time? Is it possible that one user's output will be corrupted by settings selected by another user?

Cheryl dc Kern

RE: getting "Run-time error '3061' Too few parameters response on a specific query - can anyone see

You shouldn't use my suggestion if multiple users are in the same front end. However, users should NEVER share the same front end.

Duane
Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close