Consulting

Results 1 to 6 of 6

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,382
    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 gmaxey; 06-22-2024 at 03:25 PM.
    Greg

    Visit my website: http://gregmaxey.com

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    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,382
    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
    348
    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,382
    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. retro bowl

Posting Permissions

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