Using Form Filter to Limit Unbound/Bound Combo Boxes Search Record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an filter form with numerous options...
and I pass it to the form filter with a strwhere where condition, that
filters the form.
Now, the records ARE filtered on the form..
but, the bound/unbound comboboxes, which only lists the client name, still
lists ALL of the records..
so, some records do not appear.. which is good, shows the filter works..
BUT I only need the filtered client names to appear.. by somehow passing the
filter to the unbound/bound combo boxes..
Any suggestions?
 
There are two options that I can think of
1. pass a parameter using the OpenArgs of the command line of openning the
form, and then on the form load event write the code, to filter the rowsource
of the combo
' If the filter is number
Me.ComboName.RowSource = "Select Fieldname From Tablename Where FieldName =
" & Me.OpenArgs

' If the filter is number
Me.ComboName.RowSource = "Select Fieldname From Tablename Where FieldName =
'" & Me.OpenArgs & "'"
=============================================
2. In each row source of the combo, have a filter with reference to a field
in the form, so when the form is filtered the combo list will be filtered
using the field in the form

Select FieldName From TableName Where FieldName = Forms![FormName]![FieldName]
 
Ofer, thank you very much for replying!

I went with scenario # 2.. and it works well except
One of the options on the drop-down is ALL, which is not retrieving any
records with this row source modification..
I tried some random IIF statement like
ex. IIf([Forms]![Report Filter Form Adv
Filter]![CboReg]<>"ALL",[Forms]![Report Filter Form Adv Filter]![CboReg],)
but that didn't work as no records at all appear for either criteria
Final piece of the puzzle here..
Thank you,

Freddy
 
Hi, Freddy
Try this

Select FieldName From TableName Where FieldName Like
NZ(Forms![FormName]![FieldName],"*")

Or
Select FieldName From TableName Where FieldName Like
IIf(Forms![FormName]![FieldName]="" Or Forms![FormName]![FieldName] is null
,"*", Forms![FormName]![FieldName])

So if no record were selected in the combo. it will display all the records
 
Back
Top