 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / March 2007Filter not working, easy I think, please help
Hey All I have 3 combo oboxes 1 = BrokerID which is registered once the Broker Logs On 2 = cboSortBy which is Listing Type (E, O, P, etc...) 3 = cboStateID is the third. What I want to happen is if cboSortBy is empty is for it to look just at 1 and itself(3) to filter by. What does happen is I get ALL the states ignoring even the BrokerID. Can someone help me as to what I am doing wrong? Dim fltrcriteria As String fltrcriteria = "" Me.cboListerName = "" Me.cboPhone = "" Me.cboFranchiseID = "" If Me.cboState = "ALL" Then fltrcriteria = "[btBrokerID] = Forms![frmLogOn]![cboLoginID]" Me.cboSortBy = "" If Me.cboSortBy = "" Or IsNull([cboSortBy]) Then fltrcriteria = "[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = Forms![frmLogOn]![cboLoginID]" Else fltrcriteria = "[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = Forms![frmLogOn]![cboLoginID]" & " And [llListingTypeID] = '" & cboSortBy & "'" End If End If Me.Filter = fltrcriteria Me.FilterOn = True Me.Form.AllowAdditions = False Thanks, Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II First, in your second If statement, you're missing "Me." in front of [cboSortBy] in the IsNull call; same thing at the end of the filter line in the Else clause. Probably not a big deal, but sometimes it can really cause problems. Second, I notice in your second If statement, you've only got Me.cboState instead of Me.cboStateID. Is that the source of the problem? Finally, throughout your filters, you're using cboLoginID, but you told us that combo box number one was called BrokerID. Which one's correct? Rob > Hey All > [quoted text clipped - 38 lines] > "I feel I have been denied critical, need to know, information!" - Tremors > II Replies in-line > First, in your second If statement, you're missing "Me." in front of > [cboSortBy] in the IsNull call; same thing at the end of the filter line > in the Else clause. Probably not a big deal, but sometimes it can really > cause problems. **** Never had a problem before but I fixed no change > Second, I notice in your second If statement, you've only got Me.cboState > instead of Me.cboStateID. Is that the source of the problem? **** Me.cboState is the name of the combo box, llStateID is the name of the field > Finally, throughout your filters, you're using cboLoginID, but you told us > that combo box number one was called BrokerID. Which one's correct? ****The BrokerID is not called BrokerID. The cboLoginID is a field on frmLogOn which is set to Visible = False. I must retain which BrokerID so the form filters only the Brokers' listings. My problem is if cboSortBy is null then the filter cboState is run it just causes the form to go back to ALL or nothing. Hope that better explains it. > Rob > [quoted text clipped - 40 lines] >> "I feel I have been denied critical, need to know, information!" - >> Tremors II Ummm...wait a minute here...it's your If statements that are the problem. In your first If statement, you check if cboState = "ALL". Then, INSIDE that, you check if cboState = "". Well obviously, it can't be, so that filter is never being applied. Also, you can't check for Null and a legitimate value on the same line...if cboState was Null, then the check if it's equal to "" would give you an error. I think what you probably wanted was this: Dim fltrcriteria As String fltrcriteria = "" Me.cboListerName = "" Me.cboPhone = "" Me.cboFranchiseID = "" If Me.cboState = "ALL" Then fltrcriteria = "[btBrokerID] = Forms![frmLogOn]![cboLoginID]" Me.cboSortBy = "" ElseIf Nz(Me.cboSortBy) = "" Then fltrcriteria = "[llStateID] = '" & Me.cboState & "' And [btBrokerID] = Forms![frmLogOn]![cboLoginID]" Else fltrcriteria = "[llStateID] = '" & Me.cboState & "' And [btBrokerID] = Forms![frmLogOn]![cboLoginID] And [llListingTypeID] = '" & cboSortBy & "'" End If Me.Filter = fltrcriteria Me.FilterOn = True Me.Form.AllowAdditions = False Rob Sorry, my bad, I misread the code. Just ignore my previous post, for the most part, though the change to using Nz() still applies. I still think there's SOMETHING wrong with your If statements, though, as you have the following: Me.cboSortBy = "" If Me.cboSortBy = "" ... I'm pretty sure that's not how you want it. Rob No what I want is if there is nothing in the Me.cboSortBy then just filter on the other 2.  SignatureGina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II > Sorry, my bad, I misread the code. Just ignore my previous post, for the > most part, though the change to using Nz() still applies. [quoted text clipped - 8 lines] > > Rob Robert, This works except when I switch cboState back to "ALL" the form goes blank, wha't up with that? Even got rid of the Me.cboSortBy = "" and the form still goes blank. Dim fltrcriteria As String fltrcriteria = "" Me.cboListerName = "" Me.cboPhone = "" Me.cboFranchiseID = "" If Me.cboState = "ALL" Then fltrcriteria = "[btBrokerID] = Forms![frmLogOn]![cboLoginID]" Me.cboSortBy = "" End If If IsNull(Me.cboSortBy) Then fltrcriteria = "[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = Forms![frmLogOn]![cboLoginID]" End If If Not IsNull(Me.cboSortBy) Or Me.cboSortBy <> "ALL" Then fltrcriteria = "[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = Forms![frmLogOn]![cboLoginID]" & " And [llListingTypeID] = '" & Me.cboSortBy & "'" End If Me.Filter = fltrcriteria Me.FilterOn = True Me.Form.AllowAdditions = False > Sorry, my bad, I misread the code. Just ignore my previous post, for the > most part, though the change to using Nz() still applies. [quoted text clipped - 8 lines] > > Rob In your last If statement, I believe it should be an "AND", not an "OR". Otherwise, the code looks pretty good. The code below is what I would actually use for the If statements, but other than the AND/OR thing, yours should work as is. The advantage of doing your code this way is that you can easily tell which condition will end up being used: it checks first for cboSortBy to be blank or Null (the Nz() handles the Null value), then if it's not, it checks for cboState = "ALL", then if it's not, it does the last one. With your code, fltrcriteria can be changed more than once, and it becomes more difficult to figure out which one was used in the end. If Nz(Me.cboSortBy)="" Then fltrcriteria = "[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = Forms![frmLogOn]![cboLoginID]" ElseIf Me.cboState = "ALL" Then fltrcriteria = "[btBrokerID] = Forms![frmLogOn]![cboLoginID]" Me.cboSortBy = "" 'Or NULL? Else fltrcriteria = "[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = Forms![frmLogOn]![cboLoginID]" & " And [llListingTypeID] = '" & Me.cboSortBy & "'" End If ...etc. Robert, The only thing that still doesn't work is if select a state to filter by and then go back to ALL, it blanks out the form. SignatureGina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II > In your last If statement, I believe it should be an "AND", not an "OR". > Otherwise, the code looks pretty good. The code below is what I would [quoted text clipped - 19 lines] > > ...etc. Not sure what's causing that. The only thing I can think of is try setting either setting Me.FilterOn = False then set it back to True again, or maybe after filtering, do a Me.Requery. Rob > Robert, > [quoted text clipped - 25 lines] >> >> ...etc. Tried both neither stopped the form from blanking out... really strange because it looks perfect. SignatureGina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II > Not sure what's causing that. The only thing I can think of is try > setting either setting Me.FilterOn = False then set it back to True again, [quoted text clipped - 31 lines] >>> >>> ...etc. Hi Gina, It's important that you need to understand your own code. What i see here is, the form filtering a text which will cause a blank record that that form. This is your code. ------------------------------------------------------------------------------ ------------------ If Me.cboState = "ALL" Then fltrcriteria = "[btBrokerID] = Forms![frmLogOn]![cboLoginID]" Me.cboSortBy = "" End If ------------------------------------------------------------------------------ ------------------ Use the immediate window of that VBA editor to check the results. If Me.cboState = "ALL" Then fltrcriteria = "[btBrokerID] = Forms![frmLogOn]![cboLoginID]" Debug.Print fltcriteria ' add this to check the results (it's a string) Me.cboSortBy = "" End If The correct code is, (and LoginID datatype is number.) If Me.cboState = "ALL" Then fltrcriteria = "[btBrokerID] = " & Forms![frmLogOn]![cboLoginID] Debug.Print fltrcriteria ' you can compare the results here Me.cboSortBy = "" End If Note: If "Forms![frmLogOn]![cboLoginID]" doesn't work, try "Forms!frmLogOn! cboLoginID". This form must be running/open. Must not be in design mode. Still no go... form still blanks out but only on the ALL selection. And yes you are right it is numeric but still nets the same results SignatureGina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II > Hi Gina, > [quoted text clipped - 35 lines] > cboLoginID". > This form must be running/open. Must not be in design mode. Hi Gina, What is the result of "fltrcriteria" from the Immediate Window? Output the result here for us to see. >Gina Whipp wrote: >Still no go... form still blanks out but only on the ALL selection. And yes >you are right it is numeric but still nets the same results There was nothing... did I do something wrong? I copied and pasted the code and pressed enter and nothing happened SignatureGina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II > Hi Gina, > [quoted text clipped - 6 lines] >>yes >>you are right it is numeric but still nets the same results This seems to be working now... Hopefully, it wasn't a fluke, if it was I'll post back. If Me.cboState = "ALL" And IsNull(Me.cboSortBy) Then fltrcriteria = "[btBrokerID] = " & Forms!frmLogOn!cboLoginID End If If IsNull(Me.cboSortBy) And Me.cboState <> "ALL" Then fltrcriteria = "[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = Forms![frmLogOn]![cboLoginID]" End If If Not IsNull(Me.cboSortBy) Then fltrcriteria = "[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = Forms![frmLogOn]![cboLoginID]" & " And [llListingTypeID] = '" & Me.cboSortBy & "'" End If SignatureGina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II > Hi Gina, > [quoted text clipped - 6 lines] >>yes >>you are right it is numeric but still nets the same results Hi Gina, There still some mistakes in your last two If Then statements. Look at it and compare. If IsNull(Me.cboSortBy) And Me.cboState <> "ALL" Then fltrcriteria = "[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = " & Forms!frmLogOn!cboLoginID End If If Not IsNull(Me.cboSortBy) Then fltrcriteria = "[llStateID] = '" & Me.cboState & "'" & " And [btBrokerID] = " & Forms!frmLogOn!cboLoginID & " And [llListingTypeID] = '" & Me.cboSortBy & "'" End If In your VBA Editor, type Ctrl + G to view the Immediate Window. or in the menu - View - Immediate Window. >Gina Whipp wrote: >This seems to be working now... Hopefully, it wasn't a fluke, if it was [quoted text clipped - 14 lines] >& Me.cboSortBy & "'" > End If But they work (which when you think about it they shouldn't)... I'll fix because they ARE numeric but they all work now. I know how to get to the immediate window but on the previous code, nothing happened... SignatureGina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II > Hi Gina, > [quoted text clipped - 36 lines] >>& Me.cboSortBy & "'" >> End If Hi Gina, The only thing i believe is your naming of the controls. Take a look at your previous post.... >>>>3 = cboStateID is the third. Is the control name correct? as to... >>>>If Me.cboState = "ALL" Then See the "Me.cboState"? and another..... >>>>fltrcriteria = "[llStateID] = '" & Me.cboState & "'" & " And See the "[llStateID]"? By default, Access base on your RecordSource Query, so by default the Control Name is "llStateID". (if you use wizard or the field box to create them). As to why the Immediate Window is blank, there are many reasons. Can't tell with seeing your database. Hope you see the light. >Gina Whipp wrote: >But they work (which when you think about it they shouldn't)... I'll fix >because they ARE numeric but they all work now. > >I know how to get to the immediate window but on the previous code, nothing >happened... No... typo cboStateID, it's actually cboState. I changed to the Me. as per a suggestion but hardly ever use and never had a problem. llStateID is the control source and is correctly named. (Me.cboState is unbound.) The problem was the line that said Me.cboSortBy = "", once I deleted that all was fine. If you look at the first code I sent and what I ended up with you will see what I mean. I was asking if it was Null then telling it was "" which is what made it go blank. SignatureGina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II > Hi Gina, > [quoted text clipped - 28 lines] >>nothing >>happened... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.