Hi! I'm having problems applying a filter.

I have a command button which applies this simple filter:

Code:
--------------------------------------------------------------------------------

Me.Filter = "[Status] = 'WC'"
--------------------------------------------------------------------------------


But now I discovered that this filter didn't do enough. I need to exclude some more records. I need to exclude record which have a certain branchecode (which is a control on the form). Those certain codes are ("black-")listed in a query (which is dynamic, so I can't just enter those codes into the filter).

So what I need the filter to do besides the earlier posted one, is exclude a record if it's branchecode is equal to ANY of the branchecodes from the query.

If I'm not clear enough yet, just ask!

However you are generating the value in the branchcode control on the form you will have to generate in the query. How are you generating the value on the form? Using some formula entered in the expression builder?

I'm not really sure what you mean.

I'm not generating the value on the form, I'm generating the values in a query.

This query uses a criteria from the form, so it's dependant on which record is opened in the form.

I'll explain it with a example:

The form is open and the command for the filter is clicked. The criteria value for the query is found in the control Me!Network (e.g. '31012')

What should happen:
The filter must use the values of the query ('qryFilter'), so let's open it. This query calculates which branches are 'occupied' in this network '31012'. This means that it generates a list of (about 25) branches which shouldn't be shown on the form. Let's say these values are 0403, 0404, 0605, 0606, ...

So, along with the earlier mentioned filter Me.Filter = "[Status] = 'WC'" :


Me.Filter = "[Status] = 'WC' AND Me.Branchecode NOT IN (0403, 0404, 0605, 0606, ...)" (Didn't test this code btw)

But how can I keep this filter dynamic? So how can I let these values recalculted every time with the query?

Instead of using the Filter property, you may want to consider making the forms recordsource an SQL statement, then modifying the SQL statement programatically ...

In the code that currently sets the filter ....

Const cstrSQL as String = "Select tblMyTable.* FROM tblMyTable" 'The RAW, Unfiltered SQL Statement
strSQL As String 'The SQL statement for the recordsource
.
.
strSQL = cstrSQL & " WHERE [Status]='WC' And Me.Branchcode Not In (SELECT BranchCode FROM qryOfRestrictedCodes)"

Me.Recordsource = strSQL
.
.
.

Setting the Recordsource property will automatically cause Access to Requery the form, then Access will take care of running the qryOfRestrictedCodes when the form is requeried.

Another benifit to setting the recordsource is the fact that It is typically faster than filtering.

HTH ...

Make sure you also set the .FilterOn property:

Me.FilterOn = True

Alrighty!

Sorry I didn't reply earlier, but we're having a hard time syncronising our new interactive website with my Access database.

Taking a little breather from that now, and I tried what you explained to me.

I didn't do exactly what you suggested, but I'm hesitating a bit to change the recordset. It's quite a complicated database system, with all kinds off logging and tracing functions. One of the basics for that is that users are restricted to a recordset, unless they change it by a predefined procedure.

So I sicked to the filter, but I got this to work perfectly:

Me.Filter = "[TblData]![Status] ='WC' And [TblData]![Branchecode] In (SELECT Branchecode FROM VrijBrnetwerkbranchesLijst)"
Me.FilterOn = True


Thank you guys for helping me out with this!