Using unbound textboxes as criteria

[Marshall Barton]

I have a form w/ a subform. One the form, I have several unbound controls
that the user inputs data into. These serve as the criteria that
continuously filters the query results shown in the subform. The query has
the form control names in the criteria of the applicable fields (ie. under
customer name, the criteria is [Forms]![FormName]![UnboundControl1]. I have
the "AfterUpdate" event of all the controls set to refresh the form
(me.refresh) so that the results in the subform (whose control source is the
query) continue to "drill down" as the unbound controls are filled in
(similar to the effect of the Autofilter arrows used in Excel).

What I would like to do is have it so that when a control is blanked out,
the records that were filtered by the previous text that was in the control
are again shown on AfterUpdate.

For example, when the user enters "ABC" in the "CustomerName" unbound
control, the records refresh to show only those w/ customer name of "ABC."
When user then enters "10/12/04" in the "OrderDate" unbound control, the
records are further filtered to show only Customer Name of "ABC" with orders
on 10/12/04. What I would like is when the user then deletes "10/12/04"
from the OrderDate control, the records refresh to show all the ABC orders
again, deleting the criteria and so showing more records. Right now it will
just show NO records, because no ABC customers records have an order date of
null, or "". I would like to do this for a number of criteria fields (there
will be about 8 total).

Change each field's criteria to:

WHERE ((fieldA = Forms!FormName!UnboundControl1) OR
(Forms!FormName!UnboundControl1 Is Null))
AND ((fieldB ...

 

[rgrantz]

I have a form w/ a subform. One the form, I have several unbound controls
that the user inputs data into. These serve as the criteria that
continuously filters the query results shown in the subform. The query has
the form control names in the criteria of the applicable fields (ie. under
customer name, the criteria is [Forms]![FormName]![UnboundControl1]. I have
the "AfterUpdate" event of all the controls set to refresh the form
(me.refresh) so that the results in the subform (whose control source is the
query) continue to "drill down" as the unbound controls are filled in
(similar to the effect of the Autofilter arrows used in Excel).

What I would like to do is have it so that when a control is blanked out,
the records that were filtered by the previous text that was in the control
are again shown on AfterUpdate.

For example, when the user enters "ABC" in the "CustomerName" unbound
control, the records refresh to show only those w/ customer name of "ABC."
When user then enters "10/12/04" in the "OrderDate" unbound control, the
records are further filtered to show only Customer Name of "ABC" with orders
on 10/12/04. What I would like is when the user then deletes "10/12/04"
from the OrderDate control, the records refresh to show all the ABC orders
again, deleting the criteria and so showing more records. Right now it will
just show NO records, because no ABC customers records have an order date of
null, or "". I would like to do this for a number of criteria fields (there
will be about 8 total). Excel's autofilter does this quite well.

I tried using an IIF expression in the query's criteria for each field that
has an associated unbound control on the main form:

(IIF ([Forms]![FormName]![Control1] = "",*, [Forms]![FormName]![Control1])

This did not work, however.

Does someone know how I can achieve this? Any help would be appreciated.


Thanks for reading.

 

[Marshall Barton]

You can do the same kind of thing with the dates:

WHERE ((datefield Between Forms!FormName!txtStart And
Forms!FormName!txtEnd) OR (Forms!FormName!txtStart Is Null)
OR (Forms!FormName!txtStart Is Null))

or something like that.

But this is really getting out of hand and more than a
little messy to deal with. How about using a different
approach that uses code to construct the subform's record
source SQL statement? This way, the code can check for the
unbound controls for Null and just not include a condition
in the Where clause.

strSQL = "SELECT ... FROM ... "
If Not IsNull(Me.txtLName) Then
strW = strW & " AND LName = """ & Me.txtLName & """"
End If
. . .
Me.subform.Form.RecordSource = strSQL _
& ("WHERE " + Mid(strW, 6))
--
Marsh
MVP [MS Access]

Thanks, that did the trick [snip]
As I think of it, I will probably run into a similarly confusing situation
when I do the date range criteria too. The user may be entering the Start
Date and End Date as well as all of these other criteria also, but could
also leave them as Null as with all the others. Can the "Between" be used
in this huge string of fieldname values and possible nulls too?

I have a form w/ a subform. One the form, I have several unbound controls
that the user inputs data into. These serve as the criteria that
continuously filters the query results shown in the subform. The query has
the form control names in the criteria of the applicable fields (ie. under
customer name, the criteria is [Forms]![FormName]![UnboundControl1]. I have
the "AfterUpdate" event of all the controls set to refresh the form
(me.refresh) so that the results in the subform (whose control source is the
query) continue to "drill down" as the unbound controls are filled in
(similar to the effect of the Autofilter arrows used in Excel).

What I would like to do is have it so that when a control is blanked out,
the records that were filtered by the previous text that was in the control
are again shown on AfterUpdate.

For example, when the user enters "ABC" in the "CustomerName" unbound
control, the records refresh to show only those w/ customer name of "ABC."
When user then enters "10/12/04" in the "OrderDate" unbound control, the
records are further filtered to show only Customer Name of "ABC" with orders
on 10/12/04. What I would like is when the user then deletes "10/12/04"
from the OrderDate control, the records refresh to show all the ABC orders
again, deleting the criteria and so showing more records. Right now it will
just show NO records, because no ABC customers records have an order date of
null, or "". I would like to do this for a number of criteria fields (there
will be about 8 total).

"Marshall Barton" wrote
Change each field's criteria to:

WHERE ((fieldA = Forms!FormName!UnboundControl1) OR
(Forms!FormName!UnboundControl1 Is Null))
AND ((fieldB ...

 

[rgrantz]

Marsh:

Thanks, that did the trick. I have a complicating issue, however. In the
table data that this query pulls from (using the unbound controls on the
Main Form as criteria), there are several different fields for employee
numbers. The employee numbers are related to a table that stores the
numbers and associates them with employee names, so that the query results
show the name instead of the number. Thus, in the query, I have several
different fields called EmpName, but each of them pulls from the same table
(I had to add the same table to the query design for as many entries in the
main data table there are that refer to employee numbers). Thus, there is
EmpName from table EmpList, EmpName from table EmpList_1, EmpName from table
Emplist_2, EmpName from table EmpList_3, etc. (the query builder, when
adding the same table multiple times, names them with the sequential _1, _2,
etc.). So, with each of these employee names being part of the criteria the
user needs to enter on the Main form (ie. all the orders for customer ABC on
10/12/04 that Mark entered and that Jason Shipped and that Thomas inspected,
etc., how do I refer to these EmpName fields in the "Where
(([EmpName]...etc." when they have the same title? I tried the
EmpName.EmpList_1 and EmpName.EmpList_2 syntax as field names, but it didn't
work.

Thanks again, I think I'm almost there.

As I think of it, I will probably run into a similarly confusing situation
when I do the date range criteria too. The user may be entering the Start
Date and End Date as well as all of these other criteria also, but could
also leave them as Null as with all the others. Can the "Between" be used
in this huge string of fieldname values and possible nulls too?

Thanks

I have a form w/ a subform. One the form, I have several unbound controls
that the user inputs data into. These serve as the criteria that
continuously filters the query results shown in the subform. The query has
the form control names in the criteria of the applicable fields (ie. under
customer name, the criteria is [Forms]![FormName]![UnboundControl1]. I have
the "AfterUpdate" event of all the controls set to refresh the form
(me.refresh) so that the results in the subform (whose control source is the
query) continue to "drill down" as the unbound controls are filled in
(similar to the effect of the Autofilter arrows used in Excel).

What I would like to do is have it so that when a control is blanked out,
the records that were filtered by the previous text that was in the control
are again shown on AfterUpdate.

For example, when the user enters "ABC" in the "CustomerName" unbound
control, the records refresh to show only those w/ customer name of "ABC."
When user then enters "10/12/04" in the "OrderDate" unbound control, the
records are further filtered to show only Customer Name of "ABC" with orders
on 10/12/04. What I would like is when the user then deletes "10/12/04"
from the OrderDate control, the records refresh to show all the ABC orders
again, deleting the criteria and so showing more records. Right now it will
just show NO records, because no ABC customers records have an order date of
null, or "". I would like to do this for a number of criteria fields (there
will be about 8 total).

Change each field's criteria to:

WHERE ((fieldA = Forms!FormName!UnboundControl1) OR
(Forms!FormName!UnboundControl1 Is Null))
AND ((fieldB ...

 

[rgrantz]

Okay, nevermind the first part, I think I have that straightened out. I had
my TableName_1.FieldName syntax backwards. I'm still having trouble with
the unbound controls for Start Date and End Date to filter the data table's
date fields to be between those two dates, or ALL for when the start or end
date or both are Null in the unbound controls

Marsh:

Thanks, that did the trick. I have a complicating issue, however. In the
table data that this query pulls from (using the unbound controls on the
Main Form as criteria), there are several different fields for employee
numbers. The employee numbers are related to a table that stores the
numbers and associates them with employee names, so that the query results
show the name instead of the number. Thus, in the query, I have several
different fields called EmpName, but each of them pulls from the same table
(I had to add the same table to the query design for as many entries in the
main data table there are that refer to employee numbers). Thus, there is
EmpName from table EmpList, EmpName from table EmpList_1, EmpName from table
Emplist_2, EmpName from table EmpList_3, etc. (the query builder, when
adding the same table multiple times, names them with the sequential _1, _2,
etc.). So, with each of these employee names being part of the criteria the
user needs to enter on the Main form (ie. all the orders for customer ABC on
10/12/04 that Mark entered and that Jason Shipped and that Thomas inspected,
etc., how do I refer to these EmpName fields in the "Where
(([EmpName]...etc." when they have the same title? I tried the
EmpName.EmpList_1 and EmpName.EmpList_2 syntax as field names, but it didn't
work.

Thanks again, I think I'm almost there.

As I think of it, I will probably run into a similarly confusing situation
when I do the date range criteria too. The user may be entering the Start
Date and End Date as well as all of these other criteria also, but could
also leave them as Null as with all the others. Can the "Between" be used
in this huge string of fieldname values and possible nulls too?

Thanks

I have a form w/ a subform. One the form, I have several unbound controls
that the user inputs data into. These serve as the criteria that
continuously filters the query results shown in the subform. The query has
the form control names in the criteria of the applicable fields (ie. under
customer name, the criteria is [Forms]![FormName]![UnboundControl1]. I have
the "AfterUpdate" event of all the controls set to refresh the form
(me.refresh) so that the results in the subform (whose control source

is

the

date

of


Change each field's criteria to:

WHERE ((fieldA = Forms!FormName!UnboundControl1) OR
(Forms!FormName!UnboundControl1 Is Null))
AND ((fieldB ...