Applying Filter by Form to forms that include multivalued fields

[gvbx52]

Hi all,


I'm a beginner Access user. I started learning the ropes a few weeks ago to help out in my new job, trying to go the extra mile and create a database of our affiliated experts. Here's a link to a sample of the database.

I'm running into trouble trying to apply Filter by Form to multivalued fields. If I click on Filter by Form, choose a value from an MVF (e.g. French from the 'Language' field), and click on 'Toggle Filter', I get a prompt to 'Enter Parameter Value'.

Oddly enough, if I OK the prompt, or if I cancel and close the filter altogether, all subsequent attempts to apply Filter by Form to an MVF are successful. If I close the form or quit Access, I'm back to receiving the 'Enter Parameter Value' prompt.

I also notice that on the first, unsuccessful, attempt, my chosen filtering criterion (e.g. French) is in between brackets ([French]), while it's in between quotation marks ("French") in the second, successful, attempt.

I'd really appreciate any pointers you'd have for me.




Cheers,
gvbx52

[ssanfu]

<snip>
I'm running into trouble trying to apply Filter by Form to multivalued fields. If I click on Filter by Form, choose a value from an MVF (e.g. French from the 'Language' field), and click on 'Toggle Filter', I get a prompt to 'Enter Parameter Value'.

Oddly enough, if I OK the prompt, or if I cancel and close the filter altogether, all subsequent attempts to apply Filter by Form to an MVF are successful. If I close the form or quit Access, I'm back to receiving the 'Enter Parameter Value' prompt.

I also notice that on the first, unsuccessful, attempt, my chosen filtering criterion (e.g. French) is in between brackets ([French]), while it's in between quotation marks ("French") in the second, successful, attempt.

I'd really appreciate any pointers you'd have for me.

That is one of the problems of using a MVF (multi value field).

Microsoft did not fully develop the MVF "feature". While technically a MVF meets normalization rules, there is a hidden hidden table that is not accessible by the programmer. Most experienced Access programmers do not use MVFs because they are so hard to work with.



You have a field, "Expert ID", that is an autonumber type field that is used as an "Expert" ID. This is a bad thing to do. Autonumber type fields are not guaranteed to be sequential, gap less or even positive (it can be negative) - it is only guaranteed to be unique.

AutoNumber
----------------
Purpose : Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.



You have look up fields in the table. These field values should be in tables. It is much easier to add values to a table than to edit the design of the tables. See The evils of lookup fields



Some suggestions:
------------------
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.