?
Solved

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

Posted on 2004-03-27
11
Medium Priority
?
2,491 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 2000 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

764 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