Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    JeffG3209 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Location
    Weatherford, Texas
    Posts
    66

    Filter A Form With Combo Box

    Trying to filter a form named [SEARCH] with a combo box.
    The field in [SEARCH] would be [REP].
    Combo Box values would be "Jeff" and "Paul"

    From what I have found a requery would be my best bet. I am having a hard time creating a requery.



    I am willing to use macros if it is easier that way. I have spent about two days searching the internet and this forum. I am completely lost.

    I imagine it would go something like this...

    FORMS![SEARCH].[REP] xxxxxx

  2. #2
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    Is this what you are trying to accomplish?

    http://www.datapigtechnologies.com/f...earchform.html

    Alan

  3. #3
    JeffG3209 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Location
    Weatherford, Texas
    Posts
    66
    Quote Originally Posted by alansidman View Post
    Is this what you are trying to accomplish?

    http://www.datapigtechnologies.com/f...earchform.html

    Alan
    Alan, I like your videos. Your website is bookmarked on my browser. I have used MANY of your videos to help me with various thing. Let me watch this one again. If I can't figure out a specific thing I will reply again. Thanks!

  4. #4
    JeffG3209 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Location
    Weatherford, Texas
    Posts
    66
    Could you tell me where I am going wrong?
    Code:
    [Forms]![SEARCH].[REP] & "JEFF""PAUL"

  5. #5
    alansidman's Avatar
    alansidman is offline Indifferent
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,536
    If I understand what you are saying, your combo box has two records in it. Jeff and Paul. If this is the case, you would select one of them and in your criteria, replace "Jeff""Paul" with &"*"

    The beginning of the criteria (the like statement) tells Access to look at what you selected on your search page using the combo box as a the source. This also assumes that the combobox is named REP. If it is something else then you need to change it in the Like statement.
    So it would look like this
    Code:
    Like [Forms]![SEARCH].[REP] & "*"
    Alan

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,619
    EDIT: Alan posted above while I was composing so I removed my comment.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    JeffG3209 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Location
    Weatherford, Texas
    Posts
    66
    I chose to us "on change" instead of "on click".
    Code:
    Private Sub qrep1_Change()
    DoCmd.OpenQuery "Rep Filter", acViewNormal
    End Sub
    I changed the Query Criteria to:
    Code:
    Like [Forms]![SEARCH].[REP] & "*"
    If I understand what you are saying, your combo box has two records in it. Jeff and Paul.
    Yes.



    I can't figure out why the combo box is populating with 3 values of "Paul" in Form View. Also, the 4th picture on the right, how do Ihow do I get the query to run on the SEARCH from and not open the query.


    Alan,
    I really appreciate the help more than you know!
    Once I figure out how this works a bit more I will not be so needy

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,619
    Modify the RowSource statement for the combobox to:
    SELECT DISTINCT ...

    Instead of the DoCmd.OpenQuery, just
    Me.Requery
    This should requery the form's RecordSource and refresh the display.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    JeffG3209 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Location
    Weatherford, Texas
    Posts
    66
    Changed that.

    Code:
    Private Sub qrep1_Change()
    Me.Requery "Rep Filter", acViewNormal
    End Sub

    Now it's throwing an syntax error when I run the Query as shown in the screen shot below.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,619
    You said you did not want a query to open. Is this filter criteria part of the RecordSource for a form or report?

    Where is this code?

    Is it behind the form with the records you want filtered? If so, just Me.Requery, no arguments.

    If this code is behind a search form and you want to open another form, the Requery is not needed, just code to open the form.

    If this code is behind a search form and the form to be filtered is already open then instead of Me refer to the form by name. Syntax is: Forms_formname.Requery

    Attach your project to a post and we will take a look at.

    Before running code you should Compile, this should catch syntax errors like the Requery with arguments you tried.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    JeffG3209 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Location
    Weatherford, Texas
    Posts
    66
    I'm sorry. Let me try to explain.

    Filter Criteria is what I am trying to accomplish. I would like to be able to do what Access has in place for filters without the right click.

    The SEARCH form is a continuous form. It is a general summary of the record. I would like to be able to filter the results on the SEARCH form by REP, STATUS, TOTAL ACCOUNT, PRIORITY, TYPE, etc. I figured I would use REP since it is the easiest to explain and the lowest amount of values.

    There shouldn't be any other forms associated with this query.

    I'm not anywhere near good, but I have been trying through trial and error. Many times, If I try long enough I figure things out. When I can't, I turn here. You and Alan have helped me a few times already.

    The easiest way for me to explain what I am trying to do is with this image. This is a different field, but you get the idea

    I would like to be able to filter my SEARCH form by the combo box values that are in a column.

    Thanks again for taking the time to walk me through this.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,619
    Do I understand correctly:

    1. One form in ContinuousView.

    2. The criteria combobox is in Form Header section.

    3. Form has a query as RecordSource and this query has the filter criteria that refers to the combobox.

    4. You are using Change event of the combobox to execute the Me.Requery. I recommend the AfterUpdate event.

    5. Right now you have only one criteria and therefore one combobox. If you want multiple criteria from several comboboxes will need a command button click event instead of combobox event for the Me.Requery.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    JeffG3209 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Location
    Weatherford, Texas
    Posts
    66
    Do I understand correctly:

    1. One form in DatasheetView. Correct

    2. Where is the criteria combobox? DatasheetView doesn't allow display of controls. SEARCH form header. (See image)

    3. Form has a query as RecordSource and this query has the filter criteria that refers to the combobox. Yes

    4. You are using Change event of the combobox to execute the Me.Requery. I recommend the AfterUpdate event. I will change this now

    5. Right now you have only one criteria and therefore one combobox. If you want multiple criteria from several comboboxes will need a command button click event instead of combobox event. True. I will use datapigs video to help me make this.


    If I understand correctly the after update box will take effect after the command button has been clicked.



  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,619
    Ooops. I was editing my post while you were composing. The quote doesn't capture my final edit but I get the info.

    The command button will have no impact on the AfterUpdate event. The AfterUpdate event runs immediately after value in combobox is entered/selected when pressing Enter or Tab or clicking item in list. Use command button click event now if that is where need to eventually go for multiple criteria for the Me.Requery.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    JeffG3209 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2011
    Location
    Weatherford, Texas
    Posts
    66
    How does this sound for a starting point.
    1. Adding the combo boxes and creating the queries
    2. Creating the command button and code behind it.
    3 Trial and error the entire process.
    4. If I can't figure it out post back on here.

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

Similar Threads

  1. Filter form from multiple combo boxes
    By Bird_FAT in forum Programming
    Replies: 6
    Last Post: 05-19-2010, 09:32 AM
  2. Replies: 1
    Last Post: 04-27-2010, 09:30 PM
  3. Filter my form from combo box
    By Angate in forum Forms
    Replies: 3
    Last Post: 04-24-2010, 01:57 PM
  4. filter form based on combo box
    By lloyddobler in forum Forms
    Replies: 8
    Last Post: 09-10-2009, 07:33 AM
  5. Filter Form records with Combo Box????
    By jgelpi in forum Forms
    Replies: 0
    Last Post: 05-19-2009, 07:05 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