ms access filter subform based on multiple checkboxes (1 Viewer)

[Kobs]

I have a ms access database with table recordsource subform. I need to filter the subform based on 5 checkboxes. If ticked the checkbox must filter subform ( field like "01*") for checkbox1 (field like "02*") for checkbox 2 and so on... If unticked the filter willI need to have exclude the field values. I need the checkboxes to filter the subform simultaneously. The field is text data type. Thank you.

 

[strive4peace]

hi Kobs,

Welcome!

put this in the [Event Procedure] code of the AfterUpdate event of each checkbox filter control:
call SetMyFormFilter

also in code behind the form is this:

Private Function SetMyFormFilter()
  'crystal (strive4peace)

   dim vFilter as variant _
      , i as integer

   'initialize value of filter
   vFilter = null

   'use controls on the form
   With Me
       '1
       vFilter = (vFilter + " AND ")  'obviously first time, this won't change -- included it for consistency
       if me.checkbox1 = false then
         vFilter = vFilter  & " NOT "
       end if
       vFilter = vFilter  & " ([fieldname1] like ""01*"")"
       '2
       vFilter = (vFilter + " AND ")
       if me.checkbox2 = false then
         vFilter = vFilter  & " NOT "
       end if
       vFilter = vFilter  & " ([fieldname2] like ""02*"")"
      ' ... and so on

      'set the filter for the form and turn it on
      .Filter = vFilter
      .FilterOn = true

   End With

End Function

WHERE
checkbox1 is the name of the first checkbox, checkbox2 is second, and so on
fieldname1 is the name of the first field, field2 is second, and so on

You didn't mention if the check boxes have a triple-state and can be null. This assumes they are True or False

 

[Kobs]

thanks. do you mean the whole code in each checkbox control?

 

[strive4peace]

oops, sorry Kobs -- accidentally deleted a couple lines! I edited my post and put them back!

put this in the [Event Procedure] code of the AfterUpdate event of each checkbox filter control:
call SetMyFormFilter

 

[Kobs]

what do you mean by "also in code behind the form is this:"? im new to ms access. sorry.

 

[strive4peace]

no problem, Kobs! Here is some reading that can help you:

Access Basics
http://www.accessmvp.com/strive4peace
Free 100-page book that covers essentials in Access

Learn VBA
http://www.accessmvp.com/strive4peace/VBA.htm

to answer your question:
along with your form, there is also a page for VBA code, called a "module". This is also called "code behind form" or CBF. When you're in the design view of your form, in the Design ribbon tab, on the right, you'll see a command called View Code. Click this and you see the code behind the form

~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions or operating environment, or modify objects with code behind them, you should always compile and save before executing any code.

from the menu in a VBE (module) window: Debug, Compile [the name of your project]
(Alt-F11 to switch to the code window)

Fix any errors on the highlighted lines.
Add needed references, remove unnecessary references, resolve missing references
(from the menu: Tools, References...)

keep compiling until nothing happens (this is good!) -- then Save

also be sure, before compiling, to use Option Explicit at the very TOP of each module so variables that are not declared or are misspelled will be picked up by the compiler as well as many other errors than can be fixed before running.

~~~~~~~~~ Add Option Explicit ~~~~~~~~~

if the top of your module does not have a statement that says Option Explicit, then add this:

Option Explicit  ' require variable declaration

If this was not done when the code was written, you will may need to DIM some variables -- it is best to do that anyway

 

[Kobs]

by saying "the name of your project" means filename of ms access database file?

 

[strive4peace]

by saying "the name of your project" means filename of ms access database file?

hi Kobs,

not necessarily, it can be different. The project is all the VBA code. When you choose Compile from the Debug menu, the project name is after the word "Compile"

 

[Kobs]

thanks. I will try that later. another question. how to create a report(print preview) based on the filtered subform?

 

[strive4peace]

you're welcome, Kobs

Since that is a different topic, please start a new question for it. Thank you.

 

[Kobs]

strive,
can you also tell me how to make "select all" and "de-select all" button for the checkboxes?

 

[Kobs]

hi strive,

the compiler is returning syntax error in line vFilter = (vFilter + AND ")

 

[MajP]

See this recent post. I demo a select all unselect all, and opening a report based on the selections.

VBA Userform for datarange and checkbox for selection

Hi there, I've created a database with Access2019. In this database I want to create a report which consist of the data between two chosen dates (textboxes) 'or no specific daterange if not filled in' and location(s) (checkboxes). I've tried different methods including Queries and Forms but...

www.access-programmers.co.uk

 

[Kobs]

Hi MajP. Can you tell me what is wrong with strive's code the compiler is saying syntax error in lines vFilter = (vFilter + AND ")

 

[strive4peace]

Hi MajP. Can you tell me what is wrong with strive's code the compiler is saying syntax error in lines vFilter = (vFilter + AND ")

sorry, Kobs, that should be

(vFilter + " AND ")
... aircode so I didn't test it

 

[MajP]

That code makes no sense to me. Not the way I would have done it.
Maybe this is supposed to be
vFilter = (vFilter & " AND ")
also you would have to strip off the first and at the beginning of the code.

 

[Kobs]

thanks strive. also if i put the
"Option Explicit ' require variable declaration" on top of the code its saying " only comments may appear after End Sub, End Function, or End Property.

 

[Kobs]

also the line is automatically placed after the option explicit statement

 

[Kobs]

hi strive. nothing is happening when i ticked and unticked the checkboxes... is this have to do with line "
.Filter = vFilter
.FilterOn = True "?

 

[strive4peace]

also you would have to strip off the first and at the beginning of the code.

No you don't. That is the reason vFilter is set to Null first, so then because of null propagation, " AND " won't get added to the beginning. The reason I put that in there is that I find people often add more filters before that, so then the code doesn't have to change