Open a report based on it's query criteria 1

[irethedo]

I have a report that has a query as its data source and within the query I count the number of records that have their "problem" field set to "1"

If there are no records in the table with a "1" in the problem field, I would like to display a message box that states "no problem found" instead of opening up the report.

How can I set this up to do this?

Thanks

 

[dhookom]

You could run some code prior to opening the report like:

If DCount("*","qselRptRcrdSrc","[Problem]=1") =0 Then
    MsgBox "Ain't no problems to report"
 Else
    DoCmd.OpenReport......
End If

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[irethedo]

Thanks Duane-

I tried what you suggested and ended up with the following code:

On Error GoTo Err_PrintReports

If Me!psteam = "ALL" Then
If DCount("*", "problemcode qry", "[Total Failure]=1") = 0 Then
MsgBox "Ain't no problems to report"
Else
DoCmd.OpenReport "Failure by Problem Report", PrintMode
End If
Else
If DCount("*", "problemcode qry", "[Total Failure]=1") = 0 Then
MsgBox "Ain't no problems to report"
Else
DoCmd.OpenReport "team Failure by Problem Report", PrintMode
End If
End If



Err_PrintReports:
Select Case Err.Number
Case 2501 'blow off "Resume without error" message
Resume Exit_PrintReports
Case Else
MsgBox Err.Description
' Resume Exit_PrintReports
End Select

My query is called "problemcode qry" and the field is called Total Failure which is set to a one if a problem occured. When I run this code I get an error message that states, "You canceled the previous operation"..

Any ideas what I am doing wrong here?

 

[dhookom]

Where is this code running? Also, I don't see any code for Exit_PrintReports.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[irethedo]

I have a form which contains a start date field (startdate), an end date field (enddate), and a team field (psteam) and when click a preview or print button it drops down into the following code.

The psteam filed is a drop down list that is used to select the information that is sorted for a specific team or if All is selected, there is a seperate report with its own query without the team criteria.

The form also has a group of buttons which allows the user to select a report to eiher preview or print.

I am attempting to keep the report in case 3 from appearing if the "problemcode qry" query does not return any records with a "1" in the [Total Failure] field which is specified with the criteria of > 0 in the query.

Here is the following code that is givng me the error that i reported above...

Sub PrintReports(PrintMode As Integer)

On Error GoTo Err_PrintReports

Select Case Me!ReportToPrint
Case 1
DoCmd.OpenReport "FPY", PrintMode

Case 2
If Me!psteam = "ALL" Then
DoCmd.OpenReport "fpy by operation rpt", PrintMode
Else
DoCmd.OpenReport "team fpy by operation rpt", PrintMode
End If

Case 3
If Me!psteam = "ALL" Then
If DCount("*", "problemcode qry", "[Total Failure]=1") = 0 Then
MsgBox "Ain't no problems to report"
Else
DoCmd.OpenReport "Failure by Problem Report", PrintMode
End If
Else
If DCount("*", "problemcode qry", "[Total Failure]=1") = 0 Then
MsgBox "Ain't no problems to report"
Else
DoCmd.OpenReport "team Failure by Problem Report", PrintMode
End If
End If

Case 4
DoCmd.OpenReport "Failure Data Report By Assembly", PrintMode

Case 5
If Me!psteam = "ALL" Then
DoCmd.OpenReport "Bad Part Report", PrintMode
Else
DoCmd.OpenReport "team Bad Part Report", PrintMode
End If

Case 6
If (IsNull(startdate) Or IsNull(enddate)) Then GoTo invalidate

If Me!psteam = "ALL" Then
MsgBox "You must select an 8D Team for this report"
Exit Sub
Else
DoCmd.OpenReport "8d teams report card", PrintMode
End If

Case 7
If (IsNull(startdate) Or IsNull(enddate)) Then GoTo invalidate

