I have 2 combo boxes.
ComboBox1 is the field list from a query:MyQuery (for example)
I can select any field in that query
ComboBox2 is the content results of the Field selected by ComboBox1
If I've selected the field INVOICE in ComboBox1
then I see the list of INVOICES in that field in ComboBox2 and can select one of them from ComboBox2 to use when executing
the next procedure,
which is to FIND and display all records (Line items) that match THAT INVOICE.
OK, I've been programatically able to change the rowsource for ComboBox2
BUT, there are some records for which the INVOICE data wasn't entered so I get 2 visually blank rows at the top of my list in
ComboBox2, which I want to get rid of.
ONE of those rows represents NULL values
THE OTHER row represents EMPTY/BLANK values equal to ""OK, I've been programatically able to account for those and exclude
them by having VBA create the Rowsource for ComboBox2 as follows:
SELECT DISTINCT MyQuery.INVOICE FROM MyQuery WHERE (((MyQuery.INVOICE) Is Not Null) AND (Not(MyQuery.INVOICE) ="")) ORDER BY
SELECT DISTINCT - keeps multiple instances of the same INVOICE number out of the list and just presents 1 INVOICE number for
a group of records that share that same number
WHERE (((MyQuery.INVOICE) Is Not Null) AND (Not(MyQuery.INVOICE) ="")) - effectively eliminates any NULL values and any ""
values from the list in the query.
The whole SQL string is set to a variable strSQL , strSQL represents the entire SQL string
Just prior to setting the ComboBox2.rowsource I've been using a trouble shooting technique that we are familiar with to
verify that the strSQL is exactly what I want : Debug.Print strSQL
The strSQL is Exactly correct and can be verified by copying the SQL from the Debug.Print and pasting it into a blank query
in SQL (design) view then running the query it produces exactly the results I want for the field list.
Then I ATTEMPT to plug it into the rowsource of Combobox2 like this:
ComboBox2.rowsource = strSQLNOW FOR THE ISSUE:
BUT, the null and blank fields still show at the top in the ComboBox2 list. When I examine the rowsource property of the
ComboBox2 it has stripped the WHERE clause in the strSQL statement.
Any idea why I can't pass the pure string to the comboBox2.rowsource property as is?