How to ignore filter that gives no records

[John Milward]

Hi

I have several combo boxes on a form that allow the user to filter on
various fields
These generate a SQL string that is then applied to the form.filter
property.
This all works except that when the filter results in no records the form
goes blank.
How can I prevent this?

TIA

John

 

[Allen Browne]

The whole detail section of the form goes completely blank if both:
a) There are no records, and
b) New records cannot be added.

You know why (a) is happening.
(b) might be just that you set the form's AllowAdditions property to No.
If so, change the setting back to Yes, so it can display the new record.
You can prevent new records just by adding this line to the BeforeInsert
event procedure of the form:
Cancel = True

If (b) is because the form's recordsource is a query that is read-only and
you cannot change that scenario, the other alternative is to change or
remove the filter so there are records returned. Example:
Me.Filter = strWhere
Me.FilterOn = True
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No matches"
Me.FilterOn = False
End If

 

[Guest]

Your last suggestion with the If statement solved a similar problem for me
.... Thanks! Now what if I also, in the same If statement, want to disable
form buttons called previousRecord and nextRecord? My form has a filter on it
using a value from another form. If no records are found, your message box
displays. Once the user clicks OK, they can then scroll through all the
records in the database. I'd rather (a) hide the entire form except for a
Close Form button, or (b) dim the nextRecord and previousRecord buttons.

GwenH

 

[Allen Browne]

It is only the Detail section of the form that goes blank.
Move your Close button into the Form Footer section.

BTW, if your form can be edited, you need to be aware of this serious bug
with the Close action:
Losing data when you close a form
at:
http://allenbrowne.com/bug-01.html

 

[Guest]

Thanks for the response ... your solution worked. And my form can't be
edited, so I don't have to worry about the bug.