Form Filter and No Records

[croy]

I hava a form with some footer fields used to filter the
form. The form's underlying query has criteria that point
to those footer fields. Also in the footer section are
buttons for "Apply filter" and "Remove Filter". The Apply
Filter button simply invokes requery. The Remove Filter
button clears all the footer filter fields, and then
requerys.

It's working well, except... If someone chooses a
combination of criteria that results in no records, the
detail section comes up blank, which is fine. But when the
"Remove Filter" button (custom) is clicked, an error message
appears: "No current record" (OK or Help), and I can't
figure out how to trap it.

So I'm wondering if there's a way to have my own message
appear if the filter results in no records, much the way a
report will do, and abort the code before the Requery
happens.

I've seen some things on the web about cancelling the
opening of a form if there would be no records, but nothing
about aborting the filer on the same form.

--
Any help appreciated.
croy

[Patrick Finucane]

On Thursday, March 27, 2014 1:47:54 PM UTC-5, croy wrote:
> I hava a form with some footer fields used to filter the form. The form's underlying query has criteria that point to those footer fields. Also in the footer section are buttons for "Apply filter" and "Remove Filter". The Apply Filter button simply invokes requery. The Remove Filter button clears all the footer filter fields, and then requerys. It's working well, except... If someone chooses a combination of criteria that results in no records, the detail section comes up blank, which is fine. But when the "Remove Filter" button (custom) is clicked, an error message appears: "No current record" (OK or Help), and I can't figure out how to trap it. So I'm wondering if there's a way to have my own message appear if the filter results in no records, much the way a report will do, and abort the code before the Requery happens. I've seen some things on the web about cancelling the opening of a form if there would be no records, but nothing about aborting the filer on the same form. -- Any help appreciated. croy

I typically use an alternative to your method. Ex:
Me.form.Filter = "CustomerID = " & 1
ME.Form.FilterOn = True

I don't get your error messages.

[croy]

Thanks for that. I wish I was good enough with code to do
that for my two cbos (who and where), txtboxes for beginning
date and ending date, and the "notes" field. I've made it
work in the query, but getting the syntax correct in code is
just beyond me.

>I don't get your error messages.

I'm assuming you mean that with your way, the message
doesn't appear, rather than you don't understand my error
message.... ;0

--
croy

[Patrick Finucane]

On Thursday, March 27, 2014 6:12:40 PM UTC-5, croy wrote:
> On Thu, 27 Mar 2014 12:05:55 -0700 (PDT), Patrick Finucane <patrickf...@gmail.com> wrote: >On Thursday, March 27, 2014 1:47:54 PM UTC-5, croy wrote: >> I hava a form with some footer fields used to filter the form. The form's underlying query has criteria that point to those footer fields. Also in the footer section are buttons for "Apply filter" and "Remove Filter". The Apply Filter button simply invokes requery. The Remove Filter button clears all the footer filter fields, and then requerys. It's working well, except... If someone chooses a combination of criteria that results in no records, the detail section comes up blank, which is fine. But when the "Remove Filter" button (custom) is clicked, an error message appears: "No current record" (OK or Help), and I can't figure out how to trap it. So I'm wondering if there's a way to have my own message appear if the filter results in no records, much the way a report will do, and abort the code before the Requery happens. I've seen some things on the web about cancelling the opening of a form if there would be no records, but nothing about aborting the filer on the same form. -- >Any help appreciated. croy > >I typically use an alternative to your method. Ex: > Me.form.Filter = "CustomerID = " & 1 > ME.Form.FilterOn = True Thanks for that. I wish I was good enough with code to do that for my two cbos (who and where), txtboxes for beginning date and ending date, and the "notes" field. I've made it work in the query, but getting the syntax correct in code is just beyond me. >I don't get your error messages. I'm assuming you mean that with your way, the message doesn't appear, rather than you don't understand my error message.... ;0 -- croy

