Solved

ApplyFilter based on multiple criteria selected from combo boxes...

Posted on 2004-03-27
11
2,488 Views
Last Modified: 2012-06-27
Hello experts...

I am trying to create a task manager app using Access 2000.  I have everything working the way I want it to so far except for 1 thing.

The database stores tasks that include the following fields:
Status      (Open, Closed, Waiting, Canceled)
Category  (Personal, Common, Test)
Priority     (High, Medium, Low)

My main form obviously displays all of the fields.  The form (frmTasks) is built off of a query (qryTasks) of the tblTasks.  The form is not filtered.  What I have on my form is a section that is used for filtering the Tasks.
This section contains 3 combo boxes:
cmbFilter_Status
cmbFilter_CAT     'category
cmbFilter_Priority

   What I want to happen with these is, I want to be able to filter the database by selecting any number of combination of values from these combo boxes.  At first I got each combo box to work independently.  Then what I did was add a checkbox for each combo box and made each combo box disabled by default.
   I set up VBA code to have an integer for each checkbox (set the int to 0 on form open and set it to 1 if the chk is True).  Then for each combo box  
AfterUpdate, I set up a case statement that says if this box's int value is 1 then determine which combo box is enabled (also using the int value).  From there it does a  
DoCmd.ApplyFilter using the values from the combo boxes.

NOW.  That was all working fine for a little while....
IF I selected a Status, let's say "Open", it would show me all of the open tasks.  If I then checked Category and chose "Personal", it would show me all of the open personal tasks.  Then, selecting a "High" priority would show me all of the open personal tasks that had a high priority.
   I could do this in all sorts of combinations.  Then I decided to compact and repair.  I  reopened the database and now all of those functions either work sometimes, or not at all,  or in weird combinations.  So, I went back and modified the code a bit....
   It seemed to me that the reason it was working intermittently was that none of the selected values would be "cleared" when boxes were unchecked.  So, I added code that set the values to "".  That worked for a bit while I still had the db open, then I closed the db and went back in an hour later and the code stopped working again.

ok... now I am done with my long confusing story.  I am hoping that someone can help me with my code.  I am convinced that there is a simple solution that I am not seeing.  I am relatively new to VBA, but I do know my way around.  I just don't know all of the correct syntax yet.

Let me know if you need me to post my current code...
...or maybe send the whole darn database :-)

Any help would be much appreciated... thanks EDH
0
Comment
Question by:edh-corp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 10695754
I'm afraid the database and/or the form got corrupted.

First try a Tools/Database/Compact & repair.
Next try to export the form (right-click the form) to another (dummy) database, delete the form and use File/Get external Data/Import to get it baxk.

Finally a decompile might help:
http://www.granite.ab.ca/access/decompile.htm

In the worst case just rebuild the form :-(

Nic;o)
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10695794
Hi EDH
The following is extracted from http://www.experts-exchange.com/Databases/MS_Access/Q_20928451.html

You can use the following code behind each combo's afterupdate property or placed in a command button. This overcomes the problem of having an empty combo(s). [Your Field] of course corresponds to the fields in your form.

Dim strFilter As String

If Not IsNull(SearchCombo1) Then
strFilter = strFilter & "[Your Field] = '" & SearchCombo1 & "' and "
End If
If Not IsNull(SearchCombo2) Then
strFilter = strFilter & "[Your Field] = '" & SearchCombo2 & "' and "
End If
If Not IsNull(SearchCombo3) Then
strFilter = strFilter & "[Your Field] = '" & SearchCombo3 & "' and "
End If

strFilter = Left(strFilter, Len(strFilter) - 5)
DoCmd.ApplyFilter , strFilter

The beauty of this code is that you can add more combos using the same principle. Hope this helps

jaffer
0
 

Author Comment

by:edh-corp
ID: 10696309
jaffer

    I added the code you supplied to each of my combos.  I modified the code using my field and combo names, but it still isn't working.  It is actually working just how I had it before, what I mean is, it is filtering correctly at first, even changing some selections from the combos will re-filter the filtered records.
    I guess what I need is to have some way of compensating when it hits a combination of criteria that returns no records.  And what is even stranger, even if I remove the filter from the form I cannot apply anymore filters until I close and re-open the form.  For example, if I want to filter for all records that have a status of "open", I would only select "open" from the cmbFilter_Status box.  However, this is returning a blank record.  It seems to work OK when I first open the form, but if I do a complicated combination of criteria, then do a "showallrecords", and then try to start over, none of the filtering works.

Any thoughts on that?
Thanks
EDH
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:edh-corp
ID: 10696362
Here is one example of what I entered.... looks pretty straight foward too me???

Private Sub cmbFilter_CAT_AfterUpdate()
Dim strFilter As String

If Not IsNull(cmbFilter_CAT) Then
strFilter = strFilter & "[category] = '" & cmbFilter_CAT & "' and "
End If
If Not IsNull(cmbFilter_Status) Then
strFilter = strFilter & "[status] = '" & cmbFilter_Status & "' and "
End If
If Not IsNull(cmbFilter_Priority) Then
strFilter = strFilter & "[priority] = '" & cmbFilter_Priority & "' and "
End If

strFilter = Left(strFilter, Len(strFilter) - 5)
DoCmd.ApplyFilter , strFilter
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10696366
Hmm, getting the feeling that you don't initialize the filterstring before building it.

