473,322 Members | 1,403 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Multiple combos event doing same action - Any practical way?

18
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cbo_filterDate_AfterUpdate()
  2.    filterList
  3. End Sub
  4.  
  5. Private Sub cbo_filterSupp_AfterUpdate()
  6.    filterList
  7. End Sub
  8.  
  9. Private Sub cbo_filterGroup_AfterUpdate()
  10.    filterList
  11. End Sub
  12.  
And so on for all the combos.

I also have command buttons doing same thing for each combo to remove the filter:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_removeFilterDate_Click()
  2. Me.cbo_filterDate.Value = Null
  3. filterList
  4. End Sub
  5.  
  6. Private Sub cmd_removeFilterSupp_Click()
  7. Me.cbo_filterSupp.Value = Null
  8. filterList
  9. End Sub
  10.  
  11. Private Sub cmd_removeFilterGroup_Click()
  12. Me.cbo_filterGroup.Value = Null
  13. filterList
  14. End Sub
  15.  
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
Mar 30 '13 #1

✓ answered by Hennepin

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.
Expand|Select|Wrap|Line Numbers
  1. Private Function filterList()
  2.    'your filter   
  3. End Function
  4.  
  5. Private Function clear_filterList()
  6.    Me.Controls(Me.ActiveControl.Tag) = Null
  7.    filterList
  8. End Function

9 1529
Seth Schrock
2,965 Expert 2GB
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.
Mar 30 '13 #2
Celal
18
The FilterList sub just changes the record source of the form:
The declarations:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Private Const strRowSrc As String = "SELECT * FROM qry_purchases WHERE qry_purchases.TtlQty > 0"
  3. Private Const strOrderBy As String = " ORDER BY qry_purchases.purchaseDate, qry_purchases.purchGroup, qry_purchases.purchaseSubID;"
The FilterList sub:

Expand|Select|Wrap|Line Numbers
  1. Private Sub filterList()
  2. Dim myRowSrc As String
  3. Dim rsFilterDate, rsFilterSupp, rsFilterGroup, rsfilterType, rsfilterMaterial, rsfilterCode As String
  4.  
  5. 'check filterdate
  6. 'format string to add the # delimiters and get the right format.
  7. Const conJetDate = "\#mm\/dd\/yyyy\#"
  8.     If Not IsNull(Me.cbo_filterDate) Or Me.cbo_filterDate <> "" Then
  9.         rsFilterDate = " AND qry_purchases.purchaseDate = " & Format(Me.cbo_filterDate, conJetDate)
  10.     End If
  11. 'check filterSupp
  12.     If Not IsNull(Me.cbo_filterSupp) Or Me.cbo_filterSupp <> "" Then
  13.         rsFilterSupp = " AND qry_purchases.supplierName='" & Me.cbo_filterSupp & "'"
  14.     End If
  15. 'check filterGroup
  16.     If Not IsNull(Me.cbo_filterGroup) Or Me.cbo_filterGroup <> "" Then
  17.         rsFilterGroup = " AND qry_purchases.purchGroup='" & Me.cbo_filterGroup & "'"
  18.     End If
  19.  
  20. 'check filterType
  21.     If Not IsNull(Me.cbo_filterType) Or Me.cbo_filterType <> "" Then
  22.         rsfilterType = " AND qry_purchases.purchType='" & Me.cbo_filterType & "'"
  23.     End If
  24. 'check filterType
  25.     If Not IsNull(Me.cbo_filterType) Or Me.cbo_filterType <> "" Then
  26.         rsfilterType = " AND qry_purchases.purchType='" & Me.cbo_filterType & "'"
  27.     End If
  28. 'check filterMaterial
  29.     If Not IsNull(Me.cbo_filterMaterial) Or Me.cbo_filterMaterial <> "" Then
  30.         rsfilterMaterial = " AND qry_purchases.purchMaterial='" & Me.cbo_filterMaterial & "'"
  31.     End If
  32. 'check filterCode
  33.     If Not IsNull(Me.cbo_filterCode) Or Me.cbo_filterCode <> "" Then
  34.         rsfilterCode = " AND qry_purchases.purchCode='" & Me.cbo_filterCode & "'"
  35.     End If
  36.  
  37. ' Filter The List
  38.     myRowSrc = strRowSrc & rsFilterDate & rsFilterSupp & rsFilterGroup & rsfilterType & rsfilterMaterial & rsfilterCode
  39.     myRowSrc = myRowSrc & strOrderBy
  40.  
  41.     Me.RecordSource = myRowSrc
  42.     Me.Requery
  43.  
  44. End Sub
  45.  
Mar 30 '13 #3
Seth Schrock
2,965 Expert 2GB
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.
Mar 30 '13 #4
nico5038
3,080 Expert 2GB
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)
Mar 31 '13 #5
Celal
18
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
Mar 31 '13 #6
nico5038
3,080 Expert 2GB
Hmm, "Nico's solution does not seem to simplify the code either."

That's true, it eliminates the code completely :-)

Nic;o)
Mar 31 '13 #7
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.
Expand|Select|Wrap|Line Numbers
  1. Private Function filterList()
  2.    'your filter   
  3. End Function
  4.  
  5. Private Function clear_filterList()
  6.    Me.Controls(Me.ActiveControl.Tag) = Null
  7.    filterList
  8. End Function
Apr 1 '13 #8
Celal
18
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
Apr 2 '13 #9
Celal
18
Works absolutely fine... leaned up lots of code too.. Thanks a lot Hennepin.
Apr 2 '13 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

1
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...
3
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...
1
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...
14
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)...
1
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...
4
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...
7
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
2
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...
4
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. ...
3
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='" &...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
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...
0
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...
1
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)...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
0
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
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.