I might have a command button with the caption Set Filter. In the OnClick event I then do something like
Dim strF As String
IF Not IsNull(Me.ComboBox1Name) then
strF = "Field1NameToFilter = " & Me.ComboBox1Name
Endif
IF Not IsNull(Me.ComboBox2Name) then
If strF > "" then strF = strF & " And "
strF = strF & "Field2NameToFilter = " & Me.ComboBox2Name
Endif
Me.Filter = strF 'might be Me.Form.Filter
Me.FilterOn = (strF > "") 'might be Me.Form.FilterOn

All you need to do is replace the combo box names and field names to filter

[croy]

On Fri, 28 Mar 2014 06:33:09 -0700 (PDT), Patrick Finucane

Thank you again.

I have to take this in baby steps....

I've gotten one of the cbos (the simple one) to work like
you outlined:

Dim strF As String
If Not IsNull(Me.cboWhere) Then
strF = "GeogrId = " & Me.cboWhere
End If
Me.Filter = strF
Me.FilterOn = True

... and man, does that work slick!

The other filter cbo (cboWho) is more problematic. It deals
with info in a subform because of a one-to-many situation.
I've been trying to get that to work, but just can't seem to
get the syntax right.

--
croy

[Patrick Finucane]

If you are filtering the subform it would be
If strF > ""
Me.yoursubformname.form.filter = "YourFieldName = """ & strF & """"
Me.yoursubformname.form.filteron = true
Endif

Variable syntax:
If string, "Fieldname = """ & var & """"
If date, "Fieldname = #" & var & "#"
If number, , "Fieldname= " & var

[croy]

On Sat, 29 Mar 2014 10:49:44 -0700 (PDT), Patrick Finucane

Thank you again. I hope I'm not pulling down the
rabbit-hole!

Actually, I'm wanting to filter the main form for records
that have fields that match "Who", "where", between "begin
date" and "end date", and notes. The "Who" is the
bugger--it's the subform data. I just have no idea how to
add that into filter code. I've used Access 2000 for many
years, but have no coding or SQL background. Not to mention
being somewhat of a dimwit.

All of those filter fields would be "AND" situations
(cumulative).

--
croy

[Phil]

On 30/03/2014 00:12:23, croy wrote:
> On Sat, 29 Mar 2014 10:49:44 -0700 (PDT), Patrick Finucane
> <patrickf...@gmail.com> wrote:
>
>>On Friday, March 28, 2014 10:59:15 AM UTC-5, croy wrote:
>>> On Fri, 28 Mar 2014 06:33:09 -0700 (PDT), Patrick Finucane
>>>

>>

>>If you are filtering the subform it would be
>> If strF > ""
>> Me.yoursubformname.form.filter = "YourFieldName = """ & strF & """"
>> Me.yoursubformname.form.filteron = true
>> Endif
>>
>>Variable syntax:
>> If string, "Fieldname = """ & var & """"
>> If date, "Fieldname = #" & var & "#"
>> If number, , "Fieldname= " & var
>
>
> Thank you again. I hope I'm not pulling down the
> rabbit-hole!
>
> Actually, I'm wanting to filter the main form for records
> that have fields that match "Who", "where", between "begin
> date" and "end date", and notes. The "Who" is the
> bugger--it's the subform data. I just have no idea how to
> add that into filter code. I've used Access 2000 for many
> years, but have no coding or SQL background. Not to mention
> being somewhat of a dimwit.
>
> All of those filter fields would be "AND" situations
> (cumulative).
>

Probably something like
Criteria = Criteria & " AND Who = " & Chr$(34) & Me.SubFormName.Form.Who &
Chr$(34) Chr$(34) is a double quoted which is needed when trying to find an
alpha field

That assumes that the current record on the subform is the person you are
looking for.

Phil

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

[croy]

Actually, I want to filter the entire database for who,
where, when, and notes.

--
croy