If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
use query Criteria from combo box to set subform Filter
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. |
#2
|
|||
|
|||
use query Criteria from combo box to set subform Filter
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "David" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|