Too Few Parameters? HELP?

[GreAppMat]

strSQL = "SELECT DISTINCTROW Employee.ID, Projects.[Project ID], Projects.[Project Name], Projects.Mgr " + _
"FROM Projects INNER JOIN (Employee INNER JOIN [Time Sheets] ON Employee.ID = [Time Sheets].ID) " + _
"ON Projects.[Project ID] = [Time Sheets].[Project ID] " + _
"WHERE(((Employee.ID) = [Forms]![frmGetEmpNum]![cboEmpNum]))"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

The error message is displaying too few parameters, any ideas?

Greg

 

[KDg]

Hi,
it's normally because a field name is spelt wrong. What I usualy do is Debug.Print rst, then copy and paste it into the Query Builder SQL and see what error that gives me. Usually something like "The field/table xxx cannot be found"

 

[hughest]

Doesn't dbs.OpenRecordset(A,B,C) require THREE parameters...

 

[GreAppMat]

Do you know what three parameters they are?
I have only two needed: a- recordset source
b- type of recordset

 

[KDg]

You only really need one as the default is dynaset ( I think, but it might be snapshot , either way the other 2 are optional.

[This message has been edited by KDg (edited 12-14-1999).]

 

[Travis]

I think your problem is your Where Statement. You will get a to few parameter error if the value is Empty in your Where statement.

to check this add a debug.print stSQL after you stSQL = statement. Check the Where statement and see if it has a value or it ends like this:
Where ([EmployeeID]=)

As you can see there is no value. This could be your error. Some correction ideas.

1. Don't run the Query if there is no value
2. add the Null to Zero to the value
(e.g.; nz(forms!FrmMe.EmployeeId,0) )
what nz does is if the value is Null it will replace it with the value after the comma.

 

[Pat Hartman]

You can only reference a field on a form if the form is open. If the form is closed at the time you run the query, you will get the too few parameters... message.

 

[John Davis]

You have got to watch out for those nasty little Nulls--they will propogate with anything that has a plus. I would use ampersands to build your queries, not "+" signs. With a plus sign, the item before will not show if there is nothing in the item after. This can be used to your advantage:
& ", " +[whateverfield1] & ", " +[whateverfield2]

The example above doesn't leave a comma hanging if there is no value in the field after it.

The following example propogates any nulls on both sides of the concatenation:
(Me.mytextbox + Me.myothertextbox)

If one isn't there, the other one won't be.

Man, those Nulls are nasty little creatures.

 

[jbrumbau]

I had the same error as well but with a different cause. My query had a reference to another form ([Forms]!frmLogin!txt...), which apparently gives JET fits. The solution is to use the following code:

Function removeFormRefs(ByRef qryName As String) As QueryDef
    Dim tempDB As Database
    Dim prm As Parameter
    Dim qdf As QueryDef
 
    Set tempDB = CurrentDb
    Set qdf = tempDB.QueryDefs(qryName)
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    Set removeFormRefs = qdf
End Function

Hope this helps!