Combo box for continuous form filter

[mattkorguk]

Hi all,
I have created a form with a sub continuous form and a couple of filters at the bottom, a date one and a drop down box so the user can select which date field the filter is looking at. Is this actually possible?! The query behind the subform is built when the form is opened and recreated when the 'Apply filter' button is pressed. The issue I'm having is actually refreshing the subform to adjust the user selecting a different dropdown.

'SetSecurity' is a module to restrict available buttons.
The SendKeys was me testing ways to refresh the form!
'Me.frmEvents_List.[Form].TargetDate.ControlSource = Me.selDate' this is where I'm adjusting the field displayed to reflect the option selected from the dropdown list.
I have thought about recreating the entire subform, one for each dropdown option, and just replacing the subform each time, but thought my current way would be 'smarter'?!?!


Code behind form:

Private Sub Form_Open(Cancel As Integer)
SetSecurity
 Dim fDate As String
Dim qdf As DAO.QueryDef
Dim db As Database
Dim Fcr As Recordset
Set db = CurrentDb()
fDate = Me.selDate
Me.frmEvents_List.Visible = False
Me.frmEvents_List.[Form].TargetDate.ControlSource = Me.selDate
    
    fSQL = "SELECT tBreachLog.BId AS ID, tUsers.[Login ID], tBreachLog.NINO AS NI, tBreachLog.EventId AS eID, tBreachLog.CompletedDate AS Completed, tBreachLog." & fDate & ", tBreachLog.CreationDate, tBreachLog.Outcome, tEvents.[Event Description] " _
        & "FROM (tUsers LEFT JOIN tBreachLog ON tUsers.NINO = tBreachLog.NINO) LEFT JOIN tEvents ON tBreachLog.EventId = tEvents.EventId " _
        & "WHERE (((tBreachLog.EventId) Like [Forms]![frmEvents-Master]![SelEvent]) AND ((tBreachLog." & fDate & ") Between [Forms]![frmEvents-Master]![sStart] And [Forms]![frmEvents-Master]![sEndDate]) AND ((tUsers.NINO)=[Forms]![frmEvents-Master]![NI]) AND ((IIf([CompletedDate] Is Null,1,0)) Like (IIf([forms]![frmEvents-Master]![selTerm]='No',1,'*')))) " _
        & "UNION SELECT tComplaints.CId AS ID, tUsers.[Login ID], tComplaints.NINO AS NI, tComplaints.EventId AS eID, tComplaints.CompletedDate AS Completed,  tComplaints." & fDate & ", tComplaints.CreationDate, tComplaints.Outcome, tEvents.[Event Description] " _
        & "FROM tUsers LEFT JOIN (tEvents RIGHT JOIN tComplaints ON tEvents.EventId = tComplaints.EventId) ON tUsers.NINO = tComplaints.NINO " _
        & "WHERE (((tComplaints.EventId) Like [Forms]![frmEvents-Master]![SelEvent]) AND ((tComplaints." & fDate & ") Between [Forms]![frmEvents-Master]![sStart] And [Forms]![frmEvents-Master]![sEndDate]) AND ((tUsers.NINO)=[Forms]![frmEvents-Master]![NI]) AND ((IIf([CompletedDate] Is Null,1,0)) Like (IIf([forms]![frmEvents-Master]![selTerm]='No',1,'*')))) " _
        & "UNION SELECT tComplaintsSRC.SRCId AS ID, tUsers.[Login ID], tComplaintsSRC.NINO AS NI, tComplaintsSRC.EventId AS eID, tComplaintsSRC.CompletedDate AS Completed, tComplaintsSRC." & fDate & ", tComplaintsSRC.CreationDate, tComplaintsSRC.Outcome, tEvents.[Event Description] " _
        & "FROM tUsers LEFT JOIN (tEvents RIGHT JOIN tComplaintsSRC ON tEvents.EventId = tComplaintsSRC.EventId) ON tUsers.NINO = tComplaintsSRC.NINO " _
        & "WHERE (((tComplaintsSRC.EventId) Like [Forms]![frmEvents-Master]![SelEvent]) AND ((tComplaintsSRC." & fDate & ") Between [Forms]![frmEvents-Master]![sStart] And [Forms]![frmEvents-Master]![sEndDate]) AND ((tUsers.NINO)=[Forms]![frmEvents-Master]![NI]) AND ((IIf([CompletedDate] Is Null,1,0)) Like (IIf([forms]![frmEvents-Master]![selTerm]='No',1,'*')))) " _
        & "UNION SELECT tCPD.CPDId AS ID, tUsers.[Login ID], tCPD.NINO AS NI, tCPD.EventId AS eID, tCPD.CompletedDate AS Completed, tCPD." & fDate & ", tCPD.CreationDate, tCPD.Outcome, tEvents.[Event Description] " _
        & "FROM tUsers LEFT JOIN (tCPD LEFT JOIN tEvents ON tCPD.EventId = tEvents.EventId) ON tUsers.NINO = tCPD.NINO " _
        & "WHERE (((tCPD.EventId) Like [Forms]![frmEvents-Master]![SelEvent]) AND ((tCPD." & fDate & ") Between [Forms]![frmEvents-Master]![sStart] And [Forms]![frmEvents-Master]![sEndDate]) AND ((tUsers.NINO)=[Forms]![frmEvents-Master]![NI]) AND ((IIf([CompletedDate] Is Null,1,0)) Like (IIf([forms]![frmEvents-Master]![selTerm]='No',1,'*')))) " _
        & "UNION SELECT tObs.ObsId AS ID, tUsers.[Login ID], tObs.NINO AS NI, tObs.EventId AS eID, tObs.CompletedDate AS Completed, tObs." & fDate & ", tObs.CreationDate, tObs.Outcome, tEvents.[Event Description] " _
        & "FROM tUsers LEFT JOIN (tEvents RIGHT JOIN tObs ON tEvents.EventId = tObs.EventId) ON tUsers.NINO = tObs.NINO " _
        & "WHERE (((tObs.EventId) Like [Forms]![frmEvents-Master]![SelEvent]) AND ((tObs." & fDate & ") Between [Forms]![frmEvents-Master]![sStart] And [Forms]![frmEvents-Master]![sEndDate]) AND ((tUsers.NINO)=[Forms]![frmEvents-Master]![NI]) AND ((IIf([CompletedDate] Is Null,1,0)) Like (IIf([forms]![frmEvents-Master]![selTerm]='No',1,'*')))) " _
        & "UNION SELECT tDnN.DnNId AS ID, tUsers.[Login ID], tDnN.NINO AS NI, tDnN.EventId AS eID, tDnN.CompletedDate AS Completed, tDnN." & fDate & ", tDnN.CreationDate, tDnN.Outcome, tEvents.[Event Description] " _
        & "FROM tUsers LEFT JOIN (tEvents RIGHT JOIN tDnN ON tEvents.EventId = tDnN.EventId) ON tUsers.NINO = tDnN.NINO " _
        & "WHERE (((tDnN.EventId) Like [Forms]![frmEvents-Master]![SelEvent]) AND ((tDnN." & fDate & ") Between [Forms]![frmEvents-Master]![sStart] And [Forms]![frmEvents-Master]![sEndDate]) AND ((tUsers.NINO)=[Forms]![frmEvents-Master]![NI]) AND ((IIf([CompletedDate] Is Null,1,0)) Like (IIf([forms]![frmEvents-Master]![selTerm]='No',1,'*'))));"
 On Error Resume Next
DoCmd.DeleteObject acQuery, "qryEvents-Listu"
db.CreateQueryDef "qryEvents-Listu", fSQL
Me.frmEvents_List.[Form].TargetDate.ControlSource = Me.selDate
Me.frmEvents_List.Visible = True
Me.fDate.Caption = Me.selDate
    SendKeys ("{F5}")

 

[Ranman256]

The form query shows all records.
When user enters data in a filter control, check all controls,the set the filter....

Sub CboBox_afterupdate()
  FilterForm()
End sub

Sub TxtBox_afterupdate()
   FilterForm()
End sub

Sub FilterForm()
If not IsNull(cboBox) then sWhere =  " and [field1]='" & cboBox & "'"
If not IsNull(txtBox) then sWhere = sWhere &  " and [field2]='" & txtBox & "'"
      '
If sWhere="" then
   Me.filterOn=false
Else
     'Remove 1st AND.
   SWhere = mid(sWhere,5)
  Me.filter=sWhere
  Me.filterOn=true
End if
End sub

 

[mattkorguk]

@RanMan256 - Ahh, thanks for that, I shall change things around and give this a try. Sometimes I can't see another way through! :banghead: