Results 1 to 10 of 10

Thread: Search Form

  1. #1
    Join Date
    May 2004
    Posts
    63

    Unhappy Unanswered: Search Form

    I have created "frmSearch" with following Combo box.

    CboStudy
    CboSection
    CboHangingFolder
    CboSubfolder
    Cbo2ndSubfolder

    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"

    Any idea what could be wrong here ?

    I'm attaching the sample of db.

    Thanks In Advance
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313

    Here you go...

    I uncommented 1 line, but your main problem was an extra space in the SQL you were generating.
    I left a comment in the code where the space was.

    HTH,

    Chris
    Attached Files Attached Files

  3. #3
    Join Date
    May 2004
    Posts
    63
    cpgospi,

    Thanks for your response.

    I have one more question.

    In that sample db i also have reports called "rptTest" which has following information:

    StudyNo
    StudySection
    Hanging Folder
    Sub Folder
    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 ?

    Thanks in advance

  4. #4
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313

    Sure...

    Quote Originally Posted by SAK2004
    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 ?


    Looks like you have included a dash character, so you could replace the existing text box with:

    =IIF ([Subfolder] = "-", "", [Subfolder])

    And if you want to get rid of the the "Subfolder" label. You could also replace that with a text box:

    = IIF([Subfolder] = "-", "", "Subfolder")

    Same idea would work with 2ndSubfolder

    There's a few ways to do this but I believe this would work for you.

  5. #5
    Join Date
    May 2004
    Posts
    63
    cpgospi,

    I try using your code with the text box but for some reason its not working well for me. Is it possible that you can post the example ? I have also post the sample db in previous post.

    Thanks In Advance

  6. #6
    Join Date
    May 2004
    Posts
    63
    cpgospi,

    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:

    Code:
    Private Function RequerySubformTest()
      strSQL = "SELECT * FROM QryFilter"
    
     
      If Not IsNull(Me.cboStudy) Then
        strSQL = strSQL & " Where StudyNo = '" & Me.cboStudy.Value & "'"
      Else
        GoTo ExitProcess
      End If
      
      If Not IsNull(Me.cboStudySection) Then
        strSQL = strSQL & " WHERE StudySection = '" & Me.cboStudySection.Column(1) & "'"
      Else
        GoTo ExitProcess
      End If
      
      If Not IsNull(Me.cboHangingFolder) Then
        strSQL = strSQL & " AND HangingFolder = '" & Me.cboHangingFolder.Column(1) & "'"
      Else
        GoTo ExitProcess
      End If
    
      If Not IsNull(Me.cboSubFolder) Then
        strSQL = strSQL & " AND SubFolder = '" & Me.cboSubFolder.Column(1) & "'"
      Else
        GoTo ExitProcess
      End If
    
      If Not IsNull(Me.cboSecSub) Then
        strSQL = strSQL & " AND SecSub = '" & Me.cboSecSub.Column(1) & "'"
      Else
        GoTo ExitProcess
      End If
    
    ExitProcess:
      Me.QryFilterSearchData.Form.RecordSource = strSQL
    End Function
    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.

    Any Idea what's wrong ?

  7. #7
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    I'm attaching your db with the text box for subfolders working...a little pressed for time today, but I think you can see what I've done there.

    As far as you code, looks like you'll need to change all the "Me.xxxxx" to:

    "Forms!frmSearch!cboxxxxx.xxxxxx"

    You just need to explicitly name the controls since they exist on the form, not the report.
    Attached Files Attached Files

  8. #8
    Join Date
    May 2004
    Posts
    63
    cpgospi,

    One more question.

    You code works fine and it will work for me. But is it possible to display "No SubFoder" in text box when there is no subfolder ?

    Thanks

  9. #9
    Join Date
    May 2004
    Posts
    63
    cpgospi,

    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.

    Thanks

  10. #10
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Quote Originally Posted by SAK2004
    Ok i change the code the way you have suggested but for some reason it still does not work for me.
    Are you getting any type of error? It works ok for me, but I only tested a couple lines of the code (should work all the way through though)

    Quote Originally Posted by SAK2004
    But is it possible to display "No SubFoder" in text box when there is no subfolder ?
    Yep, I replaced your text box with:
    =IIf([Subfolder]="-", "", [Subfolder])

    You can just change that to:
    =IIf([Subfolder]="-", "No SubFolder", [Subfolder])

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •