Hi, I have several combos in a form which individually filter the records in that form (continuous). The filtering process is done in a private sub filterList() and all the combos AfterUpdate event calls that procedure. Example: -
Private Sub cbo_filterDate_AfterUpdate()
-
filterList
-
End Sub
-
-
Private Sub cbo_filterSupp_AfterUpdate()
-
filterList
-
End Sub
-
-
Private Sub cbo_filterGroup_AfterUpdate()
-
filterList
-
End Sub
-
And so on for all the combos.
I also have command buttons doing same thing for each combo to remove the filter: - Private Sub cmd_removeFilterDate_Click()
-
Me.cbo_filterDate.Value = Null
-
filterList
-
End Sub
-
-
Private Sub cmd_removeFilterSupp_Click()
-
Me.cbo_filterSupp.Value = Null
-
filterList
-
End Sub
-
-
Private Sub cmd_removeFilterGroup_Click()
-
Me.cbo_filterGroup.Value = Null
-
filterList
-
End Sub
-
I was wondering if there is a better, more practical way of doing this. Thought about naming the combos and buttons as cbo_filter1, cbo_filter2 etc and putting in a for - next loop but couldn't figure out where to put that loop in code.
Thanks for any advice
In the property sheet for button change On Click to =clear_filterList() or what ever name you choose.
and for the combo boxes change AfterUpdate to =filterList().
In the button tag property put the name of the combo control the button clears in.
You have to change the subs to functions. -
Private Function filterList()
-
'your filter
-
End Function
-
-
Private Function clear_filterList()
-
Me.Controls(Me.ActiveControl.Tag) = Null
-
filterList
-
End Function
9 1529
I can't really comment on your FilterList sub since I don't know what you have, but since you are wanting it to run when each combo box is changed, you have to call your function from their AfterUpdate event just as you are doing. I don't think that a loop through the controls would benefit you any. Perhaps it would help if you would show us your FilterList sub.
The FilterList sub just changes the record source of the form:
The declarations: - Option Compare Database
-
Private Const strRowSrc As String = "SELECT * FROM qry_purchases WHERE qry_purchases.TtlQty > 0"
-
Private Const strOrderBy As String = " ORDER BY qry_purchases.purchaseDate, qry_purchases.purchGroup, qry_purchases.purchaseSubID;"
The FilterList sub: - Private Sub filterList()
-
Dim myRowSrc As String
-
Dim rsFilterDate, rsFilterSupp, rsFilterGroup, rsfilterType, rsfilterMaterial, rsfilterCode As String
-
-
'check filterdate
-
'format string to add the # delimiters and get the right format.
-
Const conJetDate = "\#mm\/dd\/yyyy\#"
-
If Not IsNull(Me.cbo_filterDate) Or Me.cbo_filterDate <> "" Then
-
rsFilterDate = " AND qry_purchases.purchaseDate = " & Format(Me.cbo_filterDate, conJetDate)
-
End If
-
'check filterSupp
-
If Not IsNull(Me.cbo_filterSupp) Or Me.cbo_filterSupp <> "" Then
-
rsFilterSupp = " AND qry_purchases.supplierName='" & Me.cbo_filterSupp & "'"
-
End If
-
'check filterGroup
-
If Not IsNull(Me.cbo_filterGroup) Or Me.cbo_filterGroup <> "" Then
-
rsFilterGroup = " AND qry_purchases.purchGroup='" & Me.cbo_filterGroup & "'"
-
End If
-
-
'check filterType
-
If Not IsNull(Me.cbo_filterType) Or Me.cbo_filterType <> "" Then
-
rsfilterType = " AND qry_purchases.purchType='" & Me.cbo_filterType & "'"
-
End If
-
'check filterType
-
If Not IsNull(Me.cbo_filterType) Or Me.cbo_filterType <> "" Then
-
rsfilterType = " AND qry_purchases.purchType='" & Me.cbo_filterType & "'"
-
End If
-
'check filterMaterial
-
If Not IsNull(Me.cbo_filterMaterial) Or Me.cbo_filterMaterial <> "" Then
-
rsfilterMaterial = " AND qry_purchases.purchMaterial='" & Me.cbo_filterMaterial & "'"
-
End If
-
'check filterCode
-
If Not IsNull(Me.cbo_filterCode) Or Me.cbo_filterCode <> "" Then
-
rsfilterCode = " AND qry_purchases.purchCode='" & Me.cbo_filterCode & "'"
-
End If
-
-
' Filter The List
-
myRowSrc = strRowSrc & rsFilterDate & rsFilterSupp & rsFilterGroup & rsfilterType & rsfilterMaterial & rsfilterCode
-
myRowSrc = myRowSrc & strOrderBy
-
-
Me.RecordSource = myRowSrc
-
Me.Requery
-
-
End Sub
-
Considering that you would have to use a select case statement to test for the control name inside the loop, I don't think that you will gain anything by looping through the controls.
Guess you're indeed "stuck" with this solution for this way of filtering.
Personally I don't code this, as I instruct my users to use the right-click pop-up menu. This filtering is more versatile as it will allow wild characters in the filter and automatically adds the AND relation.
With the QBF you could even use an OR relationship, but that's for most users "a bridge too far".
For some flexible solutions I use the Me.Subform.Filter value to show on the top of the filtered subform and I allow the user to modify and/or store the filter under a name and let them select such a filter from a combobox. Makes it easy when they have many regular filterings.
Idea ?
Nic;o)
I'd think the select case and loop would not shorten the code either..
Nico's solution does not seem to simplify the code either. I was just curious if I was taking the long and wrong way doing the same thing for the same event for many controls but seems it's the only way.
Thanks a lot for your interest and assistance
Hmm, "Nico's solution does not seem to simplify the code either."
That's true, it eliminates the code completely :-)
Nic;o)
In the property sheet for button change On Click to =clear_filterList() or what ever name you choose.
and for the combo boxes change AfterUpdate to =filterList().
In the button tag property put the name of the combo control the button clears in.
You have to change the subs to functions. -
Private Function filterList()
-
'your filter
-
End Function
-
-
Private Function clear_filterList()
-
Me.Controls(Me.ActiveControl.Tag) = Null
-
filterList
-
End Function
Hennepin: This seems much tidier and simpler for the clear filter buttons. I'll give it a try.
For the combos, I suppose you mean change the "Private Sub filterList()" to "Private Function filterList()" and change the "[Event Procedure]" to "=FilterList()" on the property window.
Sounds logical, will try when I get to my computer
Works absolutely fine... leaned up lots of code too.. Thanks a lot Hennepin.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Gianmaria |
last post by:
hi,
that's my problem. I have an object and this have a onConnectionChanged
event. and when i use it on ny form i have also my event handler.
Now, my user can add many of this objects to a list...
|
by: Hazz |
last post by:
I am just beginning to design a Treeview display (winforms) for wine
regions. Problem. Some wine growing regions belong to two counties. Eg.
Carneros is in both Napa and Sonoma Counties. Although...
|
by: feng |
last post by:
Hi,
I know this might not be a question for this group but I
would give it a try anyway as I couldn't find a better
group for my question.
I have noticed that there are a lot of articles out...
|
by: Ed Jay |
last post by:
On a multi-textbox form, linked to an external js, I use onBlur to call:
function chkNum(cellname) {
var str = document.getElementById(cellname).value.toString(10);
if (str < 28 || str > 36)...
|
by: sfeher |
last post by:
Hi All,
I need to synchronize on multiple event notifications.
After loading a number of scripts in my page ( using
document.createElement('script') / appendChild ) I get a notification
for...
|
by: AzizMandar |
last post by:
C++ Event Coding Questions
I have done some simple programs in C++ and read a lot of good C++
books (Including The C++ Programing Language, and C++ Primer) I am
trying to understand and...
|
by: John |
last post by:
Is there an easy way to add the same code (a function) to all of my fields'
GotFocus without having to do it manually?
thanks,
john
|
by: charlieopenshaw |
last post by:
I have a form listview and have used the MouseDown event to launch a
modal dialog if a particular cell is clicked.
This works fine but upon closing the dialog I am left with a dotted
rectangle...
|
by: TazaTek |
last post by:
Hi,
I'm wanting to create a form that will take in some information, and
return 2 (or more) pages in new tabs. I didn't know if this would be
a better server side, or client side application. ...
|
by: ilikebirds |
last post by:
I am looking for a way to have one combo lookup drop down to autopopulate with multiple items based off another combo lookup. I've tried
Me!rc1 = DLookup("", "", "Reject_Reason_1='" &...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |