Adding 'Blanks' & 'Non-blanks' to a filter combo box

  • Thread starter Thread starter Robin
  • Start date Start date
R

Robin

I have a form with 3 pairs of combo boxes that allow a user to set up
filtering - the left of each pair has a query field list as row source - the
right of each pair has a distinct sql statement as rowsource, based on the
same query where the field is the one selected in the left combo. From the
BeforeUpdate event on the left combo:

strFilterField = (Nz(Me![LeftCombo], 0))

Me!RightCombo.RowSource = "SELECT DISTINCT SomeQuery.[" & strFilterField &
"] FROM SomeQuery;"


This works fine and I was feeling quite good about getting this to work, but
the client now wants to add 'Blanks' and 'Non-blanks' to the right combo!
So in addition to listing all possible contents for the chosen field, the
right combo now needs to display 'Blanks' and 'Non-blanks' in its list.

I'm sat here scratching my head - I'd really welcome any suggestions or
examples of how this might be done. (I'm sure I can sort out the
application of these extra options - just can't see an obvious way to get
them added to the combo list.)

Regards

Robin
 
Create a small table with one field named (say) ComboValue.
Save it as tblComboValue.
Enter 2 records for "(Blank)" and "(Non blank)".

Use a UNION query in the rowsource:
"SELECT DISTINCT SomeQuery.[" & strFilterField & "] FROM SomeQuery UNION
ALL SELECT ComboValue FROM tblComboValue;"
 
As always, thank you for your help Allen.

Two things constantly amaze me:

1) How good Access and VBA are at allowing the creation of almost anything
2) The exponential shape of the learning curve. Getting anywhere
respectably off the bottom is a challenge!

Thanks again.

Robin

Allen Browne said:
Create a small table with one field named (say) ComboValue.
Save it as tblComboValue.
Enter 2 records for "(Blank)" and "(Non blank)".

Use a UNION query in the rowsource:
"SELECT DISTINCT SomeQuery.[" & strFilterField & "] FROM SomeQuery
UNION ALL SELECT ComboValue FROM tblComboValue;"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Robin said:
I have a form with 3 pairs of combo boxes that allow a user to set up
filtering - the left of each pair has a query field list as row source -
the right of each pair has a distinct sql statement as rowsource, based on
the same query where the field is the one selected in the left combo.
From the BeforeUpdate event on the left combo:

strFilterField = (Nz(Me![LeftCombo], 0))

Me!RightCombo.RowSource = "SELECT DISTINCT SomeQuery.[" & strFilterField
& "] FROM SomeQuery;"


This works fine and I was feeling quite good about getting this to work,
but the client now wants to add 'Blanks' and 'Non-blanks' to the right
combo! So in addition to listing all possible contents for the chosen
field, the right combo now needs to display 'Blanks' and 'Non-blanks' in
its list.

I'm sat here scratching my head - I'd really welcome any suggestions or
examples of how this might be done. (I'm sure I can sort out the
application of these extra options - just can't see an obvious way to get
them added to the combo list.)
Click to expand...
Click to expand...
 
Back
Top