Filtering Report with a Form 1

[edgarchado]

Hi to All,

I am a new user to access and trying to build a small application for my Company.

I have a Report which shows all client orders. I also have a form with a command button which I use to filter the results of the report by date. If I enter a date for which an order exists I have no problem, and I get the roport for that date. If I enter a date for which there are no orders I get a report with an error value in the date field. What I need, is for the form to display a message saying "There are no orders for (the date entered by user)" and return to the form to enter a new date.

The code for my command button is:

Private Sub Command1_Click()
On Error GoTo PROC_ERR
Dim strFilter As String
If strFilter = "" Then
strFilter = "[Date] = [Delivery Date]"
Else
MsgBox "No Orders for that Date"
End If
DoCmd.OpenReport "Reporte_Pedidos", acViewPreview, , strFilter
PROC_EXIT:
Exit Sub
PROC_ERR:
If Err.Number = 2501 Then
DoCmd.OpenForm "Home"
End If
Resume PROC_EXIT
End Sub

Thank you very much.

 

[dhookom]

You use code in the On NoData event of the report like:

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "No records meeting your filter", _
         vbOKOnly + vbInformation, "Nothing to Report"
    Cancel = True
End Sub

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]

 

[edgarchado]

dhookom,

Thank you very much for your answer, but where exactle should I introduce it in my code?

 

[edgarchado]

dhookom,

I Have found the no data event thanks. It is working just as I needed.

 

[edgarchado]

One more question.

What should I add to on nodata event so that the date apears in the message box?

Thanks.

 

[dhookom]

The answer depends on where/how you "enter a date". If this is a parameter query, you can't use the date. If the date properly comes from a control on a form, you can use a reference to the control.

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]

 

[edgarchado]

how should I refrence the control in the other form?

 

[dhookom]

Assuming you have a form "frmDateEnter" and controls "txtStart" and "txtEnd":

Private Sub Report_NoData(Cancel As Integer)
    Dim strMsg as String
    strMsg = "No records between " & _
        Forms!frmDateEnter!txtStart & " and " & _
        Forms!frmDateEnter!txtEnd & "."
    MsgBox strMsg, vbOKOnly + vbInformation, "Nothing to Report"
    Cancel = True
End Sub

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]

 

[edgarchado]

dhookom,

Thanks for all your help. It is working just as I needed it to.

I have a final question. In the form where I have the commando button which lets me filter the form i have the following code:

Private Sub Command1_Click()
On Error GoTo PROC_ERR
strFilter = "[Fecha_de_Entrega] = [FECHA DE ENTREGA]"
DoCmd.OpenReport "infReporte_Pedidos", acViewPreview, , strFilter
PROC_EXIT:
Exit Sub
PROC_ERR:
If Err.Number = 2501 Then
DoCmd.OpenForm "frmhome"
End If
Resume PROC_EXIT
End Sub

With this code I need to allways input a date, if I don´´t the report closes. What should I add to the code to have the possibility to put nothing in the emerging "Enter parameter value" box and not filter the report.

Thanks in advance.

 

[dhookom]

Add a text box on your form to enter the date. Assuming a text box named "txtDate" to compare with a field "SomeDateField" in your report's record source query.

Private Sub Command1_Click()
   Dim strFilter as String
   On Error GoTo PROC_ERR
   strFilter = ""
   If Not IsNull(Me.txtDate) Then
      strFilter = "[SomeDateField] = #" & Me.txtDate & "#"
   End If
   DoCmd.OpenReport "infReporte_Pedidos", acViewPreview, , strFilter
PROC_EXIT:
  Exit Sub
PROC_ERR:
  If Err.Number = 2501 Then
        DoCmd.OpenForm "frmhome"
  End If
  Resume PROC_EXIT
End Sub

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]

 

[edgarchado]

dhookom,

I have found another way to do it. Thanks anyway for all your halp.