Loop through query

[grades]

Hi,
I am having an issue on 2007 where I am trying to loop through query results and print a report based on a field in the query.

heres my code:

Private Sub Command81_Click()
Dim db As DAO.Database, rs As DAO.Recordset
Dim strReport As String
Set db = CurrentDb()
Set rs = db.Openrecordset("LateReport")

rs.MoveFirst
Do While Not rs.EOF
strReport = "Attendance" & rs.Fields("max")
DoCmd.OpenReport strReport
rs.MoveNext
Loop
End Sub

"LateReport" is the query which is dependant on a date field from a form (not this query in particular, but a query that LateReport uses).

So because of that i am getting the error:
Run time error 3061. Too few parameters: expected 1
on the line:
Set rs = db.Openrecordset("LateReport")

I have tried replacing it with:
Set rs = db.Openrecordset("SELECT * FROM LateReport WHERE Forms!frmmain!caldate.value = #" & Forms!frmmain!caldate.value & "#;")

As well as hard coding in a random date and other variations, to no avail.

Any ideas on how to make this work properly?

Thanks in advance

 

[steveboydon]

So you are opening a report based on a value in a field.

I'm not what your rs is doing, but it does not seem right. Once you open a recordset you do not need to loop through it, a form or report can have its recordsource set to the value of the query.

I assume you are opening the report when you click a button, try this

declare a global variable say

public g_dteLateReport Value as date(or whatever)

in the onclick event of the button put

g_dteLateReportValue = me.caldate


Then when the report fires up in the onload event try

me.recordsource = "select * from ................ where .... ='" & g_dteLateReportValue & "'"

note the quotes are singles and doubles as the value needs to be in single quotes.

Hope this what you are trying to do

Steve

 

[sparks80]

The example you have given will open up a report called Attendance and then the value from the "max" field, for each record in your query. You will therefore open the report as many times as the number of records in the query.

Is this what you intend to do? If so I can explain how to pass parameters to a query in VBA.

If not, what are you trying to achieve?

 

[grades]

Sparks,
thanks for your reply.
What field name should i be referencing exactly?
As i mentioned this query does not have a date field, but on of the queries it uses does.
if i close the main form and run the report it asks for parameter value: Forms!frmMain!caldate.Value
which is why i assume that's what it's looking for.

Forgive my ignorance here, but when you say "YourFieldName" what field are you referring to?
Thanks

 

[grades]

Yes, i have 4 reports names "attendance1" "attendance2" etc.
The "max" field is a number in the query 1-4
I am looping through the query results to grab the value for each person and print out the coresponding report by just concatenating the name.
If there is a better way then i am all ears, i am fairly new to access.

 

[sparks80]

I guess the answer to your question depends on what the reports contain.

If you want different formatting for each report then you will need a different report for each format.

If you want the same format, but select different data, then it is best to filter the results shown on a single report to only show the records you want.

Can you describe what you want the reports to contain, or better still upload a sample database? It is hard to answer your question without a little bit more information.

 

[grades]

I will try and give you a run down:

I have an employee database, and a button to print "Monthly lateness reports" in this case. We have 4 letters of warning from verbal to termination.
I have several queries filtering data:
How many times everyone has been late this month (anyone over 4)
If they have gotten a letter in the past 6 months, and which warning level it was ("max").
1 query for each level returning the date it was issued

The final query that my 4 reports are based on is called "LateReport", this would look something like this:

name------ lvl1 --------lvl2------ lvl3------- max
Bill ------1/2/2012 --2/4/2012 ---------------2
Fred ----3/4/2012 ----------------------------1
Peter ----------------------------------------------
Jim ------2/17/2012 --4/5/2012 --5/6/2012-- 3

There are NULL values if no date exists

Here is the code if it helps:
SELECT [1-LateList].employeeID, [1-LateList].name AS rname, [1-LateVerbalDate].lvd, [1-LateWrittenDate].lwd, [1-LateFinalDate].lfd, [1-LateList].CountOfemployeeID AS total, [1-LateWarningLevel].MaxOfwarningLevel AS [max], [1-LateList].occuranceType
FROM ((([1-LateList] LEFT JOIN [1-LateWarningLevel] ON [1-LateList].employeeID = [1-LateWarningLevel].employeeID) LEFT JOIN [1-LateVerbalDate] ON [1-LateWarningLevel].employeeID = [1-LateVerbalDate].employeeID) LEFT JOIN [1-LateWrittenDate] ON [1-LateWarningLevel].employeeID = [1-LateWrittenDate].employeeID) LEFT JOIN [1-LateFinalDate] ON [1-LateWarningLevel].employeeID = [1-LateFinalDate].employeeID;

Almost all of the queries above are based at some point on the date.
This is a variable i have in one of those queries (1-LateWarningLevel):
Between DateSerial(Year([forms]![frmmain]![caldate].[Value]),Month([forms]![frmmain]![caldate].[Value])-5,1) And DateSerial(Year([forms]![frmmain]![caldate].[Value]),Month([forms]![frmmain]![caldate].[Value])+1,0)

Then I have 4 seperate reports (Attendance1, attendance2, attendance3, attendance4), all based on this query grabbing the correct fields as all the data is in there. They are mostly the same, but the formatting is a bit different so I've created them seperately. They work just fine by themselves if i manually select an employee for each one.
Now to make monthly reports easy for the user, I wanted to cycle through each record (employee) in the query and print the appropriate report based on the "max" value.

Also, in order for this query chain to start, the user needs to select a date on the main Form which will serve as the variable for things like "in the past 6 months" and "this month" above.

I hope this helps, if you need to look at my other queries or the db itself I will upload it.
Thanks a lot.

 

[sparks80]

Hi,

Given the complexity of your query, perhaps the best thing to do is pass the parameter to the query. As you are using DAO you might as well stick with that.

What I think you need to do is use a querydef object. You can then supply the parameters before the recordset is opened. I'm a little bit rusty with DAO database objects as I tend to use ADO, but give this a go:

Private Sub Command81_Click()
Dim db As DAO.Database, qdf as DAO.QueryDef, rs As DAO.Recordset
Dim strReport As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("LateReport")
qdf.Parameters(0) =  Forms!frmmain!caldate.value
Set rs = qdf.OpenRecordset
 
rs.MoveFirst
Do While Not rs.EOF
   strReport = "Attendance" & rs.Fields("max")
   DoCmd.OpenReport strReport
   rs.MoveNext
Loop
End Sub

 

[grades]

Holy crap that works!
I just need to modify the way my reports are set up now, because since they are based on that query as well, each report prints one iteration of itself for every employee in the query regardless of the max level.
Not sure how to do that at the moment but thanks a lot for solving my looping issues!

 

[sparks80]

Hi,

Should be as simple as changing:

Docmd.OpenReport strReport

to

Docmd.OpenReport strReport, , , "employeeID=" & rs!employeeID

 

[grades]

Dude!
Brilliant! Thank you so much for your help, i would have spent days on this.
You truly are an Access jedi.

 

[sparks80]

The force is strong with you too my young Padawan.