Then i have created subform "sfrmfilteSearch" and link that to "frmSearch". This form "frmSearch" allows user to select the value from each combo box and based on that "sfrmfilterSearch" will display all the data based on combo box value. Each and every combo box are filter by each other (This part wrks fine).
Now the problem is that each time i select value from "CboStudy" the subfrom doesn't get filter it until i select the value from "cboSection"
In that sample db i also have reports called "rptTest" which has following information:
2nd Sub Folder
And then Contact, Date on Doc, Date Filed...etc
Now the problem is that not all "Hanging Folder" will have Sub Folder and not all Sub Folder will have "2nd Sub Folder" And when there is no SubFolder or 2ndSubFolder i would still like to see Contact...Date on Doc...Date Filed...etc in my reports.
Is there a way for me to hide "SubFolder" field and 2ndSubFolder" field when they don't exist ? Or is there any better solution to take care of this ?
Ok now i have created report based on "QrtFilter and use the same code that you have suggested to filter out the report based on combo box selection here is the code:
Private Function RequerySubformTest()
strSQL = "SELECT * FROM QryFilter"
If Not IsNull(Me.cboStudy) Then
strSQL = strSQL & " Where StudyNo = '" & Me.cboStudy.Value & "'"
If Not IsNull(Me.cboStudySection) Then
strSQL = strSQL & " WHERE StudySection = '" & Me.cboStudySection.Column(1) & "'"
If Not IsNull(Me.cboHangingFolder) Then
strSQL = strSQL & " AND HangingFolder = '" & Me.cboHangingFolder.Column(1) & "'"
If Not IsNull(Me.cboSubFolder) Then
strSQL = strSQL & " AND SubFolder = '" & Me.cboSubFolder.Column(1) & "'"
If Not IsNull(Me.cboSecSub) Then
strSQL = strSQL & " AND SecSub = '" & Me.cboSecSub.Column(1) & "'"
Me.QryFilterSearchData.Form.RecordSource = strSQL
How comes this code doesn't wrk for the filtering out of reports but this code does works when i'm filtering out everything on subform.
As far as you code, looks like you'll need to change all the "Me.xxxxx" to:
Ok i change the code the way you have suggested but for some reason it still does not work for me. I have also attached the new db with this code.
Maybe if you have time you can look into this new db and tell me what's wrong.