Problem copying Filter made from ComboBox

[bhammer]

A2K

My code copies the Filter property from one form, to apply to a Report. The
idea is that user filters the records in the form, then hits the Report
button to get a report of those records. It works fine, except when the user
filters on one of the fields that is a combo box. What happens, is that a
parameter dialog pops-up asking for something like:

Lookup_cboName.Name

when really it needs the NameID
Nothing typed in the parameter dialog box seems to work.

Can the Filter be copied and used correctly in the Report when the user
filters directly on the form? from a combobox?

-Brad

 

[Guest]

I use the combo box click event to modify the form filter.

Me.FilterOn = False
Me.Filter = "[fieldname] = " & Me!comboboxname.Value
or
Me.Filter = Me.Filter & " And [fieldname] = " & Me!comboboxname.Value
Me.FilterOn = True

 

[bhammer]

Thanks Steve,

But in this case the combo is not necessarily the only field
comprising the filter. The user can filter on any field in the form. I
can get the filter string OK as long as the filter is made-up from
fields that happen to be text boxes, rather than combo boxes. It's the
combo boxes that trigger the parameter dialog when the report is
opened and the filter SQL applied.

Just to be clear, the OnUpdate event of the combo is not what I need
here. The user is only using the combo to Filter By Selection, and may
choose to filter on more than on field. A command button opens the
Report when the user is done filtering, so the OnUpdate event isn't
quite applicable.

thx
-Brad

 

[Guest]

First, I said Click event not Update event.

Next, the Me.Filter string contains every filter the user has requested
through the right click menu. You can add the combo box with:
Me.Filter = Me.Filter & " And [fieldname] = " & Me!comboboxname.Value
In the click event for the combo box. "fieldname" is the source of the
value in the combobox bound column.

 

[bhammer]

OK, Steve,

A little more digging . . .
and I found out that Access builds the Me.Filter string adding the
'Lookup_ ComboboxName.VisibleValue' whenever you Filter-By-Selection
on a combo box that is bound to the ID and does a lookup to the table
for the VisibleValue in the combo box. This is what makes the copied
filter ask for the parameter. So even appending the 'Me.cboName.Value'
to the Me.Filter it still has the 'Lookup_ . . ." bit in there.

I found a couple of old threads that explained this problem, but no
answer. The one that came closest was to Alias the table in the
RecordSource query with the same name as the combo box. But no
good. . .

-Brad

 

[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' 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.

-Brad