Blank Report when User cancels underlying query prompt 1

[nastar1]

If a user cancels the prompt for a date range on a Report, a blank report window is displayed.

How can I have Access return the user to the Switchboard form if the query prompt is canceled?

 

[GingerR]

I would instead launch the report from a form.
So on an unbound form, put text boxes for the date range (i.e. txtDateRangeFrom and txtDateRangeTo). Put a button that opens the report. In the underlying report query, change the criteria to reference the form (i.e.

Between Forms!ReportMain!txtDateRangeFrom and Forms!ReportMain!txtDateRangeTo

In the code for the OnClick event of the button, start out with code something like this:

if me.txtDateRangeFrom = "" or isnull(me.txtDaterangeFrom) then
  msgbox "Please enter a From Date!",vbokOnly,"Missing Info"
  me.txtDateRangeFrom.setfocus
  exit sub
end if

Repeat the same for txtDateRangeTo. This way your fields are ensured to be filled in before you run the report.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[nastar1]

Thanks Ginger I'll give it a go.

One thing that this brings to mind right away is that I have 2 reports that use queries to prompt for a start and end date and 8 other reports using queries that also ask for the dates as well as another string prompt.

Would I need a separate form for each reports query prompts or could I use one?

 

[GingerR]

That's the beauty of it - you use just one. That way, say someone wants 5 of the reports and they want to look at the same date range--this is much better than them be prompted 5 different times for the same info! This is a pretty standard set-up, there might be something in the sample Northwind database or in a text book.

The basic way would be to have 10 buttons for your 10 different reports. You can label the buttons themselves, or have a label next to each one and have the buttons be little squares. Each button's code is pretty much the same, first making sure that the dates are filled in the text boxes, then opening one of the reports.

A more advanced way (tidier and easier to maintain the code) would be to have the reports listed in an option box (with the radio buttons) and one command button that says like "VIEW" or something on it. When the user hits the VIEW button, the code goes something like this (assuming an option box called optReportSelection):

'First check that dates are filled in

if me.txtDateRangeFrom = "" or isnull(me.txtDaterangeFrom) then
  msgbox "Please enter a From Date!",vbokOnly,"Missing Info"
  me.txtDateRangeFrom.setfocus
  exit sub
end if

'Dates are filled in so open the appropriate report

dim strReportName as String

Select Case Me.fraReportSelection
   Case 1
       strReportName = "Report1"
   Case 2
       strReportName = "Report2"
   Case 3
       strReportName = "Report3"
   etc
End Select

Docmd.OpenReport strReportName, acViewPreview

This way, you don't have 10 copies of the same coding, and it's just neater. So look up OPTION BOX (each radio button will have a different 'value' of like 1,2,3, etc)

So, just some ideas. Hope this helps!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html