Personally I would place the filters on an unbound form and place the frmTasks as a subform (named "sfrmTasks").

Now build the subform's filter like:

strFilter = ""
IF len(nz(me.combo1))>0 then
    strFilter = " and [Status]='" & me.cmbStatus & "'"
endif
IF len(nz(me.combo2))>0 then
    strFilter = " and [Cat]='" & me.cmbCat & "'"
endif
IF len(nz(me.combo3))>0 then
    strFilter = " and [Priority]='" & me.cmbPriority & "'"
endif

if len(strFilter) > 0 then
   strFilter = Mid(strFilter,5)
   me.sfrmTasks.form.filter = strFilter
    me.sfrmTasks.form.filteron = true
endif

Nic;o)

0
 
LVL 1

Expert Comment

by:moralju
ID: 10696863
I had the same function to do once and this is how I did it.

I actually found this code here but can't remember who I got it from.

Dim SQLstr As String, Wstr As String

SQLstr = "SELECT table.field , table.field FROM table "
Wstr = "WHERE"

If me.cmbFilter_Status="OPEN" Then
Wstr = Wstr & " (table.field) = '" & me.cmbFilter_Status & "' AND"
End If

If cmbFilter_CAT = "Closed" Then
Wstr = Wstr & " (table.field) = '" & fForm.cboSkillSet & "' AND"
End If

Wstr = Mid(Wstr, 1, Len(Wstr) - 4)

fForm.RecordSource = SQLstr & " " & Wstr
fForm.Requery

0
 
LVL 1

Expert Comment

by:moralju
ID: 10696872
oops corrections to code posted

If me.cmbFilter_Status="OPEN" Then
Wstr = Wstr & " (table.field) = '" & me.cmbFilter_Status & "' AND"
End If

If me.cmbFilter_Status = "Closed" Then
Wstr = Wstr & " (table.field) = '" & me.cmbFilter_Status & "' AND"
End If

0
 

Author Comment

by:edh-corp
ID: 10697273
Ok Nico

I am trying your suggestion now.  
Let me give some more info:

tblTasks has the following fields:
ID (autonumber)
Category
Status
Priority
Title
Description
DateOpened
DateDue
DateClosed

I created a new unbound form with my 3 combos.

frmTasks1 has the following controls:
cmbFilter_Status   (value list of "Open";"Closed";"Canceled";"Waiting")
cmbFilter_CAT     (values from qryCategory, listing all of the possible categories, this way a user will be able to add more cats without having to write code to filter by the new ones.)
cmbFilter_Priority  (value list of "High";"Medium";"Low")

I then added a subform of my table and named it sfrmTasks (off of tblTasks, as you suggested).  I then took your code and added it to each of the combo's AfterUpdate events.
'----------------------------------------------------------------------
Dim strFilter As String

strFilter = ""
If Len(Nz(Me.cmbFilter_Status)) > 0 Then
    strFilter = " and [Status]='" & Me.cmbFilter_Status & "'"
End If
If Len(Nz(cmbFilter_CAT)) > 0 Then
    strFilter = " and [category]='" & Me.cmbFilter_CAT & "'"
End If
If Len(Nz(cmbFilter_Priority)) > 0 Then
    strFilter = " and [Priority]='" & Me.cmbFilter_Priority & "'"
End If

If Len(strFilter) > 0 Then
   strFilter = Mid(strFilter, 5)
   Me.sfrmTasks.Form.Filter = strFilter
    Me.sfrmTasks.Form.FilterOn = True
End If
'----------------------------------------------------------------------
Now each combo works independantly from each other.  I select a value from cmbFilter_Status and that works.  Then I want to select another value from cmbFilter_CAT and have that filter the recently filtered records... that is what is not working.  I think we are almost there though.  I did have it working with my crazy chk boxes, but not all of the time as I said before.  This is really weird.  There must be something missing here.  Basically, what I want to be able to do is select different combinations of criteria from 1 or 2 or all of the mentioned combos and have the subform filtered.
Oh, and I also have a button that removes the filters by using DoCmd.ShowAllRecords.  I use this to start over.  However, it doesn't seem to "clear" all of the criteria (weird)....

Thanks for all of your help so far
EDH
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 500 total points
ID: 10697616
Me bad, forgot to concatenate the string :-)

Use:

strFilter = ""
If Len(Nz(Me.cmbFilter_Status)) > 0 Then
    strFilter = strFilter  & " and [Status]='" & Me.cmbFilter_Status & "'"
End If
If Len(Nz(cmbFilter_CAT)) > 0 Then
    strFilter = strFilter  & " and [category]='" & Me.cmbFilter_CAT & "'"
End If
If Len(Nz(cmbFilter_Priority)) > 0 Then
    strFilter = strFilter  & " and [Priority]='" & Me.cmbFilter_Priority & "'"
End If

Now each new IF will add the string when selected.

Nic;o)
0
 

Author Comment

by:edh-corp
ID: 10698504
Hey Nico

That was it :-)

PERFECT!  It works now, exactly like I want it to.  And I learned something new :-)  Thanks for all of your help.  The subform was a great idea.

EDH

And thanks to the other guys that gave me suggestions
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10699166
Glad we could help :-)

Success with the application !

Nic;o)
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question