Filters - Microsoft Access / VBA

katie.liddle

Hi guys,
I have written the following code to filter based on a selection from
a dropdown box, and it works:

Private Sub txt_FilterByWhat_Change()

DoCmd.ShowAllRecords
If (Me.FilterByWhere = "") Or IsNull(Me.FilterByWhere) Then
Me.Filter = "Type ='" & Me.txt_FilterByWhat & "'"
Me.FilterOn = True
DoCmd.Requery
Else
Me.Filter = "Type ='" & Me.txt_FilterByWhat & "'" & "and Where ='" &
Me.FilterByWhere & "'"
Me.FilterOn = True
DoCmd.Requery
End If
End Sub

So I copied and pasted it, changing the fields around to produce the
following, but it doesn't work... any ideas?

Private Sub FilterByWhere_Change()

DoCmd.ShowAllRecords
If (Me.txt_FilterByWhat = "") Or IsNull(Me.txt_FilterByWhat) Then
Me.Filter = "Type ='" & Me.FilterByWhere & "'"
Me.FilterOn = True
DoCmd.Requery
Else
Me.Filter = "Type ='" & Me.txt_FilterByWhat & "'" & "and Where ='" &
Me.FilterByWhere & "'"
Me.FilterOn = True
DoCmd.Requery
End If

End Sub

Aug 22 '08 #1

Subscribe Post Reply

1607

lyle fairfield

Did you name one control "txt_FilterByWhat" and the other
"FilterByWhere" without the "txt_" prefix?

When setting filters it may be more efficient to have one Private Sub,
say, FilterBy, which examines the state of the two controls and sets
the filter appropriately, and to call it from each of the two change
events. Once you have that working, the need for copying and the
inefficiency of two more or less identical procedures and two
opportunites for syntax errors are all removed.

Many developers would not set a filter but rather reset the the form's
recordsource to a dynamic query string. Depending on the database
engine and the location of the tables this might be more efficient in
terms of records loaded and processed.
On Aug 22, 6:04*am, katie.lid...@hotmail.co.uk wrote:

Hi guys,
I have written the following code to filter based on a selection from
a dropdown box, and it works:

Private Sub txt_FilterByWhat_Change()

DoCmd.ShowAllRecords
If (Me.FilterByWhere = "") Or IsNull(Me.FilterByWhere) Then
Me.Filter = "Type ='" & Me.txt_FilterByWhat & "'"
Me.FilterOn = True
DoCmd.Requery
Else
Me.Filter = "Type ='" & Me.txt_FilterByWhat & "'" & "and Where ='" &
Me.FilterByWhere & "'"
Me.FilterOn = True
DoCmd.Requery
End If

End Sub

So I copied and pasted it, changing the fields around to produce the
following, but it doesn't work... any ideas?

Private Sub FilterByWhere_Change()

DoCmd.ShowAllRecords
If (Me.txt_FilterByWhat = "") Or IsNull(Me.txt_FilterByWhat) Then
Me.Filter = "Type ='" & Me.FilterByWhere & "'"
Me.FilterOn = True
DoCmd.Requery
Else
Me.Filter = "Type ='" & Me.txt_FilterByWhat & "'" & "and Where ='" &
Me.FilterByWhere & "'"
Me.FilterOn = True
DoCmd.Requery
End If

End Sub

Aug 22 '08 #2

Similar topics

Clear filters and save doesn't stick.

by: shumaker | last post by:

I'm trying to defeat persistant filters with the following code in Close, and also in Unload: Private Sub Form_Close() DoCmd.RunCommand acCmdRemoveFilterSort Me.FilterOn = False Me.OrderByOn =...

Microsoft Access / VBA

Clear filters

by: Aaron | last post by:

Hello all. I am trying to avoid my users from saving filters. The fact is that after they use or change a filter, when closing the form Access would ask: "Do you want to save changes to the...

Microsoft Access / VBA

Tom Lane heads up

by: DeJuan Jackson | last post by:

Just dropping a quick not for Tom Lane. I sent a personal message today, but I wasn't sure if you'd get it after I remembered all of the spam filters you've got set up. Sorry for the off topic...

PostgreSQL Database

filters.alpha is not a object error

by: TJO | last post by:

Below is some sample code that fades div tags that is not working in IE 6.0.29 on xp sp2. Can anyone help see why the if(ie5) document.getElementById(divID).filters.alpha.opacity lines are not...

Javascript

using the Filters DLL (image filters)

by: Dieter Vanderelst | last post by:

Hello, I'm trying to access the Filters-Dll provided by the filters-project (http://filters.sourceforge.net/index.htm). Following the advice I got from the Python list -thank you for that-, I...

Python

Filters like old skool Jive, Fudd, Valspeak... Text transformation in Python

by: dananrg | last post by:

I would like to learn how to roll my own filter ala early 90's filters like Jive, Fudd, Kraut, Moron, etc, that take text as an input, and re-writes the text adding general hilarity of various...

Python

Export a report to excel that uses a query and additional filters.

by: smcdonald | last post by:

I have a report that opens up using a pretty complex query. I then pop up a form with combo boxes so the user can apply a filter to the existing report and then refresh the report. I need to export...

Microsoft Access / VBA

Filters on the data

by: kucol | last post by:

Hi guys, I wanted to ask you for help as I am struggling with it second evening already... I have got tables DEVICES and PARTS. One device can consist of multiple parts. But... I have...

Microsoft SQL Server

filter report based on filters in form

by: favor08 | last post by:

have a mainform called PendingsMain and a subform called PendingsSub. You can filter the subform by different filters and this works fine. i want to create a report that will print out the...

Microsoft Access / VBA

Hibernate : one-to-one non-lazy loading but Filters Not Applied.

by: CatchSandeepVaid | last post by:

We all know that one-to-one associations are non-lazly fetched but during this fetching the filters are not applied. I debugged hibernate code and found that hibernate finally calls...

Java

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice