Display query parameter in message box

[sheusz]

Hi all

Thanks for viewing.

I have a report based on a query that asks the user to enter a StartDate and EndDate in the CallDate field.

I'm using the reports On No Data event to display a message box if there is nothing to report and exit out.

MsgBox "Sorry, there is no data to report for the period specified.", vbInformation, "Report"

I'd like to add the query parameters StartDate and EndDate to the message box to show the user what they entered. I feel it's more useful for the end user.



Something like (?)
MsgBox "Sorry, there is no data to report for the period " & StarttDate & " and " & EndDate, vbInformation, "Report"

Is this possible?

I am displaying the parameters in an unbound text box (Period) on the report.

[Welshgasman]

Well if you do not have access to those controls when the report is open you cannot?
Otherwise just concatenate the form controls names into your message?

If the first situation applies, set some tempvars from those controls and refer to those in your message.

[ranman256]

instead of using param to ask user, use a form with: txtStartDate, txtEndDate.

add this ref to the query:
where [dateFld] between forms!fMyForm!txtStartDate and forms!fMyForm!txtEndDate

you can then get a rec count before opening the report:

iCt = Dcount("*","qsMyQuery")
if iCt= 0 then
   msgbox  "No data found"
else
   docmd.openreport "rMyReport"
endif

[sheusz]

Well if you do not have access to those controls when the report is open you cannot?
Otherwise just concatenate the form controls names into your message?

If the first situation applies, set some tempvars from those controls and refer to those in your message.

Hi Welshman, thanks for the reply.

I'm not sure that I completely understand.

My understanding is that when the report is initiated, the query underlying the report is run before the report is displayed. Does that mean that I would have access to the text box on the report named Period (=[Start Date - dd/mm/yy] & " and " & [End Date - dd/mm/yy])

I've tried adding the Period control to the message but get an error 2427 - You entered an expression that has no value.

Private Sub Report_NoData(Cancel As Integer)
    Dim message As String
    message = "Sorry, there is no data to report for the period specified. " & _
    Reports![svc_Unresolved Phone Calls Specify Dates]![Period]
    
    MsgBox message, vbInformation, "Report"
    Cancel = True
End Sub

I've never used tempvars, so am not sure how I would use them in this case.

[sheusz]

Hi Ranman

Thank you for the reply.

The report is one of many selected from a combo box on a form.

If I were to use a form to collect the dates for the report, can I have the form run on the On Load event of the report and then still use the On No Data event to trigger the message?

[Micron]

Put 2 texboxes on form: txtStartDate and txtEndDate. Enable the date picker for both. Use clicks on control, a calendar pops up so no worries about date format.
As for the rest, you can get the dates from
- report openArgs (if you pass them from the form that opens the report) or
- the form that holds the date textboxes mentioned above (has to be open, does not have to be visible)
Since there are no records, even if those date fields are part of the report their value will be Null so there's nothing to get from there.

EDIT - you'd use the OnNoData event to present the message box with the date values.

[sheusz]

Hi Micron

As I understand this, I would need to open the form prior to running the report. I can't use the report's On Load event to load the form?

It all comes down to the fact that the report is run from a combo box.

On Error GoTo Err_ServiceReports
    DoCmd.OpenReport "svc_" & Me.cboServiceReports, acPreview
    cboServiceReports.Value = Null
    
Err_ServiceReports:
    cboServiceReports.Value = Null
    Exit Sub
End Sub

[Micron]

I would need to open the form prior to running the report.

Your form is already open, no?

The report is one of many selected from a combo box on a form.

So why not put the aforementioned date textboxes on that form? If those would not apply for all reports in the combo list you could hide them based on selection. Move the code that opens the report to a command button. In button code, trap for invalid/no values for dates if applicable. When the no data event fires, get the dates from the same form that has the combo and date textboxes.

An alternative is to open a popup form that you create (modal) that holds the date textboxes. The button on that form would hide the form. The no data event gets the dates from that form instead. Close the popup form in the report close event, which runs when no data event cancels report opening. You don't even have to show the popup in order to close it.

