Automatic updating of query shown in a form based

问题:

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?

回答1:

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]