OpenRecordset Error 2

[jdgreen]

I am continually getting Run-Time error '3061': Too few parameters. Expected 2.
Here is the code:

Function PNPullTogether() As String

Dim strPN As String
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("qryPartNumberWO", dbOpenDynaset)
strPN = ""
If rst.EOF Or rst.BOF Then
Exit Function
Else
rst.MoveFirst
Do Until rst.EOF
If strPN = "" Then
strPN = rst!Prefix & "-" & rs!Body & "-" & rst!Suffix
Else
strPN = strPN & ", " & rst!Prefix & "-" & rst!Body & "-" & rst!Suffix
End If
rst.MoveNext
Loop
End If

PNPullTogether = strPN

End Function

The error appears at the OpenRecordset line. The query that is called out runs fine and is not misspelled. I have tried using this function on both forms and reports, calling it as a default value and as a control source. The query it is calling out is the result of two other queries that both limit the amount of records based on a form. Both of these queries also run fine. I have checked spelling in all of the queries multiple times.

John Green

 

[PaulBricker]

But you do have criteria in those queries, and that is the problem. What you will have to do is use an SQL string in the recordset and set your criteria there. For example

Dim strSQL as String
strSQL = "Select * From tblPartNumberWO Where tblPartNumberWO.Date = #" & Forms!FormName!Field1 & "# And tblPartNumberWO.Region = '" & Forms!FormName!Field2 & "'"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

rest of code

Give is a try and post back with problems.

Paul

 

[jdgreen]

Here is where I'm at. I am getting a data type mismatch which I am pretty sure is coming from the date section. I completely forgot about the pound signs to signify a date value, should there also be pound signs around the first part of the Where statement?

strSQL = "Select * From tblPartNumbers Where tblPartNumbers.DateAdded < #" & Forms!frmWorkOrderProcedure!RevDate & "# And tblPartNumbers.WorkOrder = '" & Forms!frmWorkOrderProcedure!WorkOrder & "'"



John Green

 

[Crowley16]

Just to be a pedant...

£ = pound sign
# = hash sign

anyway, you should only use the # sign around a specific date value, i.e. #01/05/04# and nowhere else...

Also, remember to be careful with the month/date orientation because access uses #month/date/year#, personally I always format my dates to medium just to be clear...

--------------------
Procrastinate Now!

 

[PaulBricker]

First thing I like to do when I get this error is to Debug.Print my SQL string. This will show you exactly what the string is seeing. You statement looks correct if
a. Forms!frmWorkOrderProcedure!RevDate is actually a date datatype and
b. Forms!frmWorkOrderProcedureRev!WorkOrder is a STRING datatype.
If WorkOrder is a number datatype then you need your syntax to look like this.

strSQL = "Select * From tblPartNumbers Where tblPartNumbers.DateAdded < #" & Forms!frmWorkOrderProcedure!RevDate & "# And tblPartNumbers.WorkOrder = " & Forms!frmWorkOrderProcedure!WorkOrder

Crowley16, in America, the # sign is called the "pound" sign because it denotes weight for us.

Paul

 

[Crowley16]

ahh, that yes, that does make more sense...

so in america, do you call hash tables pound tables?

--------------------
Procrastinate Now!

 

[jdgreen]

That was it. Extra thanks on this one.

John Green

 

[PaulBricker]

No no, we prefer to keep everything completely inconsistent. Haven't you ever listened to any of our politicians.

Paul

 

[Crowley16]

please don't get me started on your politcians...

the deludge of derogetory comments that I have concerning bush would probably flood the server...

mind you, ours isn't much better...

--------------------
Procrastinate Now!

 

[PaulBricker]

We'll avoid the red flags.

John, sorry for the sidetrack, glad it helped.

Paul