Error in Filter - 2010

[Leif]

I'm getting an error message when I try to use a filter in Access 2010 development and 2010 using /runtime. This filter works in Access 2003. I use a custom shortcut menu with the Filter For (followed by a box to type in the filter).

The field name is TOContractorProjectNo and it is combobox in a subform. The message after attempting to filter is:

"The object doesn't contain the Automation object 'Lookup_TOContractorProjectNo.' Filter is canceled"

The value I try to enter is in the drop-down list. An OK button is on the popup, which dismisses the message.

 

[jdraw]

Can you post a zip of your database? Remove anything confidential first.

 

[Leif]

I found the problem.

I had an applyfilter event. The purpose was to notify the user in the case a filter for did not find any records. The default action is when filter for does not find any records it immediately goes into Add mode. This can confuse the user.

So I grab the current filter content and do a DCount to see if the filter will get any hits. If it does then allow it to proceed. If it does not then display a message and cancel the filter.

In Access 2003 the filter content, for combo boxes, contains a prefix such as:

"Lookup_" followed by the field name. For example, "Lookup_AFC.AFC" So in my apply filter event routine I remove this prefix so that I can use the use it in the DCount.

In Access 2010 brackets are placed around the lookup prefix. For example, "[Lookup_AFC].[AFC]=".

When I remove the "Lookup_AFC" from the string, to use in my DCount the "[]" is left behind. The causes the automation error message I get.

So I'll need to add some code to check if the user is running Access 2003 or 2010.

Thanks Microsoft.