Problem with Filters

[Leif]

When you use a filter in Access, and the filter will result in no matches (0
records) the default behavior of Access to go into record Add mode (if you
have add rights), or just display a blank form (if you do not have add
rights). For my application either of these options are acceptable.

So, I tried adding some code to the apply filter event. The code looks like
the following:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
' Check if no records return after filter. If so give message and
cancel filter. Otherwise
' the system will go into add mode for those will add rights.
On Error GoTo ApplyFilterError
If DCount("TransmittalID", "qryCDRTransmittal", Me.Filter) = 0 Then
MsgBox "Filter resulted in no records found. Filter is canceled",
vbExclamation, "No records found"
Cancel = True
End If
Exit Sub

ApplyFilterError:
MsgBox Err.Description & " Filter is canceled", vbExclamation, "Error
in Filter"
Cancel = True

End Sub

The error handling catches problems like trying to filter using text on a
numeric field.

However, the problem is this coding those not work for combo fields (works
fine for other fields). I have combo fields that use a lookup query to show
values linked to another table. The resulting filter, in me.filter, might
look like the following:

((Lookup_ToAFC.AFC="0651"))

It seems internally this works just fine for Access filtering. However,
DCOUNT, used above in the code, does not know how to handle this filter. I
get an error 2001, "You canceled the previous operator".

Has any had to deal with this type of problem before? Thanks for your help.

Regards,
Leif

[Jim Burke in Novi]

Whenever I've used D functions (DCOUnt, DLookup,etc.) I specify criteria for
the third parameter just as it would appear in a WHERE clause, but without
the WHERE, e.g. if I want only records where FieldA = 500, I just code...

DLookup(a1,a2,"FieldA = 500")

My guess is that in some cases your filter property is formatted properly
for that and in other cases it's not. You probably just need to change way
you're coding the criteria so that's it's not just the value of me.filter.
Maybe someone else knows better, but that's what I'd try. Also, you may need
to accomodate for when the criteria is checking on a text field. e.g. if you
want criteria to be for when FIeldB = "ABC" you need to code FieldB = ""ABC""
so that the criteria includes the quotes.

[Leif]

Jim,

Thanks for the suggestion. I actually was thinking of that idea myself. It
is a brute force type solution. I was hoping for a more general solution,
but I could not think of anything else that worked. I did think of trying to
check recordcount after the filter was applied using form_current event, but
unfortunately if the filter returns 0 records the form_current event does not
fire.

So, I had to look for each of the combo boxes on my form and do a replace of
the Lookup_..... to the field name from the query. Not elegent or general,
but it works.

Regards,
Leif

[mikej711]

What does that "Lookup_" prefix mean?

--Mike J

"Jim Burke in Novi" wrote:

[Leif]

It is automatically generated by Access. I allows access to search on field
in which you have established a lookup based on a combo box and a reference
table.

[bhammer]

Solved!

With Access 2000 or later, the Replace function is available (or you
can get sample code to make your own global function from others in
this usergroup). Use it to search your copied Filter string for any
instances of 'Lookup_cbo' (for example) and replace that string bit
with 'tbl'.

For example, if your combobox is named 'cboCompany' and displays the
Company name in the combo (with the CompanyID in the hidden column)
then any filter done by the user that includes that field will have
the string 'Lookup_'cboCompany.Company', and poor old confused Access
throws-up the Enter Parameter dialog asking you to enter a value for
'Lookup_cboCompany.Company', due the fact that there is no table or
query named 'cboCompany'. Solve this by editing the Filter string
using the Replace function.

Change:
'Lookup_cbo.Company.Company', to:
'tblCompany.Company'

That way, Access can find the value. This only works, of course, if
you have the underlying query in the form you filtered from, include
the table 'tblCompany''.

See Access Help for tips on using the Replace function.

[Leif]

Thanks bhammer. This is exactly want I ended up doing. Of course I needed
to do an outer join with the reference tables used by the combo boxes by my
form recordset, but that does work.