Refiltering a report on No Data

[Noel2]

I sure hope someone can help me with this. On the Open event of the Report I ask the user for a beginning date and an ending date to filter the data on as follows:

Private Sub Report_Open(Cancel As Integer)
Dim strFltr As String

GStrapEd = InputBox("Please enter start date below:&quot

If GStrappED() = "" Then
Cancel = True
Else
GStrapIng = InputBox("Please enter end date below:&quot

If GStrappING() = "" Then
GStrapIng = GStrappED()
End If

strFltr = "(qry_po_log_rpt.dateordered Between #" & GStrappED() & _
"# AND #" & GStrappING() & "#)"
Filter = strFltr
FilterOn = True
End If
End Sub


The GstrappED and GStrappING are defined in my global module so that I can use these values later. Their functions are as follows:

Public GStrapEd As String
Public GStrapIng As String

Public Function GStrappING() As String

GStrappING = GStrapIng

End Function

Public Function GStrappED() As String

GStrappED = GStrapEd

End Function

The problem comes when I get a report with no data. I want the user to be able to enter new dates if they receive no data the first time around. So far I have come up with the following:

Private Sub Report_NoData(Cancel As Integer)
Dim strTemp As Long

strTemp = MsgBox("There are no invoices for this period" & vbCrLf & Chr(13) & _
"Would you like to try a different set of dates?", vbYesNo, "no data&quot

If strTemp = 6 Then
Filter = ""
FilterOn = False
Report_Open (0)
Else
Cancel = True
End If
End Sub

I can't seem to get this to work. It just continues to ask if you would like different dates. Please let me know what you think.

 

[dhookom]

Is there a reason why you don't have a form with a couple text boxes to enter the dates. You can then just display a message and close the report if there is no data. The user can then change the values in the text boxes and try again.

I would set up the code in the form:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStart) then
strWhere = strWhere & " AND [dateordered] >=#" & _
Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) then
strWhere = strWhere & &quot; AND [dateordered] <=#&quot; & _
Me.txtEnd & &quot;# &quot;
End If
DoCmd.OpenReport &quot;rptA&quot;, acViewPreview, , strWhere


Duane
MS Access MVP

 

[Noel2]

you know I have that set up right now as a work around. Take a gander at this: on the forms button I have:

On Error Resume Next
Dim strTemp As Long

GStrapEd = InputBox(&quot;Please enter start date below:&quot

If GStrappED() = &quot;&quot; Then
Exit Sub
Else
GStrapIng = InputBox(&quot;Please enter end date below:&quot
If GStrappING() = &quot;&quot; Then
GStrapIng = GStrappED()
End If

If DCount(&quot;*&quot;, &quot;qry_po_log_rpt&quot = 0 Then
strTemp = MsgBox(&quot;There are no invoices for this period&quot; & Chr(13) & _
&quot;Would you like to try a different set of dates?&quot;, vbYesNo, _
&quot;no data&quot
If strTemp = 6 Then
Command86_Click
Else
Exit Sub
End If
Else
Form_frm_po_all.Visible = False
DoCmd.OpenReport &quot;rpt_po_log&quot;, acPreview
End If
End If

On the report open:

strFltr = &quot;(qry_po_log_rpt.dateordered Between #&quot; & GStrappED() & _
&quot;# AND #&quot; & GStrappING() & &quot;#)&quot;
Filter = strFltr
FilterOn = True

I just wanted to see if I can do this using just the report instead of the form. Please let me know what you think. I appreciate the quick response.

 

[dhookom]

I never use parameter queries or Input boxes for entering any criteria. It lacks control and integrity. Quite often I will use combo boxes to select dates or date ranges. Users appreciate this feature.

Since you have all the information you need in the code prior to opening the report, why don't you just add the where clause to your docmd.openreport? I have created many hundreds of reports for all kinds of applications and have never used the filter property as you are attempting.

Duane
MS Access MVP

 

[Noel2]

The only reason I wanted to use the filter property is because I just wanted to learn how to work the filter property in combination with the input boxes and to see if it was possible to do what I was asking. Also, if it wasn't possible, then why it wasn't possible. I could definitely use the idea that you had with the docmd.openreport adding the where clause. It does pretty much the same thing anyways. I just always try to find a new way of doing something when I start a new project.

Noel

 

[dhookom]

I don't usually take (have) the time to experiment with different methods unless I am running in to trouble. I have tried enough stuff in the past that I know what works best for my way of developing applications. I stick fairly close to my own &quot;best practices&quot;. This will hopefully make my applications more consistent and maintainable.

Duane
MS Access MVP

 

[Noel2]

Thank you very much for the advise