3016 too few parameters

[tagteam]

I have some code that i supposed to check that a payment responsibility id has been set.

Me.Refresh
Dim TestNum As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("qryValidatePaymentRespID")
If rst.RecordCount > 0 Then


TestNum = 1
 MsgBox "You have unassigned Line items, please exit and complete all line items"
 Exit Sub
Else
TestNum = 0


End If

The sql for qryValidatePaymentRespId is
SELECT tbl_ImportedRepairs.BillingInvoiceID, tbl_ImportedRepairs.PaymentRespID
FROM tbl_ImportedRepairs
WHERE (((tbl_ImportedRepairs.BillingInvoiceID)=[Forms]![frmApproveInvoice]![BillingInvoiceID]) AND ((tbl_ImportedRepairs.PaymentRespID) Is Null));

My thought is that if there are is more than 0 records where the PaymentRespId is not set it will give the message and exit the sub.
Instead all I get is Run-Time error 3061 Too few parameters. Expected 1.

[pbaldy]

It's because of the form reference in the query. OpenRecordset can't resolve it. The simplest solution is the Eval() function:

SELECT tbl_ImportedRepairs.BillingInvoiceID, tbl_ImportedRepairs.PaymentRespID
FROM tbl_ImportedRepairs
WHERE (((tbl_ImportedRepairs.BillingInvoiceID)=Eval('[Forms]![frmApproveInvoice]![BillingInvoiceID]')) AND ((tbl_ImportedRepairs.PaymentRespID) Is Null));

[tagteam]

Thank you that works perfect!

I read up on the Eval function but I am still not quite sure why this is needed to work. What I think is happening is that the code is requiring a number to be returned and the query has zero records but does not actually return a number unless you use the eval. I think that if the query comes up with no records the Eval function causes it to return a 0 and if it comes up with 1 or more records it will return that number of records as a number, say 7. Then due to the code if the number is higher than 0 it will print the message and exit the sub. If the query is empty it returns 0 and continues on with the code.

Is that what is happening?

Thanks

[pbaldy]

Is has nothing to do with how many records the query returns. As I said, it has to do with OpenRecordset not being able to resolve the form reference in the query. More here:

http://access.mvps.org/access/queries/qry0013.htm

Eval() is a trick that works around the issue.

[tagteam]

Ok, thanks I will read up on it

[pbaldy]

No problem. By the way, if all you're doing is testing the count of the records returned, a DCount() might be simpler than the recordset, and won't have the same problem.

[tagteam]

Ok, that sounds interesting but I am not sure how To do that. I would imagine I don't use the record set procedure.

I understand the dcount part I just dont know how I get that to return to the value I want

The following code doesnt work because I am not sure how to get it to look for Payment Resp iD is blank so each time TesNum is going to be greater than 1


TestNum = DCount("PaymentRespId", "tbl_ImportedRepairs", "BillingInvoiceID = '" & Forms!frmApproveInvoice!BillingInvoiceID & "'")
If TestNum > 0 Then
'TestNum = 1
MsgBox "You have unassigned Line items, please exit and complete all line items"
Exit Sub
Else
TestNum = 0

[pbaldy]

I would have expected


If DCount("*", "qryValidatePaymentRespID") > 0 Then

But you could probably do it all with the DCount(). Not knowing what the query looks like, hard to be more specific.

[tagteam]

And you would be correct in your expectation. I am sorry, I am not a good programmer and I am trying to learn and fumbling around.

I added it as you suggested and it works great and is much more simple and much cleaner. I may be able to reuse this method several places to accomplish similar tasks.