MS Access combo box filter subform with Select Distinct

[Costinu]

Hello,

I have the following:
- tblProductList - table with prod1, prod2 and prod3 fields
- frmProductList - form, made automatically when I made the subform
- frmProductSearch - main form
- sfProductList - subform, takes values from the table
- cBx1, cBx2 and cBx3 - combo boxes that act as filter selection for the subform

Made in the main form header 3 combo boxes, each to act as a filter for the 3 fields in the subform in the same page.
- i have managed to make the filters to work together, so i can narrow down my search - a relative long repetitive vba code using NZ function
- i have manage to make cBx1, cBx2 and cBx3 to take all the values but no duplicates from prod1, prod2 and prod3 using SQL view on the code of the combo box and using

SELECT DISTINCT tblProductList.prod1
FROM tblProductList
ORDER BY tblProductList.prod1

- i can't manage to make the combo boxes take the values from the remaining values in the subform, so that after i am using 1 of the filters, the other 2 to display the remaining values that can be usable ( shown in the subform ) insted of all of the available values from the table.
I tried to change the source but haven't managed and also i haven't managed to make the filtering happen in a query and the subform to take the remaining values from there or something...

Also it will be great, instead of showing every value from the table in the subform, before i start to filter, the subform to be blank and values to appear after the filtering starts.

Any help with my quest ?

 

[theDBguy]

Hi. Welcome to AWF!

The above description sounds a bit complicated to me (maybe it's too early in the morning yet), but can you, instead, post a sample copy of your db with test data to help clarify your request a bit? Thanks!

 

[Costinu]

Sure, will post in about 60 min.

Thanks

 

[theDBguy]

Sure, will post in about 60 min.

Thanks

Thanks! 60 min will give me time to have coffee (and breakfast) first.

 

[Costinu]

I hope you enjoyed your coffee and breakfast.

this is what i want to achieve

Primary target 1:
When I select from filter 1 Option 1, in filter 2 to be available only Option A and Option B ( the ones remaining in the subform ). I don't want Option C to still be there cause if i select it it will give me an empty subform and if i will have 100 options ...

Primary target 2:
Combo box filter to use the values from the primary table and not display duplicates

Secondary target 1:
It will be nice in the subform to be displayed only the filtered data, instead of all the items in the source table ( like it is before applying any filter or after i will make a button to clear them for a new search )

Secondary target 2:
To find a simpler code to make all the filters to work together cause i will have up 20 filters ...


Goal of this project is:
- to find very easy a product based on one or more specific criteria
- to have a picture of the product at the end
- to be able to search, if needed, between range of values for any of the filter, instead of selecting every single available option in the filter box
- to be able to compare the products available after the filtering, to be able to indentify and difference between them
- to put everything nicely in a report for a presentation

thank you for your help !

 

[theDBguy]

Hi. Thanks. Yes, I'm a little more awake now. Okay, let me take a look, and I'll let you know...

 

[theDBguy]

Hi. Okay, I gave it a quick look. Please see attached modified copy of your db.

For Primary Target #1: This is called a "cascading combobox." There are several tutorials available on that subject.

For Primary Target #2: Not sure I understand, 'cause I think you're already doing this with the DISTINCT keyword, right?

For Secondary Target #1: Not sure I understand. When the form opens, the subform displays all source data. When a filter is selected, the subform lists only those that match. Are you saying you don't want the subform to initially open showing all the records?

For Secondary Target #2: There are also plenty of tutorials or demoes for this topic of "search forms." My preferred approach is to use individual If/Then blocks, rather than use a single/large If/Then block with a bunch of ElseIfs.

Hope that helps...

 

[Costinu]

Thanks for the help !! I am pretty new in MS Access.

For Primary Target 1: you had a great ideea with the cascading combo boxes. But there is a down side to it. I need to complete the filter boxes in order and in case i need to skip one and enter the value in the next one ... it won't be possible. But i will look for some tutorials based on your ideea and see if i can combine the codes. I just spent a lot of time trying to change the sourse of the condition FROM of the filter box - SQL view

For Primary Target 2: yes, i have found a way to make it work. I just mentioned this because it is very important and don't want to give up on it - in case the code needs to be changed.

For Secondary Target 1: when the form opens i want the subform to be empty and not to display all source data. Only when a filter is selected, the subform to lists only those that match

For Secondary Target 2: not sure if i understood you completely, being new at this, but from what i understood is that i made the code like you said you like yourself to do it.
The only problem is that if i will have up to 20 filters i will do a lot of combinations ( show this, don't show that ... ) for every possible scenario.
Will look for some tutorials regarding this.

I have spent 2 days watching tutorials to fix this issues but i haven't found anything so far. Will keep searching.


Thank you again for all your help. I will post this here if i can manage to make it work like i want to, just in case someone else will need a part of this, at least.

Thanks again for your support !!

 

[Costinu]

@MajP

Thank you so much for helping. The solution you sent makes it work in any way i select the filter boxes - just what i needed.
The problem is that it doesn't display the correct filtered data.
For example if I select Option2 it will display Option 1, then with Option2 slelected, if i select in the second filter OptionA, it i will display in the 3rd column the specification that is not allocated to the selection.
I will need to try and understand what you have done there )) because i have never worked with modules, barely managed to work myself up tu basic VBA and then try to make it work based on your ideea and @theDBguy ones.

