1

I thought this was going to be the easiest question ever to solve, but after spending a morning searching for answers, I still can't get it to work.

I have a list box showing data for certain countries on a user form that is linked to a query. I want to allow my users to filer that list based on the selection in a dropdown.

This is the SQL in the list box row source:

SELECT tblFUNDS.MorningsStar_Fund_Name, tblFUNDS.ISIN, tblFUNDS.RDR, tblISIN_Country_Table.Country
FROM tblFUNDS INNER JOIN tblISIN_Country_Table ON tblFUNDS.ISIN = tblISIN_Country_Table.ISIN
GROUP BY tblFUNDS.MorningsStar_Fund_Name, tblFUNDS.ISIN, tblFUNDS.RDR, tblISIN_Country_Table.Country, tblFUNDS.Fund_Selection
HAVING (((tblISIN_Country_Table.Country)=[Forms]![frmMain]![ddnCountry]) AND ((tblFUNDS.Fund_Selection)=0));

Both the values in the list box and the dropdown are displaying correctly. However, I have not been able to get the dropdown selection to filter the list.

So far I have tried inserting the following reference and various versions of it into the query that the list box links to in the row source:

[Forms]![frmMain]![ddnCountry]

I also inserted the following code for the On Change Event in the Dropdownlist: 

Private Sub ddnCountry_Change()
    cmbFIlterSelection.Requery
End Sub

The On Change event fires correctly because when I set a breakpoint it stops on the one line of code, but it seems to do nothing.

What am I doing wrong?

rohrl77
  • 3,277
  • 11
  • 47
  • 73

1 Answers1

0

Ok. After hours of looking for answers and now even posting on here, I found the problem:

The SQL Query needed to be modified to include the following .Text

Now it reads:

[Forms]![frmMain]![ddnCountry].[Text]
rohrl77
  • 3,277
  • 11
  • 47
  • 73
  • 1
    That kind of makes sense. That means that you likely could also just put your event into the `AfterUpdate` as well and it should work without the `.Text` you really only need to use `.Text` whenever the value hasn't had the chance to update. – Newd Jun 17 '15 at 15:46
  • @Newd I just tried that... the `AfterUpdate` event works fine and it makes sense to me to use that in the future, but I still had to put `.Text` for it to work properly. – rohrl77 Jun 18 '15 at 07:05
  • You had to use `.Text` in `AfterUpdate`? – Newd Jun 18 '15 at 12:07
  • Yup. Otherwise it failed. I am in fact still working on this particular problem, but it is no longer the issue that I have described here. I want the Listbox to show all possible records before the users start filtering it using the dropdown... I have a feeling I am going to be revisiting this exact question. – rohrl77 Jun 18 '15 at 12:50