Filter not working, easy I think, please help

[Gina Whipp]

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

 

[Robert Morley]

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

 

[Gina Whipp]

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

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

 

[Robert Morley]

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

 

[Robert Morley]

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

 

[Gina Whipp]

No what I want is if there is nothing in the Me.cboSortBy then just filter
on the other 2.

 

[Gina Whipp]

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

 

[Robert Morley]

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.

 

[Gina Whipp]

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.

 

[Robert Morley]

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,

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.

--
Gina 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
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.

 

[Gina Whipp]

Tried both neither stopped the form from blanking out... really strange
because it looks perfect.

--
Gina 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,
or maybe after filtering, do a Me.Requery.



Rob

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.

--
Gina 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
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.

 

[AccessVandal via AccessMonster.com]

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.

 

[Gina Whipp]

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

 

[AccessVandal via AccessMonster.com]

Hi Gina,

What is the result of "fltrcriteria" from the Immediate Window?

Output the result here for us to see.

 

[Gina Whipp]

There was nothing... did I do something wrong? I copied and pasted the
code and pressed enter and nothing happened

 

[Gina Whipp]

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

 

[AccessVandal via AccessMonster.com]

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
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

 

[Gina Whipp]

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...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

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
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

 

[AccessVandal via AccessMonster.com]

Hi Gina,

The only thing i believe is your naming of the controls. Take a look
at your previous post....
Is the control name correct? as to...
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]

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.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

Hi Gina,

The only thing i believe is your naming of the controls. Take a look
at your previous post....
Is the control name correct? as to...
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...