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.
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:
Rich (BB code):
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
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:
Rich (BB code):
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
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"
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...
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.
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.
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