Perhaps you will get other suggestions as well, but my preference would be to use your existing form.

[orange]

See this post for a link to Martin Green's Dynamic Reports.

[sheusz]

Hi Micron

Thanks for the reply.

The form that opens the report is shown in this screenshot. Not all of the reports that can be chosen require date criteria, so I'm reluctant to put the date boxes on this form because I don't think that the user will realise that this information needs to be entered. I suppose that I could force entry in to the date fields regardless, but that seems a bit heavy-handed.

I had already started playing with your alternative suggestion, but that is creating a whole set of other issues. As you can see in post #7 the combo box opens the report directly. The code behind the combo box is below, and this won't allow me to open the form before opening the report being that MSysObjects.Type value for forms is-32768

SELECT mid(MSysObjects.Name,5) FROM MsysObjects WHERE (Left$([Name],4)="svc_") And (MSysObjects.Type)=-32764 ORDER BY MSysObjects.Name;

Anyway, I'll keep tinkering and see what I can come up with.

[sheusz]

Hi Orange

Thanks for the link. I'd come across this previously, but I don't think it will work on this occasion. However I am using it to build another report generator.

[ArviLaanemets]

Based what I see in your post #10:

It looks like you run a report when you select it in the combo. Add a command button, and run the report from OnClick event of command button - this gives you option to change report conditions. And whenever you activate any of the report combos, save the name of selected report there to global variable added for storing the active report name.

Now at bottom of your report you have some empty space, where you can place controls for setting any filter conditions set for any report. By default all those controls, and their headers, are set invisible, and whenever you activate a combo to select a report, the filtering controls for this report are made visible, and all others are set invisible (for case there was another report activated previously). User sets filter conditions for this report, and clicks command button. OnClick events reads report name from global variable, generates the command string based on report name and filter controls values, and then runs the command string to run the report.

As only filter controls for single report are visible at any time moment, you can place filter controls of different reports in same position on form (overlapping).

[Welshgasman]

Here is how I managed to do it?
I had a common form to open for Forms, Reports and Queries.
Data came from a table.
If I needed parameters then I would open a dedicated form for those parameters (mainly start and end dates) and then run the report.
I passed any info I needed as OpenArgs.

In my tblObject I had
ObjectID
ObjectType
ObjectDescription
FormName
WhereClause

They were all brought into the combo.

HTH

Private Sub cmdOpen_Click()
' Either open a basic report/query, or open form for same with parameters
Dim strDescription As String, strName As String, strForm As String, strWhere As String
Dim iPreview As Integer
strDescription = Me.cboObject.Column(1)
strName = Me.cboObject.Column(2)
strForm = Me.cboObject.Column(3)
strWhere = Nz(cboObject.Column(4), "")


If Me.chkPreview Then
     iPreview = 2 'acPreview
Else
    iPreview = 0 ' aacNormal
End If


If Nz(strForm, "") = "" Then
    Select Case Me.txtObjectType
        Case "Report"
            If strWhere = "" Then
                DoCmd.OpenReport strName, iPreview, , , , strDescription
            Else
                DoCmd.OpenReport strName, iPreview, , strWhere, , strDescription
            End If
        Case "Query"
            DoCmd.OpenQuery strName
        Case "Form"
            DoCmd.OpenForm strName
        Case Else
            MsgBox "Object Type not catered for"
    End Select
Else
    DoCmd.OpenForm strForm, , , , , , strName
End If
End Sub

[sheusz]

Hi Arvil

Thanks for your reply.

The use of a command button is not practical in this case. There are some 30odd reports in the combo boxes. Event though only about 1/3rd of them require date criteria, I don't want a form loaded with command buttons to collect this information.

I'm thinking of splitting the date sensitive reports in to their own combo, which would allow me to get the result I want, but it require the user to work harder in finding a report that they would normally look for in a different combo.

[sheusz]

Hi Welshgasman

Thanks for sharing. I'll have a close look at this and see if I can make it fir for my application. Certainly seems interesting. Are you able to share a screenshot of the form you use with this?