Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    pbDudley is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2017
    Location
    New Orleans
    Posts
    39

    Search Form combo box filters

    Hello
    I have a question pertaining to MS Access 2010 and creating a search form. I have a search form that I created. I have 2 combo boxes. If I select a value from one combo box those values are displayed in the table on the form. Then, if I select a value from the 2nd combo box, those values are displayed in the table but resetting it to where the 1st combo box is not being used
    I wanted to know how I can have both of the combo boxes working together. I can post the code that I have if this helps.
    Im not much of a programmer but I can usually figure out how to manipulate code from tutorials but this one is confusing me. Is there a command that will make the 2 work together? I see that I have both ending with: Me.Qry_RAU_PLAN_TOTALS_subform.Form.Requery, so perhaps the Requery is resetting the value from the 1st combo box
    Any help is much appreciative



    ption Compare Database
    '--------------------------------------------------------------------------------------------------------------------
    'The code below allows one to chose a value from a combo box and only have those values displayed in the query/table
    '--------------------------------------------------------------------------------------------------------------------
    Private Sub cboPlanType_AfterUpdate()
    Dim myPlanType As String
    myPlanType = "Select * From Qry_RAU_PLAN_TOTALS where ([PLAN_TYPE_CODE] ='" & Me.CboPLANTYPE & "')"
    Me.Qry_RAU_PLAN_TOTALS_subform.Form.RecordSource = myPlanType
    Me.Qry_RAU_PLAN_TOTALS_subform.Form.Requery
    End Sub
    'The code below is set up for a combobox, one would chose a review name, and it will display those reviews
    '--------------------------------------------------------------------------------------------------------------------
    Private Sub cboReviewType_AfterUpdate()
    Dim myReviewType As String
    myReviewType = "Select * From Qry_RAU_PLAN_TOTALS where ([REVIEW_NAME] ='" & Me.cboReviewType & "')"
    Me.Qry_RAU_PLAN_TOTALS_subform.Form.RecordSource = myReviewType
    Me.Qry_RAU_PLAN_TOTALS_subform.Form.Requery
    End Sub

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure what you're trying to do. If you alter the form record source, for sure you're going to display different records (naturally assumes the sources don't contain the exact same records). Are you trying to display a sub set of records based on combo 2? If so, apply a filter based on the second combo, or if you prefer, include the second combo value as additional criteria in the WHERE clause and reset the form record source. The former method would be more efficient.

    Then there is the dependent combo box situation where the results of #2 depend on the value chosen in #1, but I don't think that's what you're asking for.
    Last edited by Micron; 03-13-2017 at 10:58 AM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    pbDudley is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2017
    Location
    New Orleans
    Posts
    39
    Yes,
    Its a split form and the top i have the combo boxes and the bottom is a table. 1 combo box i want to chose a value, display those in the table, then the 2nd combo box, pick a value and sort the data based off of both combo boxes. Perhaps its more complex then what Im showing here, or rather complex for me. But i can try the where clause to see. I followed a youtube video with this one. But one combox box, I want to pick a value, the table sorts the data with that value, then the 2nd combo box sorts that data again.

  4. #4
    pbDudley is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2017
    Location
    New Orleans
    Posts
    39
    I did find this that I think will work. The 1st combo box i have 3 values to chose the 2nd combo is 2. I see this command that i can use:

    =FindAsUTypeLoad([Form], "Combo1", "Text99")

    I think I can make it work with this where the find as you type instead of my 2nd combo box one would just type one or the other of the choices. I dont have much computer programming skills but i can usually piece what I need together based off of smarter programmers



  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    As long as we're talking SORTING and not CRITERIA, then the WHERE clause has no significance. You will have to apply a sort using code since it's going to be volatile.
    You could concatenate a sort order clause from variables or just form controls. I could assist, but I don't know the exact method of operation you need. My guess is that:
    - if combo1 and 2 are null, there is no sort other than what is returned to the form
    - if combo1 has a value and combo2 does not, sort by combo1, but which way?
    - if combo1 and combo2 have values, sort by 1 then by 2, but which way for either of them?
    - if combo1 is null and combo2 isn't, then what? Or is that not possible because you have designed so that 2 is blank if nothing is chosen in 1?
    Last edited by Micron; 03-13-2017 at 02:24 PM. Reason: clarification

  6. #6
    pbDudley is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2017
    Location
    New Orleans
    Posts
    39
    Ok, I see, it’s more complex than I thought.
    I’ll see if I can clarify the process more

    -if combo1 and 2 are null, there is no sort other than what is returned to the form: The combo box will never return a null value.

    -if combo1 has a value and combo2 does not, sort by combo1, but which way? If combo1 has a value than combo2 has to.

    -if combo1 and combo2 have values, sort by 1 then by 2, but which way for either of them?:

    If combo1 and combo2 both have values, they always will. Combo1 is choosing a plan type, 3 choices. Once I choose a value for combo1 it only displays those plans, but each plan has a geological and an engineering review. Combo2 allows one to choose either geological or engineering, then sorts all of the plans by that.

    -if combo1 is null and combo2 isn't, then what? Or is that not possible because you have designed so that 2 is blank if nothing is chosen in 1?:
    Yes it isn’t possible to have null data
    Its more complex than i thought, I'm not really sure how to set that up. Right now I only have it set up by adding an event procedure to combo1 and an event procedure to combo 2(as the code above) they arent working insync. I can research more on it and see how i can get the 2 combos to work together,

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It's not complex, really. It's just that what you want is very fuzzy. You've cleared up some of the conditions involved, but not exactly what you need to do. You didn't answer how the sort is to be done as asked (ascending or descending) but more importantly, I still don't know if we're sorting or requerying (or both).

    Sounds like you need a button to run the code since update events on either combo would do something but likely at the wrong time since one or the other hasn't had a selection made at first. Since the combos will never be null, you must have bound them to something. That doesn't sound correct for a search form, for any changes to their selection will alter the value in the underlying record displayed on the form - unless you have designed so that the form loads unbound combos with some default value - again, not real practical for a search form.

    You will have to describe the process in specific detail for me to help further, starting from what happens when the search form opens. After several posts, I still don't know what you're trying to do: filter on combo1 + combo2 choices; requery on 1+2 choices; or load records into an empty form based on 1+2 choices.

  8. #8
    pbDudley is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2017
    Location
    New Orleans
    Posts
    39
    Ok
    My work has tables in an oracle database that I can link to via Access and create my own custom queries. I created a query based off of 2 of the tables. Then I created a split form based off of the query results. The query results are types of plans that I work with, I can call them Plan A and Plan B. The first combo box lets me choose this value. If I choose Plan A, the table on the form gives me all Plan A records. Each Plan A record has a Geologic Review and a Engineering Review. Now, the 2nd combo box I would want to choose the review type. If I choose Plan A, then Geologic Review, only those records.
    As far as sorting or re-querying, I'm not sure, I thought re-query but I guess Its just taking the query results and sorting by all plan A's, then resorting by review type?
    Im really just following this tutorial but I'm not getting the same results

    https://www.youtube.com/watch?v=choPri7y_o4

    The instructor appears to be choosing his 1st combo box results then choosing combo box2 to resort or requery the results
    The code that I posted earlier is really just this you tube tutorials code changed to what I need.
    I would post a sample database but I dont think I can upload that here
    Im sure there is a lot simpler way of doing it, I know I could add the criteria to the query for these options, but the database is used by many and it feels safer to have it in the VB and less likely to be modified or deleted by a user.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I watched 4 minutes of that. Based on that short view, they are requerying, not filtering. If you replicate this approach, then when you click Plan A you will get records related to Plan A. If you then click the combo for Review Type and choose Geologic, you will get records related to Geologic type regardless of what plan they are. Where I stopped watching was the point were they were going to clear out the other two combos when you select a value from one, which means that only one combo was going to be used to return records. Your first post reads that you want to utilize both. So there are basically 2 ways to approach your search form:
    1) choose one value from one combo and load records related to that value. Choose a value from the other combo and load records related to that value regardless of their relationship to the first combo value. The first value may as well be deleted because it has no bearing on the second set of records.
    2) choose one value (e.g. PlanA) and load all records related to that value only if the second combo has no selection. If the second combo has a chosen value as well, the records would be where the PlanType is PlanA AND the Review Type matches the chosen value**. If there is no plan type chosen but "Geologic" is chosen, load all records where the review type matches regardless of the plan type.

    I presume you want the second type of functionality. However, what I described is much more robust than saying "No, I don't want to be able to search for review type if no plan is chosen." so please consider the depth of complexity you need.
    EDIT: this will result in no records being shown if the combination chosen does not exist

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Hmmm. Maybe not as complex as I thought. Took some time to come up with the following (I really need to get off this couch!):
    Code:
    Private Sub cboPlanType_AfterUpdate()
    Dim sql As String
    Dim strWhere as String
    
    strWhere = ""
    sql = "Select * From Qry_RAU_PLAN_TOTALS WHERE "
    If IsNull(Me.cboPlanType) AND IsNull(Me.cboPlanType) Then
      msgBox "A Plan Type OR Review Type must be selected."
      Exit Sub
    End if
    
    If IsNull(Me.cboPlanType) Then
      strWhere = "([REVIEW_NAME] ='" & Me.cboReviewType & "')"
    Else
      strWhere = ([PLAN_TYPE_CODE] ='" & Me.CboPLANTYPE & "') AND ([REVIEW_NAME] ='" & Me.cboReviewType & "')"
    End If
    
    With Me.Qry_RAU_PLAN_TOTALS_subform.Form
      .Recordsource = sql & strWhere
      .Requery
    End With
    
    End Sub
    
    Private Sub cboReviewType_AfterUpdate()
    Dim sql As String
    Dim strWhere as String
    
    strWhere = ""
    sql = "Select * From Qry_RAU_PLAN_TOTALS WHERE "
    If IsNull(Me.cboPlanType) AND IsNull(Me.cboPlanType) Then
      msgBox "A Plan Type OR Review Type must be selected."
      Exit Sub
    End if
    
    If IsNull(Me.cboPlanType) Then
      strWhere = "([REVIEW_NAME] ='" & Me.cboReviewType & "')"
    Else
      strWhere = ([PLAN_TYPE_CODE] ='" & Me.CboPLANTYPE & "') AND ([REVIEW_NAME] ='" & Me.cboReviewType & "')"
    End If
    
    With Me.Qry_RAU_PLAN_TOTALS_subform.Form
      .Recordsource = sql & strWhere
      .Requery
    End With
    You can try it (no guarantees as it's air code) and see what happens. If it's not perfect, perhaps you can tweak it. I didn't include notes as I figured it was pretty evident as to what's going on. Hope I didn't assume too much about that. The message box part is probably more suited to a situation where the code is run from a command button, but I figured it would be possible to update the controls by deleting and then moving off of them. Remove/alter as you see fit.
    Last edited by Micron; 03-14-2017 at 07:40 AM. Reason: added info

  11. #11
    pbDudley is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2017
    Location
    New Orleans
    Posts
    39
    Ok thank you. i kind of see how your doing it.
    I keep getting a syntax error in each of these"
    strWhere = ([PLAN_TYPE_CODE] ='" & Me.CboPLANTYPE & "') AND ([REVIEW_NAME] ='" & Me.cboReviewType & "')"

    I dont see anything wrong with it,

  12. #12
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In each event, look at the first strWhere and compare to the second strWhere and see my typo and the resulting copy/paste error. It's a small error, but enough to cause it to balk.

  13. #13
    pbDudley is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2017
    Location
    New Orleans
    Posts
    39
    Ok, Im not sure
    I see that we have cboPlanType set but in the strWhere we have it as CboPLANTYPE, I changed this and it still has an errror, I tried removing some spaces and ' " to '" but I dont see the error, im not that profficent at this, Im learning a lot but I would still have a hard time writing it without examples in front of me, Ill check some more

  14. #14
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Good that you tried. It is this
    strWhere = "([PLAN_TYPE_CODE]
    Missing double quote at the beginning of the statements - and an End Sub. Sorry, I'm not usually this sloppy (or I like to think I'm not), but in my own defense I can't compile code for controls and forms that I don't have, so it's all from Notepad. Maybe this is how it needs to be
    Code:
    Private Sub cboPlanType_AfterUpdate()
    Dim sql As String
    Dim strWhere as String
    
    strWhere = ""
    sql = "Select * From Qry_RAU_PLAN_TOTALS WHERE "
    If IsNull(Me.cboPlanType) AND IsNull(Me.cboReviewType) Then
      msgBox "A Plan Type OR Review Type must be selected."
      Exit Sub
    End if
    
    If IsNull(Me.cboPlanType) Then
      strWhere = "([REVIEW_NAME] ='" & Me.cboReviewType & "')"
    Else
      strWhere = "([PLAN_TYPE_CODE] ='" & Me.CboPLANTYPE & "') AND ([REVIEW_NAME] ='" & Me.cboReviewType & "')"
    End If
    
    With Me.Qry_RAU_PLAN_TOTALS_subform.Form
      .Recordsource = sql & strWhere
      .Requery
    End With
    
    End Sub
    
    Private Sub cboReviewType_AfterUpdate()
    Dim sql As String
    Dim strWhere as String
    
    strWhere = ""
    sql = "Select * From Qry_RAU_PLAN_TOTALS WHERE "
    If IsNull(Me.cboPlanType) AND IsNull(Me.cboReviewType) Then
      msgBox "A Plan Type OR Review Type must be selected."
      Exit Sub
    End if
    
    If IsNull(Me.cboPlanType) Then
      strWhere = "([REVIEW_NAME] ='" & Me.cboReviewType & "')"
    Else
      strWhere = "([PLAN_TYPE_CODE] ='" & Me.CboPLANTYPE & "') AND ([REVIEW_NAME] ='" & Me.cboReviewType & "')"
    End If
    
    With Me.Qry_RAU_PLAN_TOTALS_subform.Form
      .Recordsource = sql & strWhere
      .Requery
    End With
    
    End Sub
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    pbDudley is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2017
    Location
    New Orleans
    Posts
    39
    Alright
    That just solved the issue. I was trying to use an online syntax error website, it didn't show me the error.
    Thank you very much. Im going to go over some more to make sure I can understand whats going on, but this helped a lot

    thank you very much Micron in Ontario!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. A search box that filters through multiple fields
    By WesHarding in forum Queries
    Replies: 4
    Last Post: 02-10-2017, 03:33 PM
  2. Replies: 8
    Last Post: 04-02-2016, 04:56 AM
  3. Replies: 7
    Last Post: 08-08-2012, 03:28 PM
  4. Search Allowing greater specification of filters.
    By DatabaseIntern in forum Programming
    Replies: 6
    Last Post: 07-02-2012, 10:32 AM
  5. Replies: 1
    Last Post: 04-20-2012, 03:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums