Solved Run-time error '3061'. Too few parameters. Expected 1

[oxicottin]

Hello, I have a function that's giving me the error 3061 and it worked fine until I needed to add a field to the query that's a date field and a criteria for that field to a date text box on the form that I'm running the function from.

Errors and Degug to:
Set rs = db.OpenRecordset("qry_FindNullRecords", dbOpenSnapshot)

Here is the SQL:

SELECT tbl_InventoryOverview.InventoryOverviewID, tbl_InventoryOverview.InventoryDate, tbl_ProductData.strProductLength, tbl_Products.Product, tbl_InventoryDetails.Quantity
FROM tbl_Products INNER JOIN (tbl_ProductData INNER JOIN ((tbl_Employees INNER JOIN tbl_InventoryOverview ON tbl_Employees.EmployeeID = tbl_InventoryOverview.EmployeeID) INNER JOIN tbl_InventoryDetails ON tbl_InventoryOverview.InventoryOverviewID = tbl_InventoryDetails.InventoryOverviewID) ON tbl_ProductData.ProductDataID = tbl_InventoryDetails.ProductDataID) ON tbl_Products.ProductID = tbl_ProductData.ProductID
WHERE (((tbl_InventoryOverview.InventoryDate)=[Forms]![frm_InventoryOverview]![txtInventoryDate]))
GROUP BY tbl_InventoryOverview.InventoryOverviewID, tbl_InventoryOverview.InventoryDate, tbl_ProductData.strProductLength, tbl_Products.Product, tbl_InventoryDetails.Quantity
HAVING (((tbl_InventoryDetails.Quantity) Is Null));

Here is the function:

Function fncDisplayNull(frm As Form) As Boolean
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim intX As Integer
    Dim strMissnData As String
    Dim strMissnProd As String
    Dim lastproduct As String
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("qry_FindNullRecords", dbOpenSnapshot)
'--------------------------------------------------------------------------------------------------
'Open the switchboard its a new record and there isnt data missing
    If frm.NewRecord Then
        If CurrentProject.AllForms("frm_Switchboard").IsLoaded Then
            Forms![frm_Switchboard].Visible = True
            Forms!frm_Switchboard!sfrm_Switchboard.Form.Requery 'Requerys switchboard subform
        End If
'--------------------------------------------------------------------------------------------------
'If you entered data in main form (frm_InventoryOverview) but didnt create records in subform then cancel and go back to form.
        Dim MissinProd As DAO.Recordset
        Set MissinProd = frm.Form.sfrm_InventoryDetails.Form.RecordsetClone()
        
    ElseIf frm.Form.sfrm_InventoryDetails.Form.Recordset.RecordCount = 0 Then
        MsgBox "You havent created records in your subform click Create Records button!", vbInformation + vbOKOnly, "Required Data!"
        
        fncDisplayNull = True
'--------------------------------------------------------------------------------------------------
'If there isnt any fields left blank then close the form and open switchboard
    ElseIf rs.RecordCount < 1 Then
        If CurrentProject.AllForms("frm_Switchboard").IsLoaded Then
            Forms![frm_Switchboard].Visible = True
            Forms!frm_Switchboard!sfrm_Switchboard.Form.Requery 'Requerys switchboard subform
        End If
'--------------------------------------------------------------------------------------------------
'Some data is missing, stop and display whats missing so it can be filled out, _
        Run through the query (qry_FindNullRecords) and find products/lengths/quanity that data wasnt entered
    Else
        With rs
            .MoveLast: .MoveFirst: intX = .RecordCount
            'strMissnData = .Fields("Product") & " " & .Fields("strProductLength") & vbNewLine
            
            Do Until .EOF
                
                 strMissnData = strMissnData & .Fields("Product") & " " & .Fields("strProductLength") & vbNewLine

                .MoveNext
            Loop
        End With

        If vbYes = MsgBox("There are " & intX & " records that are missing information for the following Products/Lengths." & vbCrLf & vbCrLf _
        & strMissnData & vbCrLf _
        & "You have to follow up on these products/lenghts!", _
        vbYesNo + vbExclamation, "Missing Information") Then
'--------------------------------------------------------------------------------------------------
'Stop closing of form you selected YES, so finish filling out blank fields you missed
        fncDisplayNull = True  'End Function
'--------------------------------------------------------------------------------------------------
    Else
'--------------------------------------------------------------------------------------------------
'You selected NO so close the form and open the switchboard
        If CurrentProject.AllForms("frm_Switchboard").IsLoaded Then
            Forms![frm_Switchboard].Visible = True
            Forms!frm_Switchboard!sfrm_Switchboard.Form.Requery 'Requerys switchboard subform
        End If

'--------------------------------------------------------------------------------------------------
    End If
End If
rs.Close
Set rs = Nothing
Set db = Nothing
End Function

 

[MarkK]

This error can be distracting with its reference to parameters. What is going on is that there is an identifier--probably a misspelled field name--in your SQL. The SQL parser, when it doesn't find that identifier in any of the referenced tables, assumes it is a parameter. Then when you execute the SQL, and the SQL expects a parameter which you have not supplied, it raises this error.

Check the spellings of your field names.

 

[Josef P.]

...
WHERE (((tbl_InventoryOverview.InventoryDate)=[Forms]![frm_InventoryOverview]![txtInventoryDate]))
...

The form reference does not work in DAO.
Either you redesign the query or you fill the parameter with the value from the form.

 

[oxicottin]

@MarkK I did check the names previously and they all were correct. If I have the form open and run the query in datasheet view then the query runs fine and show results. BUT if I open the form and close the form thats when it checks the Function/Query and thats when I get the error.

 

[theDBguy]

Have you seen this?

Code Snippets

 

[oxicottin]

Sorry Fellas I was off for a few days... I'll try your examples and suggestions today!

 

[oxicottin]

@theDBguy That worked perfect, Thanks!

Set rs = fDAOGenericRst("qry_FindNullRecords", dbOpenSnapshot)

 

[theDBguy]

@theDBguy That worked perfect, Thanks!

Set rs = fDAOGenericRst("qry_FindNullRecords", dbOpenSnapshot)

Glad to hear you got it sorted out. Good luck with your project.