Open filtered report from unbound form command button

[BarryMK]

I've searched this one extensively and come up with nothing to help.

I have an unbound switchboard form with several command buttons.

I would like to set a filter in the code for each command button to open up my report "rptCurrentAll" to show all records where a field on the report called "Section" equals a variable.

For example where the filter is "NV" show all records where Section = NV
I suspect it's going to be a variant on DoCmd.OpenReport with a where clause but can't get the correct syntax.

 

[phatnq2002]

I've searched this one extensively and come up with nothing to help.

I have an unbound switchboard form with several command buttons.

I would like to set a filter in the code for each command button to open up my report "rptCurrentAll" to show all records where a field on the report called "Section" equals a variable.

For example where the filter is "NV" show all records where Section = NV
I suspect it's going to be a variant on DoCmd.OpenReport with a where clause but can't get the correct syntax.

DoCmd.OpenReport "rptCurrentAll", acViewPreView,, "Section = '" & txtFilter.Value & "'"


Note:
txtFilter: text box contains your filter value. If your filter is the caption of the command button, you use CommandButton.Caption instead.
Because field "section" has a String (Text) data type, so you must put the
single quote before and after the filter value.

 

[BarryMK]

Result!

Many thanks phatnq2002

I placed a hidden text box on the switchboard for each command button and named them txtFilterNV, txtFilterADMIN etc then added your code to each button referencing the relevant txFilter field. works just fine.

 

[BarryMK]

Now need to check for nulls

I'm reopening this thread as although the code is fine, if the particular section filtered for has no records I need to stop the form from loading.

I've tried a few ways to trap for no records with no success. I tried the following as I know the DCount method works on its own but .

DoCmd.OpenForm stDocName
    If DCount("*", "qryCurrentAll") = 0 Then
           MsgBox "No records to display."
    DoCmd.CancelEvent
Else
If DCount("*", "qryCurrentAll") >= 0 Then
            DoCmd.ApplyFilter , "Section = 'CM'"
        End If
   End If
Exit Sub

Any suggestions?

 

[boblarson]

In the command button code put

    If DCount("*", "qryCurrentAll") = 0 Then
           MsgBox "No records to display."
    Else
        DoCmd.OpenReport "rptCurrentAll", acViewPreView,, "Section = '" & txtFilter.Value & "'"
   End If

 

[phatnq2002]

I'm reopening this thread as although the code is fine, if the particular section filtered for has no records I need to stop the form from loading.

I've tried a few ways to trap for no records with no success. I tried the following as I know the DCount method works on its own but .

DoCmd.OpenForm stDocName
    If DCount("*", "qryCurrentAll") = 0 Then
           MsgBox "No records to display."
    DoCmd.CancelEvent
Else
If DCount("*", "qryCurrentAll") >= 0 Then
            DoCmd.ApplyFilter , "Section = 'CM'"
        End If
   End If
Exit Sub

Any suggestions?

The easy way:

First, modified the current code:
On Error Resume Next
DoCmd.OpenReport "rptCurrentAll", acViewPreView,, "Section = '" & txtFilter.Value & "'"
On Error Goto 0

Second, in the Report_NoData event procedure of the report, you put this code:
MsgBox "No record. Report will be closed."
Cancel = True


When you set Cancel = True, the report will not be loaded, then it is not opened, too. But there is an error returns to the DoCmd.OpenReport. So I put two On Error statement before and after the DoCmd.OpenReport. The first one says if occurs error, dont do anything, go ahead. The second says, the first no longer valid.