' If IsNull(Nactteam) Then
' MsgBox "You must select an Nact Team for this report"
' Exit Sub
If Me!Nactteam = "ALL" Then
DoCmd.OpenReport "ALL Nact team report card", PrintMode
Else
DoCmd.OpenReport "Nact team report card", PrintMode
End If

End Select

Exit_PrintReports:
Exit Sub

invalidate:
MsgBox ("You must enter Start and End dates to print this report")
Exit Sub

Err_PrintReports:
Select Case Err.Number
Case 2501 'blow off "Resume without error" message
Resume Exit_PrintReports
Case Else
MsgBox Err.Description
' Resume Exit_PrintReports
End Select

End Sub

 

[dhookom]

I can't see anything that stands out in the code. Have you tried setting a break point and stepping through the code?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[irethedo]

Thanks Duane-

This one has me stumped...

I have set a breakpoint and when it steps into the statement:

If DCount("*", "problemcode qry", "[Total Failure]=1") = 0 Then

it then drops directly down to the following code and the case else is executed:

Err_PrintReports:
Select Case Err.Number
Case 2501 'blow off "Resume without error" message
Resume Exit_PrintReports
Case Else
MsgBox Err.Description
' Resume Exit_PrintReports
End Select

 

[dhookom]

Is the query a parameter query? What happens if you open the immediate window and enter

? DCount("*", "problemcode qry", "[Total Failure]=1")

I generally place []s around table/query names with spaces.
What is the SQL view of [Problemcode Qry]?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[irethedo]

Thanks Duane-

I get a syntax compile error if I put "DCount("*", "problemcode qry", "[Total Failure]=1")" in the open event of the report....

Also, I have also tried ["problemcode qry"], [problemcode qry], and "[problemcode qry]" but these do not work either as all give me a syntax error...

Here is my query SPL:

(in the title of the SPL code is - "problemcode qry:Select query")

SELECT [FPY Table].Date, [FPY Table].[Assembly Number], [FPY Table].ProblemCode1, [8D-team table].[8D-team], [FPY Table].ID, [FPY Table].Operator, [FPY Table].[Serial Number], [FPY Table].[Pass Quantity], [FPY Table].[Fail Quantity], [FPY Table].LatePass, [FPY Table].Rework, [FPY Table].[Part Number], [FPY Table].[Description of Problem], [FPY Table].PassYield, [FPY Table].FailYield, [FPY Table].depos, [FPY Table].multiprobs, [FPY Table].RejectNbr, [FPY Table].DTS, [FPY Table].TermID, [FPY Table].Operation
FROM [FPY Table] INNER JOIN [8D-team table] ON [FPY Table].[Assembly Number] = [8D-team table].[Part Number]
GROUP BY [FPY Table].Date, [FPY Table].[Assembly Number], [FPY Table].ProblemCode1, [8D-team table].[8D-team], [FPY Table].ID, [FPY Table].Operator, [FPY Table].[Serial Number], [FPY Table].[Pass Quantity], [FPY Table].[Fail Quantity], [FPY Table].LatePass, [FPY Table].Rework, [FPY Table].[Part Number], [FPY Table].[Description of Problem], [FPY Table].PassYield, [FPY Table].FailYield, [FPY Table].depos, [FPY Table].multiprobs, [FPY Table].RejectNbr, [FPY Table].DTS, [FPY Table].TermID, [FPY Table].Operation, [FPY Table].[Total Failure]
HAVING ((([FPY Table].Date) Between [forms]![print or view reports]![StartDate] And [forms]![print or view reports]![EndDate]) AND (([FPY Table].[Total Failure])>0));

 

[dhookom]

I don't see any selected field in your query named [Total Failure].

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[JoeAtWork]

I think your original requirements would be met by adding code to the report's NoDate event:

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "Nada, nien, no, non, zippo, zilch!"
    Cancel = True
End Sub

 

[irethedo]

Thanks Duane-
That is strange that the [Total Failure] was not shown as selected in the SQL as it was shown in the design view...

So I removed it and added it again in design view and then it worked... go figure...

thanks