Re: Repost: Errorhandling with Apply Filter Event

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Dirk Goldgar (dg_at_NOdataSPAMgnostics.com)
Date: 09/30/04 

Date: Thu, 30 Sep 2004 14:03:34 -0400

"Fred Boer" <Fredboer1@NOyahooSPAM.com> wrote in message
news:OA4hStwpEHA.3712@TK2MSFTNGP15.phx.gbl
> Hi Dirk:
>
> I spoke a bit too soon... I am still having problems, and I think I
> might have a clue why I couldn't make it work earlier; I was using a
> combobox for my filter field instead of a text box. If I use the
> shortcut "Filter For:" on a *Text* field it works perfectly. However,
> if I attempt to do a filter on a combobox control, I get error(s)...
> The only visible error message is 2001 "Cancelled previous". However,
> I set some break points and I have uncovered the following error
> message:
>
> The expression you entered as a query parameter produced this error:
> 'The object doesn't contain the Automation object
> 'Lookup_cboPubPlace.PubPlace."
>
> Anyway.. I'm starting to work on this to see if I can figure this one
> out.. if I can't I might post back. If you don't mind could you
> please check back here over the next day or so?

Sure, but I can tell you a little bit about your problem now. This is
the situation as I understand it. The value your combo box is
displaying comes from some column other than the bound column. When you
ask Access to filter on this value, it is "aware" of this and uses a
hidden query, "Lookup_cboPubPlace", to lookup the bound-column value
that has to be filtered on.

Consider this example: suppose your combo box's rowsource provides
these two columns of data:

    1, "Fred"
    2, "Dirk"
    3, "Jeff"

Suppose also that the bound column is column 1, the number, but you're
displaying column 2, the name. Now you go to filter your form for all
records with the value "Fred" in the combo box. But "Fred" isn't
actually stored in the data, because this isn't the bound column.
Therefore, what I think Access does is build a query to look up the
bound-column value that corresponds to the displayed value (or it may
already have created the query to use with the combo box, I'm not sure),
join the form's recordsource to that query, and make a filter that
applies your criterion to the looked-up field from that query. I should
say that this is just my conclusions based on some fooling around I did
with this a long time ago; it shouldn't be taken as gospel.

In the case of your form, code, and error message, "Lookup_cboPubPlace"
is the name of the hidden query Access is using to filter the form. The
problem is that, when you go to use that filter yourself, as in your
DLookup, you don't have access to that hidden query.

As I said, I fooled around with this for a while a couple of years ago.
I was thinking about developing a general-purpose mechanism for
transforming such lookup references in filters into criteria that could
be applied directly recordsources, outside of built-in filtering
process. I was still struggling with it when other matters intervened,
and I put it aside, and haven't picked it up since.

I'm going to think about it a bit more now. In your case, if you don't
need a general solution, you can probably take the filter, parse out all
the criteria that refer to combo or listbox lookups, and transform them
yourself into corresponding criteria that refer only to the data
actually present in the recordsource. That would be a bit of work and
coding, but not really all that complicated if you know the names of the
combos in question.

Alternative to that would be finding a different way to accomplish your
greater goal, which as I understand it is just displaying a message if
the user tries to apply a filter that returns no records.

I'll get back to you. 

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)

Relevant Pages

  • Re: Display if count = 5
    ... criteria line of the field that is being counted, ... And, if you count something in the design grid in a query, ... if you have designed a complicated filter. ...
    (microsoft.public.access.queries)
  • Re: other table that stores data differently than ADO.Net datatable?
    ... >> would suggest making some sort of a "search" type of screen and filter ... >> criteria for your where clause, that will help slim down your list. ... >>> query the database everytime the user does a scroll (or page up/page ... >>> will display the next N records once the user has reached the last ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Near Suicidal!
    ... box to filter by pressing a button on the form where the list ... By pressing on the button I want to pass a parameter (or ... to the the query the list's rowSource is based on. ... example) into the fldChargeType's criteria row and ...
    (microsoft.public.access.formscoding)
  • Re: Query only showing specific records - i need all - please help
    ... If the data is in the table but not in the form, you may have a filter on ... If the form is based on a query, then, when you look at the ... have a criteria in the query that filter out those records. ... You could also have use a Format to format the NULL values as displaying ...
    (microsoft.public.access.queries)
  • Re: access 2003
    ... the "ChooseJob_AfterUpdate Event" uses this filter ... the form to show a new set of records: For, as you know, the query points to ... the two combo boxes for criteria information. ...
    (microsoft.public.access.conversion)