Filter not working, easy I think, please help

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


"Robert Morley" wrote in message
...
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

"Gina Whipp" wrote in message
...
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


"Robert Morley" wrote in message
...
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.

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200703/1

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

--
Gina Whipp

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


"AccessVandal via AccessMonster.com" u18947@uwe wrote in message
news:6f0c00d99b7f0@uwe...
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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200703/1

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

--
Message posted via http://www.accessmonster.com

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

--
Gina Whipp

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


"AccessVandal via AccessMonster.com" u18947@uwe wrote in message
news:6f0cb34b9072d@uwe...
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

--
Message posted via http://www.accessmonster.com

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

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


"AccessVandal via AccessMonster.com" u18947@uwe wrote in message
news:6f0cb34b9072d@uwe...
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

--
Message posted via http://www.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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200703/1

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


"AccessVandal via AccessMonster.com" u18947@uwe wrote in message
news:6f0d270dc2711@uwe...
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200703/1

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200703/1

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


"AccessVandal via AccessMonster.com" u18947@uwe wrote in message
news:6f0da59e90ed7@uwe...
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...

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200703/1