?
Solved

Report_No_Data (Return to Form "Criteria_Trips")

Posted on 2003-03-16
18
Medium Priority
?
289 Views
Last Modified: 2007-12-19
I have some code which is not giving me the desired result. Being an amature I am not able to know the commands to achieve.  The Report on No_Data returns the message "There were no recods that matched your selection criteria" and then cancels the report.  What I want to do is not cancel but return back to the form Criteria_Trips for the user to input again.  Below is the code...

-------------------------------------------------------------------
Private Sub Report_Close()
'Clear the input data on the form Criteria_Trips
'ResetFormToDefault Forms![Criteria_Trip Pay]

'Close the form Criteria_Trips
DoCmd.Close acForm, "Criteria_Trip Pay"
End Sub

Private Sub Report_NoData(Cancel As Integer)
' Display a message if user enters a date for which there are no records,
' and don't preview or print report.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

strMsg = "There were no records that matched your selection criteria."
intStyle = vbOKOnly
strTitle = "Criteria Range"

MsgBox strMsg, intStyle, strTitle

'Cancel the report
Cancel = True

End Sub

Private Sub Report_Open(Cancel As Integer)
' Open Criteria_Trip Pay form.
' IsLoaded function (defined in Utility Functions module)determines if specified form is open.

Dim strDocName As String

strDocName = "Criteria_Trip Pay"
' Set public variable to True so Shipping by Date Dialog knows that report is in its Open event.
blnOpening = True

' Open form.
DoCmd.OpenForm strDocName, , , , , acDialog

' If Criteria_Trip Pay isn't loaded, don't preview or print report.
' (User clicked Cancel button on form.)
If IsLoaded(strDocName) = False Then Cancel = True

'Set public variable to False, signifying that Open event is finished.
blnOpening = False
End Sub
--------------------------------------------------------------------------
0
Comment
Question by:Trygve Thayer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
18 Comments
 
LVL 4

Expert Comment

by:dotthei
ID: 8146503
Don't close the form.  Minimize or hide it (if form not modal and dialog).

If necessary to close the form IF there is data to report, close the form on Report OPen
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8148071
Set a report level variable to indicate if there is no data. Then close the criteria form only if the variable is False - eg, put this at the top of the report's code module:
Dim NoDataFound As Boolean

In the NoData event, set the variable to true. Then in the Close event, put:
If NoDataFound Then DoCmd.Close acForm, "Criteria_Trip Pay"
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8148080
Sorry - I wasn't thinking straight. The line should actually read:
If Not NoDataFound Then DoCmd.Close acForm, "Criteria_Trip Pay"
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 3

Author Comment

by:Trygve Thayer
ID: 8149154
Being the amature that I am could you copy and paste my code above with your recommended changes in it.  I'm a little lost.  If it helps the command buttons on the Criteria_Trips do this....OK does Me.Visible = False....the Cancel does a DoCmd.Close

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8149244
Put the:
Dim NoDataFound As Boolean

right at the top of the module, just below the Option lines but before any subs/functions.

Your OnNoData event procedure then reads:
Private Sub Report_NoData(Cancel As Integer)
' Display a message if user enters a date for which there are no records,
' and don't preview or print report.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

strMsg = "There were no records that matched your selection criteria."
intStyle = vbOKOnly
strTitle = "Criteria Range"

MsgBox strMsg, intStyle, strTitle
NoDataFound=True
'Cancel the report
Cancel = True
End Sub

Your OnClose event reads:
Private Sub Report_Close()
'Clear the input data on the form Criteria_Trips
'ResetFormToDefault Forms![Criteria_Trip Pay]

'Close the form Criteria_Trips
If Not NoDataFound Then DoCmd.Close acForm, "Criteria_Trip Pay"
End Sub
0
 
LVL 3

Author Comment

by:Trygve Thayer
ID: 8149248
Well here is what I did but it didn't work. Guess I have the placement or syntax wrong somewhere.

-------------------------------------------
Option Compare Database

Dim NoDataFound As Boolean

Private Sub Report_Close()
'ResetFormToDefault Forms![Criteria_Trip Pay]
'DoCmd.Close acForm, "Criteria_Trip Pay"
If Not NoDataFound Then DoCmd.Close acForm, "Criteria_Trip Pay"
End Sub

Private Sub Report_NoData(Cancel As Integer)
' Display a message if user enters a date for which there are no records,
' and don't preview or print report.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

strMsg = "There were no records that matched your selection criteria."
intStyle = vbOKOnly
strTitle = "Criteria Range"

MsgBox strMsg, intStyle, strTitle

NoDataFound = True

End Sub

Private Sub Report_Open(Cancel As Integer)
' Open Criteria_Trip Pay form.
' IsLoaded function (defined in Utility Functions module)determines if specified form is open.

Dim strDocName As String

strDocName = "Criteria_Trip Pay"
' Set public variable to True so Shipping by Date Dialog knows that report is in its Open event.
blnOpening = True

' Open form.
DoCmd.OpenForm strDocName, , , , , acDialog