Thanks a lot ! Both of you ! I have never expected such kindness.

 

[theDBguy]

But there is a down side to it. I need to complete the filter boxes in order and in case i need to skip one and enter the value in the next one ... it won't be possible.

I think @MajP covered that issue in the demo they posted above.

when the form opens i want the subform to be empty and not to display all source data

That should be easy to do. Start out with a subform with an empty RecordSource or one that doesn't return any records. You can then update the RecordSource from your filters.

not sure if i understood you completely, being new at this, but from what i understood is that i made the code like you said you like yourself to do it.

No, not quite the same. You did something like this:

If Condition1=True Then
   'do stuff
ElseIf Condition2=True Then
   'do other stuff
ElseIf Condition3=True Then
   'do more stuff
End If

I am talking more about something like this:

If Condition1=True Then
    'do stuff
End If
If Condition2=True Then
    'do other stuff
End If
If Condition3=True Then
    'do more stuff
End If

If you look at some of those "search form" demoes or threads I was talking about, you'll see what I mean.

 

[Costinu]

@theDBguy
Thanks again !
Not sure how i can make this work:
"That should be easy to do. Start out with a subform with an empty RecordSource or one that doesn't return any records. You can then update the RecordSource from your filters."
Can you please help me out a little ?

Regarding
If - do stuff - and if
instead of
if - do stuff - elseof - do stuff - end if
It never crossed my mind that is not the same thing. But like i said, i am new at this access thing and will give it a try. I am more than sure it will make a huge difference.

 

[theDBguy]

@theDBguy
Thanks again !
Not sure how i can make this work:
"That should be easy to do. Start out with a subform with an empty RecordSource or one that doesn't return any records. You can then update the RecordSource from your filters."
Can you please help me out a little ?

Regarding
If - do stuff - and if
instead of
if - do stuff - elseof - do stuff - end if
It never crossed my mind that is not the same thing. But like i said, i am new at this access thing and will give it a try. I am more than sure it will make a huge difference.

Okay, give me a few minutes, and I'll modify your file again, just to show you what I mean. (Of course, I won't be modifying the "cascading combo" part, because you'll probably go with @MajP's way on those anyway). Cheers!

 

[Costinu]

If you have the time and an easier way to do it (so i can also learn something from the modifications you are making ) it will be much appreciated. The cascading combo box from @MajP uses modules / macros and i barely know VBA.

 

[theDBguy]

Okay, hope this helps...

Edit: Oops, I didn't see you last post. It's time for lunch . I'll see if I can figure something out later.

 

[MajP]

You may want to look at this thread. This requires little code and is a super powerful filter.

The Most Powerful form Filter and Requires almost No Code

NOTE: See updated code below. I recommend using the updated class module that @arnelgp modified. It further simplifies building a search form and addresses some bugs...

www.access-programmers.co.uk

 

[Costinu]

@theDBguy i understand now what you meant by if - do something - end if. very neat and clean ... doesn't compare whit what i have done with the code
@MajP thank you for the tip. i will certainly have multiple looks in that. because i don't know very much how to code, i tend to see what others are doing, try to do the same and understand in the same time part of the technique and code.

Thank you both very very much !

I will do my best and post my result here. Hopefully i will manage to make my little project gain a shape.

 

[theDBguy]

Okay, sounds good. Good luck with your project!

 

[MajP]

The problem is that it doesn't display the correct filtered data.
For example if I select Option2 it will display Option 1, then with Option2 slelected, if i select in the second filter OptionA, it i will display in the 3rd column the specification that is not allocated to the selection.

Sorry that version what messed up. Try this. Your conditions caused a very strange bug in Access. It required a workaround which I do not even understand. The first time you entered a combobox the filter would always return the first selection regardless of the real selection.

The cascading combo box from @MajP uses modules / macros and i barely know VBA.

The goal with a module or class is to provide a black box. You do not need to understand how it works just how to use it. It would be like you asking for instructions on how to build a bike so you can get to get to the store, and I say just take my car.

To demonstrate here is my code to create your filter

