Multi-select listbox to display distinct values from table

[MG101]

Hi All,

I am currently trying to make a form which will generate a report for the users. Currently the form has 3 combo boxes where users select which fields they want displayed from the table in the report. They also have sorting options next to them (Asc or Desc). I am now trying to put in a multi select list box under each combo box to allow users to filter the report to their liking. Currently I am trying to pull distinct values from the specific field of the table and display them in the listbox for users to select. I have tried using vba to set me.filter1.rowsource = SELECT DISTINCT me.combo1 FROM EVAP_Database, but this doesnt seem to work. I have also tried a few other codes and still no luck. Any assistance with this task is much appreciated. Thanks!

Table: EVAP Database
Combo: Combo1
Filter Listbox: Filter1

 

[pbaldy]

Try

me.filter1.rowsource = SELECT DISTINCT " & me.combo1 & " FROM EVAP_Database"

 

[MG101]

Hi Pbaldy,

I tried your code and I received a compile error. I added a quotation before SELECT and it seemed to resolve the error but when I go to try the code out, the listbox remains blank.

Here is my file for reference if you like.
I left the code in the after update and on change of the combo box events .

Thanks!

 

[pbaldy]

Because of the inadvisable spaces, and using the actual table name:

Me.Filter1.RowSource = "SELECT DISTINCT [" & Me.Combo1 & "] FROM [EVAP Database]"

 

[MG101]

Thanks! That worked perfect!

Is it easy for you to explain the use of " and & like that in coding, I am not very fluent at all and was just curious if it was easily explainable. If not, Thanks anyways

 

[pbaldy]

The idea is to concatenate the literal parts of the SQL string that will not change with a changing value from the form. When the line is processed, the SQL that ends up in the row source looks like:

SELECT DISTINCT [Date Code] FROM [EVAP Database]

The way you had it would have been

SELECT DISTINCT me.combo1 FROM EVAP_Database

and Access couldn't interpret the "me.combo1", since Me is only valid in VBA code.

 

[billmeye]

Here is the filter form Custom3.