Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2004-03-27
11
Medium Priority
?
2,499 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

580 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