' If Shipping by Date Dialog form isn't loaded, don't preview or print report.
' (User clicked Cancel button on form.)
'If IsLoaded(strDocName) = False Then Cancel = True
If IsLoaded(strDocName) = False Then Cancel = True
'Set public variable to False, signifying that Open event is finished.
blnOpening = False
End Sub
0
 
LVL 3

Author Comment

by:Trygve Thayer
ID: 8149277
OOOPS!

Posted the previous before your recent suggestion.  I am trying this now.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8149310
I think I may have misunderstood your requirement. The problem you have is that a report cannot be "requeried" - by the time the NoData event fires, the report is already in a "state of no return" - that is, it knows it either has records or it doesn't.

You will need to modify the way you work the criteria, I think. When the user tries to open the report (presumably from a menu or a command button), open the criteria form. The criteria form can then open the report (rather than doing this in the OnOpen event of the report itself).

Alternatively, you can open a recordset to determine if there are any records, but this might be SLOW...what is the SQL of the report's record source ?
0
 
LVL 3

Author Comment

by:Trygve Thayer
ID: 8149335
Ok here is how it looks now.  When I put in a known value for date to date that will not return data here is what happens.  The message comes up that no data was found then after clicking ok the message comes up the report was cancelled then I am returned back to a form called Reports that I am using to call the report.  What I am wanting to do when no data is found it the message pops up that no data is found and then pops back up the Criteria_Trips for the user to "try again"  The report is basen on a query where I have set criteria to this.
Is Not Null And Between [Forms]![Criteria_Trip Pay]![txtbegdate] And [Forms]![Criteria_Trip Pay]![txtenddate]

------------------------------------------
Option Compare Database

Dim NoDataFound As Boolean


Private Sub Report_Close()
'Clear the input data on the form Criteria_Trips
'ResetFormToDefault Forms![Criteria_Trip Pay]

'Close the form Criteria_Trips
If Not NoDataFound Then DoCmd.Close acForm, "Criteria_Trip Pay"

End Sub

Private Sub Report_NoData(Cancel As Integer)
' Display a message if user enters a date for which there are no records,
' and don't preview or print report.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

strMsg = "There were no records that matched your selection criteria."
intStyle = vbOKOnly
strTitle = "Criteria Range"

MsgBox strMsg, intStyle, strTitle
NoDataFound = True
'Cancel the report
Cancel = True

End Sub

Private Sub Report_Open(Cancel As Integer)
' Open Criteria_Trip Pay form.
' IsLoaded function (defined in Utility Functions module)determines if specified form is open.

Dim strDocName As String

strDocName = "Criteria_Trip Pay"
' Set public variable to True so Shipping by Date Dialog knows that report is in its Open event.
blnOpening = True

' Open form.
DoCmd.OpenForm strDocName, , , , , acDialog

' If Shipping by Date Dialog form isn't loaded, don't preview or print report.
' (User clicked Cancel button on form.)
'If IsLoaded(strDocName) = False Then Cancel = True
If IsLoaded(strDocName) = False Then Cancel = True
'Set public variable to False, signifying that Open event is finished.
blnOpening = False
End Sub
0
 
LVL 3

Author Comment

by:Trygve Thayer
ID: 8149359
Ok, I will take the form called Reports and point the command button to open the Criteria_Trips Form.  After I do that how should the code look for the report?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8151178
You can keep everything the same as above except for the Open event. That can just be removed completely.
0
 
LVL 3

Author Comment

by:Trygve Thayer
ID: 8155945
I didn't get the desired results but something that will work.  I have an additional problem now that I have made it through that. I am opening the report acViewPreview...acNormal.  When the form pops up I do not have a Print button.  How do I get one of those to show up.  I am using a module called BasAccessHider that closes the access window an opening when I do a report. I have a printer icon but when I click print it prints the reports form (which is behind the report) instead of the report.  If I right click on the report preview and select print then I am prompted for a printer and am able to print the report.  I am doning this for an elderly lady and would like to make it as easy a possible.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8155998
The print button code should have something like:

DoCmd.OpenReport "YourReportName"

rather than DoCmd.PrintOut
0
 
LVL 3

Author Comment

by:Trygve Thayer
ID: 8156035
Thank you so much for helping me!!! Here is what I have.

*************************************************
1. The Form "Reports" that calls the report called "Trip Pay"

Option Compare Database

'------------------------------------------------------------
' Command3_Click
'
'------------------------------------------------------------
Private Sub Command3_Click()
On Error GoTo Command3_Click_Err

    DoCmd.OpenReport "Substitute Pay", acViewPreview, "", "", acNormal


Command3_Click_Exit:
    Exit Sub

Command3_Click_Err:
    MsgBox Error$
    Resume Command3_Click_Exit

End Sub


'------------------------------------------------------------
' Command7_Click
'
'------------------------------------------------------------
Private Sub Command7_Click()
On Error GoTo Command7_Click_Err

    DoCmd.OpenReport "Trip Pay", acViewPreview, "", "", acNormal

Command7_Click_Exit:
    Exit Sub

Command7_Click_Err:
    MsgBox Error$
    Resume Command7_Click_Exit

End Sub


'------------------------------------------------------------
' Command10_Click
'
'------------------------------------------------------------
Private Sub Command10_Click()
On Error GoTo Command10_Click_Err

    DoCmd.OpenReport "Sick Leave", acViewPreview, "", "", acNormal
   
Command10_Click_Exit:
    Exit Sub

Command10_Click_Err:
    MsgBox Error$
    Resume Command10_Click_Exit

End Sub


'------------------------------------------------------------
' Command16_Click
'
'------------------------------------------------------------
Private Sub Command16_Click()
On Error GoTo Command16_Click_Err

    DoCmd.Close acForm, "Reports"
    DoCmd.OpenForm "Main Menu", acNormal, "", "", , acNormal


Command16_Click_Exit:
    Exit Sub

Command16_Click_Err:
    MsgBox Error$
    Resume Command16_Click_Exit

End Sub


'------------------------------------------------------------
' Command26_Click
'
'------------------------------------------------------------
Private Sub Command26_Click()
On Error GoTo Command26_Click_Err

    DoCmd.OpenReport "Transportation Billing", acViewPreview, "", "", acNormal


Command26_Click_Exit:
    Exit Sub

Command26_Click_Err:
    MsgBox Error$
    Resume Command26_Click_Exit

End Sub


Private Sub Form_Open(Cancel As Integer)
DoCmd.RunMacro "mcrHide"
End Sub

-----------------------------------------

****************************************************
2. The Report "Trip Pay"

Option Compare Database

Private Sub Report_Close()
DoCmd.Close acForm, "Criteria_Trip Pay"
If Cancel = False Then DoCmd.OpenForm "Reports"

End Sub

Private Sub Report_NoData(Cancel As Integer)
' Display a message if user enters a date for which there are no records,
' and don't preview or print report.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

strMsg = "There were no records that matched your selection criteria."
intStyle = vbOKOnly
strTitle = "Criteria Range"

DoCmd.Close acForm, "Reports"

MsgBox strMsg, intStyle, strTitle

Cancel = True

DoCmd.OpenForm "Reports"

End Sub

Private Sub Report_Open(Cancel As Integer)
' Open Criteria_Trip Pay form.
' IsLoaded function (defined in Utility Functions module)determines if specified form is open.

Dim strDocName As String

strDocName = "Criteria_Trip Pay"

' Set public variable to True so Shipping by Date Dialog knows that report is in its Open event.
blnOpening = True

' Open form.
DoCmd.OpenForm strDocName, , , , , acDialog

'If Criteria Dialog form isn't loaded, don't preview or print report (User clicked Cancel button on form.)
If IsLoaded(strDocName) = False Then Cancel = True
If Cancel = False Then DoCmd.Close acForm, "Reports"
If Cancel = False Then DoCmd.RunMacro "mcrRestore"

'Set public variable to False, signifying that Open event is finished.
blnOpening = False
End Sub

********************************************************
3. The form Criteria_Trip Pay the report calls for criteria

Private Sub Cancel_Click()
DoCmd.Close
End Sub

Private Sub OK_Click()
Me.Visible = False
End Sub
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 600 total points
ID: 8156056
Sorry, but I think you may need to review the way you do this.

This is the logic that you would follow:
Open the Reports form.
User clicks a button to choose the Trip Pay report.
Open the Criteria_Trip Pay form.
When the user has filled in the criteria, they can click on a print or preview button.
The report opens in print or preview. If there is no data, the report brings up a message and exits.
The Criteria_Trip Pay form remains on screen.
0
 
LVL 3

Author Comment

by:Trygve Thayer
ID: 8156252
What is the command to open the report in Print mode?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8156481
Just use DoCmd.OpenReport without the acViewPreview (or replace acViewPreview with acViewNormal).
0
 
LVL 3

Author Comment

by:Trygve Thayer
ID: 8156670
Yee Haa!!!!(That's Tennesse talk for Thank You) I think I got it.  

Please review the code to see if anything looks potentially unsafe. As mentioned before I am an amature and have been working on this for 4 weeks.  Any advise to make this work clean is appreciated.

************************************************
Option Compare Database
_________________________________________________________
Private Sub Report_Close()

Dim strDocName As String

strDocName = "Criteria_Trip Pay"

DoCmd.OpenForm "Reports"
If IsLoaded(strDocName) Then DoCmd.Close acForm, "Criteria_Trip Pay" Else DoCmd.OpenForm "Criteria_Trip Pay"

End Sub
__________________________________________________________
Private Sub Report_NoData(Cancel As Integer)
' Display a message if user enters a date for which there are no records,
' and don't preview or print report.

DoCmd.Close acForm, "Criteria_Trip Pay"

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

strMsg = "There were no records that matched your selection criteria."
intStyle = vbOKOnly
strTitle = "Criteria Range"

MsgBox strMsg, intStyle, strTitle

Cancel = True

End Sub
__________________________________________________________
Private Sub Report_Open(Cancel As Integer)
' Open Criteria_Trip Pay form.
' IsLoaded function (defined in Utility Functions module)determines if specified form is open.

DoCmd.Close acForm, "Reports"
DoCmd.RunMacro "mcrRestore"

End Sub
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question