Too few parameters. Expected 1

[carmenv323]

I have a Navigation form with a few tabs on it. The first tab is open to a blank form, when the second tab is selected the form in the first tab is blank and creates an empty row in my table.

To avoid this I added a datacheck to remove the blank rows but I'm getting an error...



Too few parameters. Expected 1 when I wrote this:

If IsNull(Me.txtProjectNumber) Then
     Set rs = db.OpenRecordset("SELECT RequestID FROM tbl_Req WHERE txtProjectNumber is Null
   While Not rs.EOF
rs.Delete
rs.MoveNext
Wend
Set rs =Nothing
End If
Exit Sub

Too few parameters. Expected 2 when I changed it to this:

If IsNull(Me.txtProjectNumber) Then
     Set rs = db.OpenRecordset("SELECT RequestID FROM tbl_Req WHERE Me.txtProjectNumber is Null
   While Not rs.EOF
rs.Delete
rs.MoveNext
Wend
Set rs =Nothing
End If
Exit Sub

Any ideas?

[Welshgasman]

Concatenate the control value.
Query has no clue about form controls unless you specify the full form name and control.

[carmenv323]

Not sure how you mean. I added

WHERE [Forms]![frmReq]![txtProjectNumber] is Null

but still Expecting 2 parameters

[Gicu]

You need to isolate the variable txtProjectNumber:

Set rs = db.OpenRecordset("SELECT * FROM tbl_Req WHERE " & Me.txtProjectNumber & " is Null;",dbOpenDynaset) 'if txtProjectNumber is numeric
Set rs = db.OpenRecordset("SELECT * FROM tbl_Req WHERE '" & Me.txtProjectNumber & "' is Null;",dbOpenDynaset) 'if txtProjectNumber is text

Cheers,

[carmenv323]

Thank you!

[Gicu]

You're welcome!

[carmenv323]

You're welcome!

This is a different sql in the same form

 tmpRS = CurrentDb.OpenRecordset("SELECT tbl_PPISD.FPNUMBER, tbl_PPISD.ISD FROM tbl_PPISD" & _
                                                            " WHERE tbl_PPISD.[FPNumber] = ' " & Me.txtProjectNumber & " ' ;", dbOpenDynaset)

What am I doing wrong?

[Welshgasman]

Leaving spaces with the single quote

[carmenv323]

Leaving spaces with the single quote

Thanks for responding, I was praying someone did! this is the entire code, but it's still expecting a parameter even after I removed the spaces

        Set db = CurrentDb
        Debug.Print
        tmpRS = db.OpenRecordset("SELECT * FROM tbl_PP_ISD_ WHERE tbl_PP_ISD_.[FPNumber] = '" & Me.txtProjectNumber & "' ;", dbOpenDynaset)
            If tmpRS.RecordCount > 0 Then
                Me.txtPPISD = rs.Fields(0)
            Else
                Me.txtPPISD = 0
            End If
        Set tmpRS = Nothing

[Welshgasman]

So put the select into string variable debug.print it, the copy that to a query sql window and run it and see what happens.

[carmenv323]

So put the select into string variable debug.print it, the copy that to a query sql window and run it and see what happens.

In the immediate window I get the correct sql that I need

Select * from tbl_PPISD Where tbl_PPISD.[FPNumber] = '99115'

Which is what I want

[Micron]

So as mentioned, you copy the immediate window output into a new query in sql view. Then switch to datasheet view (I'd advise to not run action queries, use datasheet view instead). If it doesn't balk and shows a datasheet, the sql is good. Don't worry if the datasheet is blank. Usually, fields with "number" in the name are numbers, not text, so there's that. If it's a text field, keep the single quotes. If not, ditch them. There are ways to fix this error message if the aforementioned approaches don't work.

EDIT - might help to show more of the code before what you have. You can often resolve the problem by assigning the value of a form control to a variable and use the variable instead of the control reference in the sql. Otherwise, you might have to declare Parameter(s).

[carmenv323]

So as mentioned, you copy the immediate window output into a new query in sql view. Then switch to datasheet view (I'd advise to not run action queries, use datasheet view instead). If it doesn't balk and shows a datasheet, the sql is good. Don't worry if the datasheet is blank. Usually, fields with "number" in the name are numbers, not text, so there's that. If it's a text field, keep the single quotes. If not, ditch them. There are ways to fix this error message if the aforementioned approaches don't work.

EDIT - might help to show more of the code before what you have. You can often resolve the problem by assigning the value of a form control to a variable and use the variable instead of the control reference in the sql. Otherwise, you might have to declare Parameter(s).

I tried the following to no avail:

strSQL= "select * from tbl_PPISD where tbl_PPISD.[FPNumber] = " & Me.txtProjectNumber & "'""
Set rs = db.OpenRecordset(strSQLc)

and even though the debugging print returns the correct result, I'm still missing a parameter.

[Micron]

Try
Dim strProject As String, strSql As String

strProject = Me.txtProjectNumber
strSql = "select * from tbl_PPISD where tbl_PPISD.[FPNumber] = '" & strProject & "'"

That's one of the potential solutions I referred to. Make sure you have not mis-spelled a field or control name, otherwise you'll get the error message forever.
When I said post more code I had something much more revealing in mind
Got to be honest - when suggestions are made and not followed (paste/datasheet view) and seemingly not addressed it makes it harder to keep an old guy's attention for long.

[Welshgasman]

I tried the following to no avail:

strSQL= "select * from tbl_PPISD where tbl_PPISD.[FPNumber] = " & Me.txtProjectNumber & "'""
Set rs = db.OpenRecordset(strSQLc)

and even though the debugging print returns the correct result, I'm still missing a parameter.

Paste it into a sql window and see what Access complains about.?
If your parameter is correct, it will likely be the fieldname at fault?