1

In an Access Userform I have a listbox that is linked to a dropdown field. In the dropdown field the user can choose a country, and the listbox then updates and shows only the records that are relevant to that country.

I have described how I get the listbox to filter based on the selection in the dropdown in this question here.

While I managed to solve one problem there I created another.

Now when I open the form, the listbox is empty instead of showing all records.

My question: How can I show all records in the listbox to begin with and THEN have the user filter the list based on the dropdown?

The SQL Code in the LIstbox is now the following:

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].[Text]) AND ((tblFUNDS.Fund_Selection)=0));
Community
  • 1
  • 1
rohrl77
  • 3,277
  • 11
  • 47
  • 73

1 Answers1

0

So I ended up solving this one as well... with a bit of help from an online article which I can now no longer find unfortunately (otherwise I would reference it here):

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) Like Nz([Forms]![frmMain]![ddnCountry].[Text],'*')) AND ((tblFUNDS.Fund_Selection)=0));

The important part is this...

Like Nz([Forms]![frmMain]![ddnCountry].[Text],'*')) AND ((tblFUNDS.Fund_Selection)=0));

Essentially the Nz function lets you return a value when a variant is null. I had it return * which ofcourse is the SQL equivalent of Return All.

rohrl77
  • 3,277
  • 11
  • 47
  • 73