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

[gmaxey]

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.

[June7]

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.

[gmaxey]

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

[June7]

If you go with VBA, don't need textbox.

Textbox hack was for macro.

[gmaxey]

Thanks. I went with a VBA solution.

[timothyferri]

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.

[gmaxey]

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

[Gasman]

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.

[georgiboy]

timothyferri's reason for posting was not to help but to hide spam...

[Gasman]

Ah, I see now. Missed the edit note. Sorry.

[georgiboy]

You didn't miss it, I have been away for a few days and only edited their post this morning.

[Aussiebear]

There has been a few of those lately.