combo box with multiple values for filtering by criteria

[Dachbo]

Hello,



I have a combo box with some values like "Field11", "Field12"... (see image) which are some column-names in this table.



I would like to get only the people with an "Y" in the cell when I select "FieldXY". If I select "Field17" in the combo box I only want to get the one person... "Field13" the 6 person with a "Y"

I tried it with a simple Query but I noticed it's not a good option. I think it requires VBA, could anybody give me some advice how the code could look like?

Thanks!

[June7]

Looks like non-normalized data structure and that is what is making this so difficult.

Build a form based on the table or query and apply filter criteria to the form. Use the intrinsic search/filter tools from ribbon and right click menu or use VBA to build filter string. http://www.allenbrowne.com/ser-62.html

[ranman256]

You cant use form boxes in a query if there's nothing in them..so..
Test all controls for a possible filter then build the where clause.

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value


    'remove 1st And
sWhere= mid(sWhere,5)


  'just use the filter


me.filter = sWhere
me.filterOn = true

[Dachbo]

Thank you both.

I'm little confused though. How do I use the .filter and how do I tell Access to list the person with the "Y" in the specific column in a new list box?

[June7]

We are also confused. Your OP does not mention a listbox. The suggestion is to have form bound to the dataset and use code to build filter criteria and apply to the form Filter property.

[Dachbo]

oh sorry, I forgot to mention this.

I have a combobox to select a column ""Field_XY" and when I click the go button all the people with a "Y" in this column, should show up in the listbox.

[June7]

Code will have to set the listbox RowSource property, in VBA something like:

Me.listboxname.RowSource = "SELECT ID, Lastname, Firstname FROM tablename WHERE " & Me.comboboxname & "=True;"

That assumes the fields are yes/no type. If they are text type:

Me.listboxname.RowSource = "SELECT ID, Lastname, Firstname FROM tablename WHERE " & Me.comboboxname & "='Y';"

The GO button is not necessary. Can put the code in combobox AfterUpdate event.

[Dachbo]

thanks, works just fine!

[Dachbo]

how do I edit the code, to filter it even more by some check boxes

[June7]

Review http://www.allenbrowne.com/ser-62.html

[Dachbo]

 Me.lstbx_EMPLOYEES.RowSource = "SELECT EMPLOYEES_ID FROM tbl_EMPLOYEES WHERE " & Me.cmbx_EMPLOYEES & "='Y';"
    
    If Me.chck_OAP1 = -1 Then
        Me.lstbx_EMPLOYEES.RowSource = "SELCET EMPLOYEES_ID FROM tbl_EMPLOYEES WHERE ='OA_P1';"
    
    End If

When I step through the code:
- it displays all entries with a "Y" in the list box,
then
- the If function will clear the list box, when it's checked, but there are records with "OP_P1"

I think after the WHERE is something missing, but I don't know what.

[June7]

Need the field name to apply filter parameter to.

WHERE [some field name here] = some data here

[Dachbo]

it still doesn't work

I tried it with different field names and data names.

[June7]

"doesn't work" means what - error message, wrong results, nothing happens? Post revised code.

[Dachbo]

nothing happens, it steps through the code but doesn't bring up any results in the list box

 If Me.chck_OAP1 = -1 Then
                  Me.lstbx_EMPLOYEES.RowSource = "SELCET EMPLOYEES_ID FROM tbl_EMPLOYEES WHERE [DEPTS] ='OA_P1';"

      End If