Results 1 to 10 of 10
  1. #1
    AJJJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    12

    Can't Get A ComboBox To Filter a Query

    Hi. This is the first time I've posted here. I'm having a problem doing something I've done multiple times before, and can't get to work in this situation. I have no Idea why. I have pared things down to the basics for simplicity.



    I have a database that contains a list of Stocks e.g. IBM, Apple etc. Each stock has a Category that it is assigned to. I have a table -tblInvAAMainCats- That contains the category name in one field and an ID number in an autonumber field. I have another table -tblEquity- that contains fields for Equity Name, ID, and Category ID.

    I have a continuous form with two columns: Name and Category. I have, in the Form Header, a combo box that populates itself from the table -tblMainCats and contains a list of Categories and their ID Number (AACatID). I want to use that ComboBox to filter the form to show only stocks in the category chosen are displayed. To do that I have always put a reference to the combobox as a Criteria for the forms RecordSource. This has always worked for filtering forms and cascading queries, but this time I get nothing.

    >This is the SQL for the ComboBox rowsource: (RowSource Type is Table/Query and Bound Column is 1, No Control source)

    SELECT tblInvAAMainCats.AACatID, tblInvAAMainCats.MainCat
    FROM tblInvAAMainCats;


    >This is the SQL for the Form RecordSource when I put a hard value as criteria in the HAVING line--in this case 1. It works fine and returns only rows where The autonumber ID field (AACatID) in tblAAInvMainCats is 1

    SELECT tblEquity.Company, tblInvAAMainCats.MainCat, tblInvAAMainCats.AACatID
    FROM tblInvAAMainCats INNER JOIN tblEquity ON tblInvAAMainCats.AACatID = tblEquity.MainAAID
    GROUP BY tblEquity.Company, tblInvAAMainCats.MainCat, tblInvAAMainCats.AACatID
    HAVING (((tblInvAAMainCats.AACatID)=1));



    >This is the same SQL with a reference to the ComboBox -cboMainCats. It does not work.

    SELECT tblEquity.Company, tblInvAAMainCats.MainCat, tblInvAAMainCats.AACatID
    FROM tblInvAAMainCats INNER JOIN tblEquity ON tblInvAAMainCats.AACatID = tblEquity.MainAAID
    GROUP BY tblEquity.Company, tblInvAAMainCats.MainCat, tblInvAAMainCats.AACatID
    HAVING (((tblInvAAMainCats.AACatID)=[Forms]![frmInvEquity]![cboMainCat]));

    I have manually refreshed to screen and checked all the spelling and I can't figure out why this will not work.

    Would greatly appreciate any help anyone may have to offer!

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You probably have a lookup field for tblInvAAMainCats.AACatID. Remove it and see what data is actually stored in that field.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    AJJJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    12
    Hi Gicu Thanks for your help. There is no lookup field in the table. I put a control Box on the form with a controlsource 0f =cboMainCat just to see what the value of the combobox was and it show the Category number just as it should.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Do you have a Me.Requery in the AfterUpdate event of the cboMainCat combo?

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Try the afterupdate event of the combobox to set the form's recordsource to the sql string.

  6. #6
    AJJJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    12
    Quote Originally Posted by Gicu View Post
    Do you have a Me.Requery in the AfterUpdate event of the cboMainCat combo?

    Cheers,
    Vlad

    I do have me.requery in the afterupdate event of the combo box and have tested that it is working.

  7. #7
    AJJJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    12
    Quote Originally Posted by davegri View Post
    Try the afterupdate event of the combobox to set the form's recordsource to the sql string.
    Thanks. I will try that, but I'm still interested to uncover why this has worked in the past but not now.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry to not follow you, but with Me.Requery you are still not getting the filtered results or are you getting them as expected?
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    AJJJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    12
    Quote Originally Posted by Gicu View Post
    Sorry to not follow you, but with Me.Requery you are still not getting the filtered results or are you getting them as expected?

    No Results after requery

  10. #10
    AJJJR is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    12
    Quote Originally Posted by Gicu View Post
    Sorry to not follow you, but with Me.Requery you are still not getting the filtered results or are you getting them as expected?
    I have this working in a small test DB so I'm going to try and reconstruct the form and test it step by step



    No Results after requery

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filter Query combobox options
    By dhannant in forum Access
    Replies: 2
    Last Post: 05-12-2015, 03:25 AM
  2. Replies: 7
    Last Post: 11-11-2014, 06:10 PM
  3. Query Filter Trough ComboBox
    By Dinjyo65 in forum Queries
    Replies: 5
    Last Post: 07-16-2012, 11:43 AM
  4. Replies: 0
    Last Post: 08-24-2010, 06:38 PM
  5. Replies: 0
    Last Post: 12-16-2009, 01:14 PM

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