Link to home
Start Free TrialLog in
Avatar of conardb
conardbFlag for United States of America

asked on

How to display other than key / bound field on filter by form in access 2007

How can I display a field other than the bound column on a form used to filter by form and have filter by form feature continue to work?  I have the key field bound to each combo box but in order to have filter by form to work on the field the key field (i.e. auto number 1,2,3,4 etc... ) has to be displayed.  If I change the column widths to show the description field vs. the id field the filter by form does not work and returns a type mismatch error and does not filter the form.  I want to be able to display the decriptive field associated not the id and have filter by form work.
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
< I want to be able to display the decriptive field associated not the id and have filter by form work. >
That is not how Filter by Form works...

Is it a big deal to simply display both values side by side and filter on the Numeric value, while seeing the text?

If you need this level of Filtering, then it may be time to create your own interface for filtering.

But let's see what some other Experts post...


JeffCoachman
mbizup...

I'll let you take it from here to avoid confusion...


Jeff
Jeff,

Hang around...
Avatar of conardb

ASKER

Yes, I had the fields as combo boxes with record source as a query on the look up tables.  The search / filter-by-form is a split form.  If I change the widths to show the descriptive vs. key field filter by form wasn't working.  It looks to be related to design vs. layout view and either the order of or which field you change the width's in and the view.  I think mbizup provided the solution.
<Jeff,
Hang around... >
Don't worry, I am always skulking around somewhere...
;-)

Jeff
Avatar of conardb

ASKER

Thanks....
Avatar of conardb

ASKER

I edited the widths in layout view to the datasheet portion of the split form and was able to use filter by form while displaying the description field.  However, after closing the database and reopening the original problem returned... When using filter by form the type mismatch error is presented.  If I change the widths while in layout view again It works and the filter does display as being filtered on the key field value vs the description that is displayed.  It appeared to be resolved.  Any thought on why it would work while the db is open then stop working after closing and reopening?
Did you follow my suggestion of bringing in the description field by joining it's table in the recordsource query?

You may also need to add a seperate control such as a textbox to display that field.

Access's Filter by Form restricts you to the fields in your form's recordsource.

The problem you are having is that your combo box stores the ID even though it displays the description.  This is why you actually have to add the description in order for a Filter by Form to work.

I suggested that this solution was sub-optimal earlier because you wind up with fields that you do not necessarily want or need on your filter.... and you wind up adding complexity to your query and additional description fields that really do not need to be there.

The best approach, IMO is a custom filter form (see the example in the article I posted the URL for earlier).