Dim Spec1 As String
Dim Spec2 As String
Dim Spec3 As String
Dim fltr As String
Dim strSql As String
Spec1 = GetFilterFromControl(Me.cBxFilter1)
Spec2 = GetFilterFromControl(Me.cBxFilter2)
Spec3 = GetFilterFromControl(Me.cBxFilter3)
fltr = CombineFilters(ct_And, Spec1, Spec2, Spec3)

Yours is

If Nz(cBxFilter1.Value, "") = "" And Nz(cBxFilter2, "") = "" And Nz(cBxFilter3, "") = "" Then
Form_sfProductList.Filter = ""

ElseIf Nz(cBxFilter1.Value, "") <> "" And Nz(cBxFilter2.Value, "") = "" And Nz(cBxFilter3, "") = "" Then
Form_sfProductList.Filter = "Spec1='" & cBxFilter1.Value & "'"

ElseIf Nz(cBxFilter1.Value, "") = "" And Nz(cBxFilter2.Value, "") <> "" And Nz(cBxFilter3, "") = "" Then
Form_sfProductList.Filter = "Spec2='" & cBxFilter2.Value & "'"

ElseIf Nz(cBxFilter1.Value, "") = "" And Nz(cBxFilter2.Value, "") = "" And Nz(cBxFilter3, "") <> "" Then
Form_sfProductList.Filter = "Spec3='" & cBxFilter3.Value & "'"

ElseIf Nz(cBxFilter1.Value, "") <> "" And Nz(cBxFilter2.Value, "") <> "" And Nz(cBxFilter3, "") = "" Then
Form_sfProductList.Filter = "Spec1='" & cBxFilter1.Value & "' AND Spec2='" & cBxFilter2.Value & "'"

ElseIf Nz(cBxFilter1.Value, "") <> "" And Nz(cBxFilter2.Value, "") = "" And Nz(cBxFilter3, "") <> "" Then
Form_sfProductList.Filter = "Spec1='" & cBxFilter1.Value & "' AND Spec3='" & cBxFilter3.Value & "'"

ElseIf Nz(cBxFilter1.Value, "") = "" And Nz(cBxFilter2.Value, "") <> "" And Nz(cBxFilter3, "") <> "" Then
Form_sfProductList.Filter = "Spec2='" & cBxFilter2.Value & "' AND Spec3='" & cBxFilter3.Value & "'"

Else
Form_sfProductList.Filter = "Spec1='" & cBxFilter1.Value & "' AND Spec2='" & cBxFilter2.Value & "' AND Spec3='" & cBxFilter3.Value & "'"

End If

If I want to add a 4th combobox then I need 2 lines of code

dim Spec4 as string
spec4 = getFilterFromControl(Me.cbxFilter4)

and then just add it to my filter combiner

fltr = CombineFilters(ct_And, Spec1, Spec2, Spec3,spec4)

If I wanted 20 combox then I just have 20 Dim statements, and 20 getFilterFromControl statements.

Sure the modules take a lot of time, but they do a whole lot and are resused everywhere I need a filter from a control. Build once and use many times.

Now the hard part is the cascading comboxes in any order. .
@theDBguy, you may want to look at what I did to see if you can think of an easier way. This works well for me, but it is a little confusing on how it works. There may be a better approach.

@Costinu, The amount of code to do this cascading is small but the concepts are kind of advanced. Simpler to understand solutions will be much more labor intensive, and impractical for more than a few comboboxes. This solutions works with any amount of comboboxes and not additional code is required.

Every time I filter the form I modify the query definition of the underlying query. Each combobox gets it rowsource using a distinct on this "filtered" query. This would not work by applying the filter to the subform, you have to modify its query.

However. Your example with Spec 1, Spec2, ... hints of an improperly designed database. This may just be an example, but if this is representative you may want to discuss your tables first.

 

[Costinu]

@MajP

Thanks again for helping.
The only reasons I wanted to understand what I am doing are:
- to be able to make modifications - for example toadd a fourth combo box for example or another field ... and know that i need to add another line and where.
- to actually manage to learn from you guys and be able to do simple things by myself.

Regarding the modules... if they are like a car and you can just borrow ( copy - paste ) and doesn't require any modifications they are more than welcome, as long as i don't need to make any modifications to their code.

From a newbie those things just breaks your mind

- regarding my table .. i just want to have, for example:
Brand - Model - Processor - GHz - RAM - type of RAM - display - a.s.o. and based on what i need to find to narrow the results to the perfect fit and similar products.

I want to do this for myself because i am selling industrial spare parts like chains, motors, gear reducers, conveyor belts, ball bearing a.s.o.
These products have a lot of characteristics and is very hard to find the correct one searching thru catalogs ... and Access is the only database software that can be installed on the laptop by the IT department. So i want to ease my work regarding the products i need to identify and be very precise. One dimension or characteristic wrong and the customer will receive a product not fitted to his needs.

 

[arnelgp]

another sample.