View all – undo combo box filter

[kay]

I have created combo box such that when you select the value from drop down
list, it filters the matched field value in sub form and display the related
rows.
How do I come back to view all data. I mean it should show all data on sub
form.

Much appreciate your help! Thanks.

 

[Marshall Barton]

I have created combo box such that when you select the value from drop down
list, it filters the matched field value in sub form and display the related
rows.
How do I come back to view all data. I mean it should show all data on sub
form.

Try setting the combo box's value to Null and calling it
AfterUpdate event procedure:

Me.thecombobox = Null
thecombobox_AfterUpdate

 

[kay]

No that's not working, when I set the code on combox after update, i can not
select the value from combox to filter the records but it does not unfilter
the data on subform.
keep trying, I tried .filteron = false but even thats not working.

thanks

 

[Marshall Barton]

I might be able to make another try if you provided enough
information about what you are doing. Putting the code I
suggested in the combo box's own AfterUpdate event is
certainly not going to work, so what do you expect users to
do to clear the filter?

I guessed that you were using the subform's Filter property
to filter the subform. If you are using a criteria in the
subform's record source query, what is the query's SQL?

Maybe there is some other code in the combo box's
AfterUpdate event that is getting in the way. If you would
post a Copy/Paste of the AfterUpdate event procedure, maybe
I will be able to spot a useful clue.

 

[kay]

I have created mainform –caption “mainform†and in that Subform – caption
“subform1â€
In mainform I have created combo box –name “cmdException†for ExceptionType
and the record source is “Select ExceptionType from tblExceptionâ€

The tblException table is unique table for Exception Types. So it has ID
(PK) and Exception Type(Desc of Exception).

In subform Record source: “Select * from tblCustomerâ€
This table has ExceptionType field (FK of Unique tblException tbale)

So when I select value form dropdown combo box, it filters the match
exception type value in tblCustomer table and shows the matched rows.

So this is working fine - When I select another value form drop down combo
box , it filters accordingly.
But now I am working on How to come back to show all rows.
Should I created button on main form and write some code to show all value
in subform or ????.

I did put me.cmdException = Null after then tried even procedure on subform
me.filteron = false but as that was not working I have removed all even
procedures.


thanks for your quick replies.

I might be able to make another try if you provided enough
information about what you are doing. Putting the code I
suggested in the combo box's own AfterUpdate event is
certainly not going to work, so what do you expect users to
do to clear the filter?

I guessed that you were using the subform's Filter property
to filter the subform. If you are using a criteria in the
subform's record source query, what is the query's SQL?

Maybe there is some other code in the combo box's
AfterUpdate event that is getting in the way. If you would
post a Copy/Paste of the AfterUpdate event procedure, maybe
I will be able to spot a useful clue.
--
Marsh
MVP [MS Access]

No that's not working, when I set the code on combox after update, i can not
select the value from combox to filter the records but it does not unfilter
the data on subform.
keep trying, I tried .filteron = false but even thats not working.

.

 

[Marshall Barton]

You still did not explain how the subform filtering is done.
The only clue is that you said "I have removed all even
procedures", so I will guess that the subform's record
source is a query with a criteria like:
Forms!mainform.cmdException
on the ExceptionType field.

If that is what you are doing, then you are not using the
subform's Filter property as I assumed earlier and my
suggestion does not apply to your situation. In this case
change the subform's record source query to use this kind of
criteria:
Forms!mainform.cmdException OR Forms!mainform.cmdException
Is Null

Then you can get the subform to display all the records by
highlighting the value displayed in the combo box and
hitting the Delete key or by adding a command button with
the code I posted earlier in the button's Click event
procedure.

Either way, what you have should not work after you "removed
all even procedures". The combo box's AfterUpdate event
procedure should contain at least a line like:
Me.[subform control].Form.Requery

The subform **control** name may or may not be "subform1"
and it may or may not be the same as the name of the form
object displayed in the subform control. (A form's Caption
has nothing to do with anything beyond what is displayed in
the form's title bar.)

 

[Dale Fye]

Kay,

I usually modify the SQL for the combo box to include an option which
includes all records. To do this, I maintain a table (tblNumbers) which I
use for a variety of purposes, which contains a single field (intNumber) and
ten records (with values from 0 to 9). Then I do something like:

SELECT ID, ExceptionType
FROM (
SELECT ID, ExceptionType
FROM tblException
UNION
SELECT intNumber, "All Types" as [ExceptionType]
FROM tblNumbers
WHERE intNumber = 0
) as Temp
ORDER BY ID

Then, modify the query for your subform so that it looks something like:

SELECT * FROM tblCustomer
WHERE ExceptionTypeID = Forms!formName.cboException
OR Forms!formName.cboException = 0

Then, in the afterupdate event of cboException, requery the subform:

Private Sub cboException_AfterUpdate

me.subFormControlName.Form.Requery

End Sub

----
Dale

I have created mainform –caption “mainform†and in that Subform – caption
“subform1â€
In mainform I have created combo box –name “cmdException†for ExceptionType
and the record source is “Select ExceptionType from tblExceptionâ€

The tblException table is unique table for Exception Types. So it has ID
(PK) and Exception Type(Desc of Exception).

In subform Record source: “Select * from tblCustomerâ€
This table has ExceptionType field (FK of Unique tblException tbale)

So when I select value form dropdown combo box, it filters the match
exception type value in tblCustomer table and shows the matched rows.

So this is working fine - When I select another value form drop down combo
box , it filters accordingly.
But now I am working on How to come back to show all rows.
Should I created button on main form and write some code to show all value
in subform or ????.

I did put me.cmdException = Null after then tried even procedure on subform
me.filteron = false but as that was not working I have removed all even
procedures.


thanks for your quick replies.

I might be able to make another try if you provided enough
information about what you are doing. Putting the code I
suggested in the combo box's own AfterUpdate event is
certainly not going to work, so what do you expect users to
do to clear the filter?

I guessed that you were using the subform's Filter property
to filter the subform. If you are using a criteria in the
subform's record source query, what is the query's SQL?

Maybe there is some other code in the combo box's
AfterUpdate event that is getting in the way. If you would
post a Copy/Paste of the AfterUpdate event procedure, maybe
I will be able to spot a useful clue.
--
Marsh
MVP [MS Access]

No that's not working, when I set the code on combox after update, i can not
select the value from combox to filter the records but it does not unfilter
the data on subform.
keep trying, I tried .filteron = false but even thats not working.


:
kay wrote:
I have created combo box such that when you select the value from drop down
list, it filters the matched field value in sub form and display the related
rows.
How do I come back to view all data. I mean it should show all data on sub
form.


Try setting the combo box's value to Null and calling it
AfterUpdate event procedure:

Me.thecombobox = Null
thecombobox_AfterUpdate

.