Selection criteria on parameter forms

[amd via AccessMonster.com]

Hope someone can help me. . . .

I have set up a parameter form to select criteria from a combo box linked to
a table, and pass that criteria to a report.
The report is based on a query and I have used [Forms]![MyParameterForm]!
[MyTableField] coding in the query successfully.
Now I wish to add several other combo boxes, also linked back to other tables,
where the selection is optional' - that is, I can choose whether to enter
anything in those combo boxes or leave them blank, and if they are blank, the
report will return all records for that field (whilst still selecting records
from the other combo boxes that do have a selection).

I have been looking through the postings for about 2 hours and have printed
down various articles but I'm now a bit confused as to whether the coding
should be in the query or the form.
I have tried using = ([Forms]![MyParameterForm]![MyTableField] OR [Forms]!
[MyParameterForm]![MyTableField] IS NULL) in the query but that returned
nothing at all on my report and, when I went back to check it, found it was
gone from the query (I believe that Access does that sometimes when the
criteria is too complex).

I'm thinking there must be an easier way to do this by coding the fields on
the parameter form or in the report rather than in the query but I can't find
what is, to me, an explanation I can understand.

I'm not an Access expert but can usually manage to muddle through by
following threads and copying and pasting coding into visual basic and a lot
of what I read in the postings now makes sense to me (but not this time).

Could some kind person please explain what I need to do and precisely where I
need to do it.

Many Thanks
Alison

 

[Allen Browne]

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article explains how to build a filter for a form, but it's exactly the
same for a report.

It is possible to edit the WHERE clause of the query in SQL View for the
kind of thing you are trying to do, but it's less efficient. The article
above shows how to take that approach too if you really want to.

 

[amd via AccessMonster.com]

Hi Allen,

Thank you for the fast response. I really appreciate you taking the time to
talk me through this.

I have taken your advice and 'commented out' the 2 If ... End If blocks (now
appearing in green text in the code)

I then checked through your comments;

1) The search form was NOT bound (sorry, I missed that) but it is now. I
have set the record source in the forms properties to TblWeeklyLog (which is
the underlying table for all my data).

2) The filter fields are all in the table [TblWeeklyLog]

3) TblWeeklyLog does NOT have a field named LkUpCustomers but it has a field
named [Customer] where the Display Control is a combobox where the row source
type is Table/Query and the row source type is the table [LkUpCustomers]

4) The combo named CboCustomer is bound; it's row source is set to the table
named [LkUpCustomers].
I did wonder whether I should have set the source to [TblWeeklyLog] but have
tried both options for the row source without success.

To test the form I set up a very simple query using a date field [date] from
TblWeeklyLog and [Customer] from LkUpCustomers which has the relationship
back to [Customer] on TblWeeklyLog.
I entered [Forms]![ParameterForm]![CboCustomer] in the criteria for [Customer]
on the query but when I run the query I get the same message as before; Enter
Parameter Value: Forms!ParameterForm!CboCustomer

I am determined to get this right and I hope I am explaining things right and
using the correct terminology.

Thanks
Alison
(And apologies for the double posting last time)

Work with the form first.

The search form is bound to a table/query.
The fields you wish to filter on need to be in that table/query.
Does your table have a field named LkUpCustomers?

You have a combo named CboCustomer on your form.
Is this an unbound combo (nothing in its ControlSource)?
Is its RowSource set up so that its Bound Column contains the data that will
match the LkUpCustomers field?

Temporarily comment out the other 2 If ... End If blocks, until you get the
first one working. They look right, but once you get one working you can
apply the same technique to get the others working.

Hi Allen,

[quoted text clipped - 76 lines]

 

[Allen Browne]

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Download the application, and play with it.
See how the form works.
It applies a filter. There are no criterin the query.

When you follow how that works, you can do exactly the same thing by opening
the report form the form where you have the criteria boxes. You pass the
filter string as the WhereCondition for OpenReport. Details in the article.