Hello fellow UA's

How can I filter a form based on the value selected in the 2 Listboxes and 1 option group(toggle button) ?

Thanks!

put comboboxes, textboxes, listboxes, etc on the form (i put then in the header). Assign this to the AfterUpdate event of each one...

=SetFormFilter()

then put this code behind the form





me.controlname_for_number refers to the NAME property of a control on the form you are behind (Me. represents the form -- kinda like "me" for me is not "me" for you (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif) )

delimiters are used for data that is not a number
quote marks ' or " for text
number signs # for dates

mfilter is a string that is being built for each condition -- but if nothing is specified in the filter control (IsNull), then that addition to the filter string is skipped.

finally, when the filter string is done, it is applied to your form.

That means that as you flip through records, ONLY records matching that filter will show

Then, put another command button on the form

Name --> btnShowAll
OnClick --> [Event Procedure]



a requery of the form will also reset the record pointer back to the first record.

If you want to capture the record you are on and set it back, you can do this:

if your listboxes are single-select, you can use the code as written above. If your list boxes are multi-select, you will need to loop through them and construct an IN clause.

For your option group, you will need to test its value and, build the filter string according to what was chosen.


************
Multi-Select Listbox




****
Option Group

Hi Crystal,
Been watching your reply posts and saving several of them for later use.
I admire you spending so much time helping others.
For us beginners, it means so much.
Thanks,
AP

what a nice thing to say, thank you (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif) happy to help

Crystal,

Ditto to AP's comments.

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/notworthy.gif)

Thanks for all the help you do for others.

Thanks, Boyd!

back at ya ... I have learned some neat tricks from your code

