use query Criteria from combo box to set subform Filter

[David]

Hello all,
This is somewhat long, but please hang in there. I wasn't sure how much
detail to include.

I am trying to use a combo box column1 on the main form to filter the
recordset of the subform. I have about 10 comboboxes and I am finding that
trying to set the subform filter is difficult with so many conditions
possible.
So, I am trying to use the combo box column1 as the criteria for the subform
query, and just requery the subform on the after update event of the combo
box.

Kicker is that I have (all) and (blank) in the combo boxes column 0. I
can't figure out how to work the criteria to account for these two conditions
* and " " in column 1.

Here are the several attempts I have used in the criteria for the field
Status2. I have this in the status2 field. Attempt 1 is the closest to what
I want.

Attempt1
Like Eval("Forms!frm_TC_by_Release!cbo_Status.Column(1)") & "*"

When Blank is selected, it shows All records. I want it to only show
records with null in Status field.

Attempt2
IIf(IsNull([Status])," ",Like
Eval("Forms!frm_TC_by_Release!cbo_Status.Column(1)") & "*")

returns records with status - null, regardless of the value in
Forms!frm_TC_by_Release!cbo_Status.Column(1)

Here are the several attempts I have used in the criteria for the field
Status. I have this in the status field

Attempt3
Like Eval("Forms!frm_TC_by_Release!cbo_Status.Column(1)") & "*"

Does not records when All or blank is selected from the combo box.

Attempt4
IIf(IsNull([Status])," ",Like
Eval("Forms!frm_TC_by_Release!cbo_Status.Column(1)") & "*")

no records returned

Here is the SQL for the query (left join)
SELECT tbl_temp_TC.*, IIf(IsNull([Status])," ",[Status]) AS Status2,
tbl_TC_Modify_History.Modify_By, tbl_TC_Modify_History.SR,
tbl_TC_Modify_History.Status
FROM tbl_temp_TC LEFT JOIN tbl_TC_Modify_History ON tbl_temp_TC.Link =
tbl_TC_Modify_History.Link
WHERE (((tbl_TC_Modify_History.Status) Is Null Or
(tbl_TC_Modify_History.Status) Like
Eval("Forms!frm_TC_by_Release!cbo_Status.Column(1)") & "*"))
ORDER BY tbl_temp_TC.TS_NAME;



Forms!frm_TC_by_Release!cbo_Status bound column is column 1, but I don't use
this value in a table. Just in VBA code and this query. I reference it as
Forms!frm_TC_by_Release!cbo_Status.Column(0) or
Forms!frm_TC_by_Release!cbo_Status.Column(1)

Here is the value list for Forms!frm_TC_by_Release!cbo_Status
(All);"*";(Blank);"";PRB Scheduled;PRB Scheduled;Checked Out Modify;Checked
Out Modify;Checked In;Checked In;Checked Out Execution;Checked Out Execution



Thank you very much for reading this. I appreciate any suggestions.

 

[Allen Browne]

David, I didn't go through all that in detail, but it seems you are trying
to build a filter that handles special cases for "(all)", i.e. no filter,
and "(blank)", i.e. the filter will end up as "[SomeField] Is Null"

It might be easiest to use some code to create the filter string. The code
might go in the AfterUpdate event procedure of the combo, or in the Click
event of a button if you prefer. This kind of thing:
Dim strWhere As String
With Me.cbo_Status
Select Case .Value
Case "(blank)"
strWhere = "[Field1] Is Null"
Case "(all)"
'do nothing
Case Else
If Len(.Value) > 0 Then 'Ignore nulls
strWhere = "[Field1 = """ & .Value & """"
End If
End Select
End With
If strWhere <> vbNullString Then
Me.Filter = strWhere
Me.FilterOn = True
Else
Me.FilterOn = False
End if

If you have multiple controls you are trying to handle like this, this might
help:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello all,
This is somewhat long, but please hang in there. I wasn't sure how much
detail to include.

I am trying to use a combo box column1 on the main form to filter the
recordset of the subform. I have about 10 comboboxes and I am finding
that
trying to set the subform filter is difficult with so many conditions
possible.
So, I am trying to use the combo box column1 as the criteria for the
subform
query, and just requery the subform on the after update event of the combo
box.

Kicker is that I have (all) and (blank) in the combo boxes column 0. I
can't figure out how to work the criteria to account for these two
conditions
* and " " in column 1.

Here are the several attempts I have used in the criteria for the field
Status2. I have this in the status2 field. Attempt 1 is the closest to
what
I want.

Attempt1
Like Eval("Forms!frm_TC_by_Release!cbo_Status.Column(1)") & "*"

When Blank is selected, it shows All records. I want it to only show
records with null in Status field.

Attempt2
IIf(IsNull([Status])," ",Like
Eval("Forms!frm_TC_by_Release!cbo_Status.Column(1)") & "*")

returns records with status - null, regardless of the value in
Forms!frm_TC_by_Release!cbo_Status.Column(1)

Here are the several attempts I have used in the criteria for the field
Status. I have this in the status field

Attempt3
Like Eval("Forms!frm_TC_by_Release!cbo_Status.Column(1)") & "*"

Does not records when All or blank is selected from the combo box.

Attempt4
IIf(IsNull([Status])," ",Like
Eval("Forms!frm_TC_by_Release!cbo_Status.Column(1)") & "*")

no records returned

Here is the SQL for the query (left join)
SELECT tbl_temp_TC.*, IIf(IsNull([Status])," ",[Status]) AS Status2,
tbl_TC_Modify_History.Modify_By, tbl_TC_Modify_History.SR,
tbl_TC_Modify_History.Status
FROM tbl_temp_TC LEFT JOIN tbl_TC_Modify_History ON tbl_temp_TC.Link =
tbl_TC_Modify_History.Link
WHERE (((tbl_TC_Modify_History.Status) Is Null Or
(tbl_TC_Modify_History.Status) Like
Eval("Forms!frm_TC_by_Release!cbo_Status.Column(1)") & "*"))
ORDER BY tbl_temp_TC.TS_NAME;



Forms!frm_TC_by_Release!cbo_Status bound column is column 1, but I don't
use
this value in a table. Just in VBA code and this query. I reference it
as
Forms!frm_TC_by_Release!cbo_Status.Column(0) or
Forms!frm_TC_by_Release!cbo_Status.Column(1)

Here is the value list for Forms!frm_TC_by_Release!cbo_Status
(All);"*";(Blank);"";PRB Scheduled;PRB Scheduled;Checked Out
Modify;Checked
Out Modify;Checked In;Checked In;Checked Out Execution;Checked Out
Execution



Thank you very much for reading this. I appreciate any suggestions.