Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-03-27
11
Medium Priority
?
2,496 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

604 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