Thanks for the help!
(IMG:http://www.utteraccess.com/forum/style_emoticons/default/notworthy.gif) (IMG:http://www.utteraccess.com/forum/style_emoticons/default/notworthy.gif) (IMG:http://www.utteraccess.com/forum/style_emoticons/default/notworthy.gif)

you're welcome (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

...just have to point out a couple things

First, there is an error in the code

mFilter = "AND [fieldname] IN (" & mFilterList & ") "
SHOULD NOT HAVE "AND" IN IT since the statement above puts it in if necessary --> change to



also

it is a good idea to output the constructed filter to the debug window before it is actually applied -- at least while developing -- this allows you to open the debug window and make sure your filters are correctly constructed and, if there is a problem -- like the filter is not applied -- you can examine the string and correct the problem



press CTRL-G to Goto the debug window

---

Whenever you write or paste code, your should ALWAYS compile it before you attempt to run it.

from the menu: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

it is ALSO a good idea to include an error handler in your code

ERROR HANDLER



if, during execution, an error flag is raised, execution will go to the error handler, which is denoted with a line label

procedurename_Error:

is a line label -- you can change the name before the colon -- but you cannot have the same line label twice in the same module sheet. So, in this case, your procedure name is "SetFormFilter", so your line label would be

SetFormFilter_error:

and your message mox statement would say
MsgBox Err.Description, , "ERROR " & Err.Number & " SetFormFilter"

and the line at the top to initalize the error handler would say
On Error GoTo SetFormFilter_Error

once the execution goes to the error handler, a message box is issued describing the problem, and then the code is STOPped with

Stop

Press the F8 key ( which is the shortcut key to single-step through code) once to go to the next line

Resume

tells the process to go back to the line that caused the problem.

press F8 again to go to the actual offending line. In this case, it would probably be

me.filter = mfilter

and that would be because something is wrong with the filter string

at that point, you could open the Debug Window and examine the string.

You can then fix the problem, save your code, and then resume the code at the top where mFilter is set to ""
(right-click on the line you want to resume with
mFilter = ""
and choose "Set Next Statement" from the shortcut menu)

then, either F5 to RUN (from the menu bar: Run, Run)
or F8 to continue stepping through code

OR
to stop the procedure
(from the menu bar: Run, Reset)

if, at any point during execution, you want to STOP the code, press CTRL-BREAK -- you may have to hold the Control key and repeatedly press the Pause/Break key until Access pays attention to the keyboard. In your code, you can put in a line that MAKES it pay attention to the keyboard:

DoEvents

If I have a loop, I often put DoEvents into the loop while I am developing. Sometimes DoEvents is necessary to make changes "stick" -- like if you are writing to a form from code that is not behind the form, or changing data with an SQL statement.

Just wanted to say thank also Crystal, I came searching for help with combining options with a combo box selection and I think this would work great.
You put a lot into extra effort in your responses and us newbies are very thankful for your help.
Mary

thanks, Mary (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif) ... Welcome to Utter Access

Crystal,

Just wanted to chime in -- I am a real Access newbie and finding this post has made my filtering so much easier-- my heartfelt thanks!

Best regards,
Nina

thank you, Nina (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif) happy to help

Welcome to Utter Access

I want to bring up this old post, particularly Crystals first response. I am having trouble understanding.

If you put =SetFormFilter in the After_Update event of each control you want to filter by, then it would seem you would have to have code in Function SetFormFilter() to process every one of the control names. Or you could pass the control name to the function, though I don't know how to do that. Can anyone shed a little light on this?

I was told I should hard code the control name after the Me. If I run the function in the after_update event of each control, it will have no way of knowing which control fired it - I think.

Let's say I have 3 comboboxes, ComboA, ComboB, and ComboC and I want to filter the form on all 3. Is it possible to add to the filter with each subsequent Combobox selection? That is, make a selection from ComboA which filters it on ColumnA in the dataset, then ColumnB which further filters it, and then ColumnC, which narrows it down yet further. OR, I could have the user make his selections, and then build the multiple-criteria filter in one shot after a button is pushed. But I am not sure of the synatax for that, either.

Hi Duane,

"f I run the function in the after_update event of each control, it will have no way of knowing which control fired it - I think."

you CAN know that information ... with Me.ActiveControl.Name
(not that it really matters much)

"Is it possible to add to the filter with each subsequent Combobox selection? "

if your combos are cascaded such that, if C is filled then A and B don't matter, or if B is filled then A doesn't matter, you could do something like this:



WHERE
comboA, comboB, and comboC are the control Names of the respective controls


if the data type of your fields are not numbers, you will need to concatenate delimiters

WHERE
text_field a field name in your RecordSource whose data type is text
date_field a field name in your RecordSource whose data type is date/time

So, on that code at the very top of this post, I suppose you could make it into a function and pass Me.ActiveControl.Name to it and maybe programmatically determine whether it was a data or number or text and then apply the filter accordingly?

Hi Duane,

no, you would not need to pass the Activecontrol. In a general procedure, it is:
Screen.ActiveControl.Name

I would not recommend putting filtering stuff into a general module though, use CBF (code behind the form) because, in addition to data type(s), you need to get the fieldname(s) right

I must be really missing something here. I will quote the code at the beginning:



OK. We put this code behind the form. Let's say I have a textbox called MyDCWtext, MyDCWnumber, and MyDCWdate, just to make sure I don't mis-interpret your naming conventions here. And let's say I am filtering on these 3 textboxes on the same form with this one function behind it. You say to put =SetFormFilter in the After_Update event of each of these 3. Now, it seems to me, that you would have to either 1) change the code from me.controlname_for_text to Screen.ActiveControl, or 2) hard-code one function of a different name for each of these textboxes and hardcode the name of the textbox in the function (ie: Me.MyDCWtext instead of me.controlname_for_text), or 3) have a decision loop inside the function to determine which textbox was being processed at the time (ie, something like If screen.activecontrol.name = MyDCWtext then .... else me.activecontrol.name = Me.MyDCWnumber then ...., etc.).

So what gives here? I am afraid I just don't understand. Thanks for your patience and replies here.