Consulting

Results 1 to 12 of 12

Thread: Embeded Macro Apply Filter using ComboBox.Column(#) value

  1. #1
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location

    Embeded Macro Apply Filter using ComboBox.Column(#) value

    Help with combobox Event>After Update>Embedded Macro>Apply Filter

    Let's assume that I have a simple (not normalized) query that returns:

    Action Last Name Datestarted
    Job 1 Smith 12/1/2023
    Job 2 Miller 12/5/2023

    Job 3 Smith 12/14/2023


    I use that query as the row source for a form. On that form there is a combobox that displays the Last Name of each record. Yes, Smith is listed twice and I understand that is not logical. Please humor me as this is just a basic example and I am only looking for a method to perform a more complex task.

    When I select Miller in the combobox, I want my form to repopulate with any record where the Last Name is "Miller" (so in this case just one). If I then select "Smith" then I would want the form to repopulate with all the records where Last Name is "Smith) (or two records)

    Sitting here in a pile of hair and bloody scalp, I have managed to achieve this goal by using combobox After Update event and the Embedded Macro “Apply Filter” where my where condition is:

    [LastName]=[Forms]![frmTest].[ComboLastName]
    However here is where it gets complicated (for me). In my actual form, the combo box has four columns of data. The first column (the bound column) is not displayed. Obviously, the where condition used above fails because the condition is never met.

    I have proven to myself that when I enter the following as code in the ComboBox_Click event it returns the for values stored.
    MsgBox [Forms]![frmTest].[ComboLastName].Column(0)
        MsgBox [Forms]![frmTest].[ComboLastName].Column(1)
        MsgBox [Forms]![frmTest].[ComboLastName].Column(2)
        MsgBox [Forms]![frmTest].[ComboLastName]].Column(3)
    However, if I change my where condition to any of the following or combination of the following:
    [LastName]=[Forms]![frmTest].[ComboLastName.Column(1)]
      [LastName]=[Forms]![frmTest].[ComboLastName.][Column(1)]
      [LastName]=[Forms]![frmTest].[ComboLastName.][Column][(1)]
    I get either a “Enter Parameter Dialog” when the event fires or an alert that bracketing is wrong.

    The only way have have been able to get this to work in my actual form is to:
    1) Change the bound column of the combobox from 1 to 2 and …
    2) Use Where
    [LastName]=[Forms]![frmTest].[ComboLastName]
    Question:
    How do I get my where expression to read the ComboLastName.Column(1) value e.g.,
    [Forms]![frmActiveAppeals].[Combo29].Column(1)
    instead of having to change the bound column?


    Thank you.
    Last edited by Aussiebear; 07-19-2024 at 01:44 PM.
    Greg

    Visit my website: http://gregmaxey.com

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    372
    Location
    Really should do filter on record ID, not descriptive text.

    I don't think macros can read Column property (queries can't). I don't use macros, only VBA.

    Can have a textbox with expression to read combobox column then macro references textbox.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location
    I know you are correct with what I should do. I should really recreate (or at least try) a normalized database but time and budget is forcing me to continue to push golf balls through garden hoses.

    I can probably meddle through the VBA process with a textbox as you describe

    Thanks
    Last edited by Aussiebear; 06-22-2024 at 08:20 PM.
    Greg

    Visit my website: http://gregmaxey.com

  4. #4
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    372
    Location
    If you go with VBA, don't need textbox.

    Textbox hack was for macro.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location
    Thanks. I went with a VBA solution.
    Greg

    Visit my website: http://gregmaxey.com

  6. #6
    To achieve your goal of applying a filter based on the selected value in a ComboBox where the desired value is in a non-bound column, you need to use a VBA function to construct your filter criteria.
    Last edited by georgiboy; 07-21-2024 at 11:48 PM. Reason: Removed spam

  7. #7
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location
    I think I already said that I went with a VBA solution so I fail to see the point of you popping in three weeks later to tell me what I need to do
    Greg

    Visit my website: http://gregmaxey.com

  8. #8
    VBAX Contributor
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    113
    Location
    Quote Originally Posted by gmaxey View Post
    I think I already said that I went with a VBA solution so I fail to see the point of you popping in three weeks later to tell me what I need to do
    Newbie, with just that one post.

  9. #9
    Administrator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,256
    Location
    timothyferri's reason for posting was not to help but to hide spam...
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2408, Build 17928.20080

  10. #10
    VBAX Contributor
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    113
    Location
    Ah, I see now. Missed the edit note. Sorry.

  11. #11
    Administrator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,256
    Location
    You didn't miss it, I have been away for a few days and only edited their post this morning.
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2408, Build 17928.20080

  12. #12
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,236
    Location
    There has been a few of those lately.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

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