Display a MsgBox if no record found to run a report

Question

The report is initiated from a Form "F_SachberbeiterUnterlagen", which uses a ComboList to search for the name of a specialist in a Table named "Abgabe_Unterlagen" If the name is found the report is initiated displaying all records for the Specialist.

To avoid empty reports I'd like to display a MsgBox when no record for the Specialist exists. My code doesn't work I tried different versions already.

can anyone help ?

Private Sub SachBearbeiterBericht_Click()
Me.Filter = "Sachbearbeiter_ID_F = " & Me.SachbearbeiterSuche
Me.FilterOn = True
If Not NULL Me.Filter Then
' Record found, proceed with generating the report
 DoCmd.OpenReport "R_MandantZuSachbearbeiter", acPreview, , "Sachbearbeiter_ID_F = " &         Me.SachbearbeiterSuche
Else
' No Record found display MsgBox
MsgBox "Der Bericht enthält keine Daten.", vbInformation + vbOKOnly, "Keine Daten"
End If

End Sub"

My code doesn't work I tried different versions already.

    Private Sub SachBearbeiterBericht_Click()
    Me.Filter = "Sachbearbeiter_ID_F = " & Me.SachbearbeiterSuche
    Me.FilterOn = True
    If Not NULL Me.Filter Then
    ' Record found, proceed with generating the report
    DoCmd.OpenReport "R_MandantZuSachbearbeiter", acPreview,,"Sachbearbeiter_ID_F = " &         Me.SachbearbeiterSuche
    Else
    ' No Record found display MsgBox
    MsgBox "Der Bericht enthält keine Daten.", vbInformation + vbOKOnly, "Keine Daten"
    End If

    End Sub"

To avoid empty reports I'd like to display a MsgBox when no record for the Specialist exists.

Answer 1

Use a simple DCount to see if any records exists prior to opening the report.

Not sure about the type of the Sachbearbeiter_ID_F field, but if it's text, its value needs to be enclosed in single quotes ' '.

Private Sub SachBearbeiterBericht_Click()

    'No record selected
    If IsNull(Me.SachbearbeiterSuche) Then
        MsgBox "Please select a value"
        Exit Sub
    End If

    'No records found
    If DCount("*", "Abgabe_Unterlagen", "Sachbearbeiter_ID_F = " & Me.SachbearbeiterSuche) = 0 Then
        MsgBox "No data"
        Exit Sub
    End If
    
    'Records found
    DoCmd.OpenReport ...
End Sub

Also, this syntax is incorrect (among other issues):

If Not NULL Me.Filter Then