Solved

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

Posted on 2004-03-27
11
2,482 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
  • 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now