open recordset error on where clause

[67flyer]

I am getting an run time error 3061, too few parameters. expected 1 when i try to open the report. If i use this code: Set rsHR = Cdb.OpenRecordset("Select Count([HRID])as HR from qryrptflighthr WHERE PID = 'ae5698'"
the report will open but if using the code below i get the error. The me!pid equals ae5698.

Thanks for the Help.

--------------------
Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
Dim Cdb As DAO.Database
Dim rsHR As DAO.Recordset

'Rest the row counter
bRowCounter = 0
'Get the count of the max rows that have values
Set Cdb = CurrentDb
Set rsHR = Cdb.OpenRecordset("Select Count([HRID])as HR from qryrptflighthr WHERE PID = " & Me!PID)
bHRcount = Nz(rsHR!HR)
rsHR.Close
Set rsHR = Nothing
Set Cdb = Nothing
'Reset all controls to display in black for the first Record
Me!NSN.ForeColor = COLOR_BLACK
Me!ITEM.ForeColor = COLOR_BLACK
Me.ui.ForeColor = COLOR_BLACK
End Sub

 

[pbaldy]

You've answered your own question. The successful code contains the required single quotes around a text value. The failing code does not.

 

[67flyer]

I did this:
Set rsHR = Cdb.OpenRecordset("Select Count([HRID])as HR from qryrptflighthr WHERE PID = 'Me!PID'")
report opens but no data in it. The report it self has a record source of qryrptflighthr. should i do that? I tryed taking the record source off of the report and changing the openrecordset to select *, count..... but the error states i can't use * with a grouping.

Thanks,

 

[pbaldy]

The correct syntax would be:

Set rsHR = Cdb.OpenRecordset("Select Count([HRID])as HR from qryrptflighthr WHERE PID = '" & Me!PID & "'")

 

[67flyer]

that did it! I can hit myself for not seeing that, I have used that code before, '" & ... & "'"
But I couldn't think of it at all.

Thank you very much pbaldy!

 

[67flyer]

I am trying to sort on nsn now and the following code:

Set rsHR = Cdb.OpenRecordset("Select Count([HRID])as HR from qryrptflighthr WHERE PID = '" & Me!PID & "'" & ORDER BY nsn)

putting the sort in the qryrptflighthr doesn't sort it.

Thanks

 

[pbaldy]

The sort order would be irrelevant, since that SQL should only return one record no matter what (the count). In any case, you've left the order by bit outside the quotes.

 

[67flyer]

the code does return one record, the pid of a person. but there are several items associated with each pid.

Set rsHR = Cdb.OpenRecordset("Select Count([HRID])as HR from qryrptflighthr WHERE PID = '" & Me!PID & "' ORDER BY nsn") give me and 'excute a query that does not include the specified expression 'NSN' as part of an aggregate function.'

So i made did this code:

Set rsHR = Cdb.OpenRecordset("Select Count([HRID])as HR, nsn from qryrptflighthr WHERE PID = '" & Me!PID & "'" & ORDER BY nsn) but i get the same error.

still learning to use the sql code.

Thanks,

 

[pbaldy]

The first will only ever return one record, but I suppose the second could return multiple records. You'd need to add:

GROUP BY nsn

in the SQL to get rid of that error. The easiest way to learn that would be to use the QBE to build a query that returns what you want, then look at it in SQL view.

 

[67flyer]

this code:
Set rsHR = Cdb.OpenRecordset("Select NSN, ITEM, UI, ISSUEDQTY, PID, Count([HRID])as HR FROM qryrptflighthr GROUP BY NSN, ITEM, UI, ISSUEDQTY WHERE PID = '" & Me!PID & "'")

gives me syntax error (missing operator) in qry expression 'issuedqty where pid=jb4623"

found ref. to aggregate functions that all the other select items must be grouped by.

thanks

EDIT: changed the group by to after the were clause and the error went away but it only shows 1 record now. before it would show all 59 records associated with the pid.

 

[pbaldy]

You must not have used the QBE as recommended. If you had, it would have shown you that the WHERE clause must be before the GROUP BY clause.

 

[67flyer]

using QBE i get the following SQL:

SELECT Count(qryrptflighthr.PID) AS HR, qryrptflighthr.NSN, qryrptflighthr.ITEM, qryrptflighthr.UI, qryrptflighthr.ISSUEDQTY, qryrptflighthr.PID FROM qryrptflighthr WHERE (qryrptflighthr.PID) = '" & Me!PID & "' GROUP BY qryrptflighthr.NSN, qryrptflighthr.ITEM, qryrptflighthr.UI, qryrptflighthr.ISSUEDQTY, qryrptflighthr.PID ORDER BY qryrptflighthr.NSN;")

in QBE it list correctly except that the count is always at 1. so the report only shows 1 record. There should be 59. so the count is not working.

Thanks

 

[pbaldy]

You wouldn't want the "Me.PID" in the QBE, as Me is only valid in VBA (I didn't think to mention that earlier; sorry). Try and hardcode the value that would be on the form for testing. If that doesn't work, can you post a sample db?

 

[67flyer]

I did hard code the pid in QBE. I will post a sample db. Thanks for the help.

 

[67flyer]

sample db

here is the sample db. one button opens the working report(it's not sorted on nsn or item.)

the second button is using the code that is trying to sort on nsn.

Thanks,

 

[pbaldy]

I'm still not sure what you're trying to accomplish, but the code is okay. I think the SQL is wrong. In your working report, the SQL returns 1 row with a count of 49. In the non-working SQL, due the the various select and group by items, it returns 49 rows each with a count of 1 (as you'd expect). Given that it appears that all you use the recordset for is the count, why all the rest of it in the SQL? I guess I'm back to what I said earlier; get a query that returns what you want then copy it into VBA. If I copy your VBA SQL into a query, like I said I get 49 records with a count of 1 on each.

 

[67flyer]

Okay i'm dumb. Got it to work. instead of trying to sort on the qry that the report is based on or in the SQL code in the group heading print event. I put the field i want to order by in the ORDER BY field on the data tab of the reports propertys and of course it worked. So in this case the sort didn't work but Order by did. Go figure. (never had to do that before, could always use VBA or the qry the report is based on to do the sorting.)


Thanks for all the help Paul!!!