Filter with No Records returned problem

[Kenln]

I have a Form with several buttons that filters and requeries the form.

If I filter one of them and it returns NO records the form goes blank. Nothing, no buttons (including exit), check boxes, sub-forms, labels, nothing...

The only way I have been able to prevent this is by setting ‘Additions’ to 'Yes'. Definitely not to the preferred choice since you cannot add records here and clicking check boxes gets the typical lock and invalid PK pop-up box.

Is there a way to test or show the complete form with no records returned???

I am getting ready to publish so any help would be appreciated.

Thanks,

 

[boblarson]

Do you have any lookups defined at table level?

Also, what is the code behind the button that has the problem?

 

[Kenln]

Here is the code for the Main Form and the combo box that resides in the form. I use this format depending on which button is clicked and by that which filter is reqested.

The Main Form access a query not a Table directly as does two sub-forms.





Code:
Private Sub cmd_Find_Not_Reviewed_Click()
Dim MySql As String


Me.FilterOn = False
Me.RecordSource = "qry_Emp_Name"
Me.Filter = "[Reviewed] = False"
Me.FilterOn = True

Me.OrderByOn = False
Me.OrderBy = "[Employee_FName], [Employee_LName], [Employee_ID]"
Me.OrderByOn = True

MySql = ""
MySql = "SELECT "
MySql = MySql + "qry_Emp_Name.Employee_ID, "
MySql = MySql + "qry_Emp_Name.[Employee_FName] + ' ' + qry_Emp_Name.[Employee_LName] + ' - ' + Str(qry_Emp_Name.[Employee_ID]) AS Name "

MySql = MySql + "FROM qry_Emp_Name "

MySql = MySql + "Where ("
MySql = MySql + "qry_Emp_Name.Reviewed = False "
MySql = MySql + ") "

MySql = MySql + "ORDER BY "
MySql = MySql + "qry_Emp_Name.Employee_FName, "
MySql = MySql + "qry_Emp_Name.Employee_LName, "
MySql = MySql + "qry_Emp_Name.Employee_ID "
MySql = MySql + ";"

Me.cbo_Employee_Name.RowSource = MySql

Me.Requery

Me.cbo_Employee_Name.SetFocus
Me.cmd_View_All.Enabled = True
Me.cmd_Find_Not_Reviewed.Enabled = False
Me.cmd_Sign_In.Enabled = True
Me.cmd_Employees_wo_Hours.Enabled = True

End Sub

End Code

 

[Rabbie]

See this link for an explanation of your problem

 

[Kenln]

Right after

 Me.FilterOn = True

I added

    If Me.RecordsetClone.RecordCount = 0 Then
        Call sView_All_Records
        Dim strResponse As String
            strResponse = "There are no records" & vbCrLf & "that match this filter."
        strResponse = MsgBox(strResponse, vbInformation + vbOKOnly, "No records returned")

        Exit Sub
    End If

The form goes blank for a moment then I call the same routine that sets the form back to normal.

Thanks,