Printing multiple reports in a Event procedure using the WHERE Condition...

[casinc815]

I have a modal form that is being used to generate reports for an Access 2007 application. On one specific button, I have an event procedure that was designed to print multiple (5) reports automatically and pass the Where condition to each report.



The condition is a Student's name: LastName, First Name. Each report is based on a query and the criteria for each query is the same, the student's name. An Input box allows for the entry of the name and is then parsed to extract the LastName and the First name. The two strings are then concatenated with a comma and space to give the same result back: LastName, FirstName; ie. Smith, John. This string is then used in the WHERECondition as FullName = "StudentName = " & "'" & LastName & ", " & FirstName & "'"MsgBox FullName, vbOKOnly

DoCmd.OpenReport ("rpt_WTWResults"), , , WhereCondition:=FullName
DoCmd.OpenReport ("rpt_FountasP"), , , WhereCondition:=FullName

The Message box checks the validity of the string and the string is used on the DoCmd line to execute the report for printing. Eventually the MsgBox will be removed from the procedure. The report is based on a query with a StudentName criteria for both(all) reports. The first command line is executed, a property sheet appears indicating a report was sent to the printer then it appears the query asks for the name again. I enter the name from the Message box and the command prints the report. The second command line is executed without a problem. It works just like it was designed and prints the report automatically. Eventually I would like to have a Case statement that allows me to group report printing based on the case criteria. Any ideas why the first command line did not print automatically while the second line did? Oh, all strings in this code were declared at the top of the procedure as string variables.

Thanks for you help, everyone! -- Jim

[ItsMe]

Is there a way you can use primary keys to match foreign keys in your where clause?

[casinc815]

Is there a way you can use primary keys to match foreign keys in your where clause?

The FullName in the code description above, is the primary key. I think I understand your implication but I believe
the error lies in how the WhereCondition statement is used and why it is not recognized in the first instance but it is recognized and prints the report in the second instance without interference.

Just for completeness, the primary key on the two tables in this relationship are StudentName, SchYear, SchTerm and SchGrade. This key structure will allow for the students to be tracked from year to year. Therefore, a group of reports would be required to all the teachers to monitor the progress of the student. From the IT developers side, this approach should be flexible for the 4 - 5 years the student is in this school district before moving on to high school. So the subquery joins the latter three key parts of this composite and requires the input of the StudentName. This in effect eliminates an instance of cousins having the same name. I have that problem in this application and the school year and grade rectified that nicely.

Thank you for your support!

[ItsMe]

I am not convinced your where clause within the DoCmd is function in either DoCmd statement.

Why do you believe it is working in one of them? Did you cross reference the results and verify the data?

Why are you using a colon within your where clause?

Can you post the code that shows your declarations and the strings that comprise your criteria?

[ssanfu]

@ItsMe

Why are you using a colon within your where clause?

The ":=" is called a "Named Argument".

From Help:
named argument
An argument that has a name that is predefined in the object library. Instead of providing a value for each argument in a specified order expected by the syntax, you can use named arguments to assign values in any order. For example, suppose a method accepts three arguments:

DoSomeThing namedarg1, namedarg2, namedarg3

By assigning values to named arguments, you can use the following statement:

DoSomeThing namedarg3 := 4, namedarg2 := 5, namedarg1 := 20

Also see: http://bytes.com/topic/access/insigh...amed-arguments




@casinc815
I too would like to see the code.
From your description, I think you have a design issue that is going to bite you in the end. (pun intended )

Each report is based on a query and the criteria for each query is the same, the student's name. An Input box allows for the entry of the name and is then parsed to extract the LastName and the First name.

Instead of the input box, I would use two text box controls (FName & LName) on the form. Saves a LOT of parsing and the query or code can refer to the controls to get the student name without asking every time the student name is required. Much easier....

There are a couple of changes you should/could make:
You have:

 FullName = "StudentName = " & "'" & LastName & ", " & FirstName & "'"

The RED is not needed

 FullName = "StudentName = '" & LastName & ", " & FirstName & "'"

DoCmd.OpenReport ("rpt_WTWResults"), , , WhereCondition:=FullName
DoCmd.OpenReport ("rpt_FountasP"), , , WhereCondition:=FullName

Since you are using named arguments, you don't need the commas.

DoCmd.OpenReport ("rpt_WTWResults"), WhereCondition:=FullName
DoCmd.OpenReport ("rpt_FountasP"), WhereCondition:=FullName

[ItsMe]

Steve,

Getting rid of the commas will fix the problem?

[casinc815]

Steve,

Getting rid of the commas will fix the problem?

Great Suggestions! Leet me work on this and I will keep you posted.

ItsMe and Steve many thanks!

Jim

[casinc815]

Great Suggestions! Leet me work on this and I will keep you posted.

ItsMe and Steve many thanks!

Jim

People! Many thanks for the usggestions.

I took them to heart and have incorporated a few suggestions into the code.

What then happened was the light bulb when off. I checked the underlying queries to the reports and they
run in QBE. But, the first query had a Criteria = Student Name and the second query did not. When I removed
the criteria from the query, all reports print with the WhereCondition on the command line.

Thanks for getting the juices flowing again! Many thanks!

[ssanfu]

Excellent... ready to mark this solved?

[ItsMe]

Do you mind posting your named argument and your DoCmd line? I still do not understand how this is working for you and would like to learn.