Using different form strings to filter

[nybaseball22]

Hello. Is there a way to code a query to have it use a filter string
if a form is open, or ignore it if the filter is not open?

Example:

Query Name: Query1
Form: Form1 and Form2
Query fields: Field1 and Field2

I have a string in field 1 and 2

Forms!Form1!Field1 in the first field of the query, and Forms!Form1!
Field2 in the second field. Can I also have Forms!Form2!Field1 in the
query as well? I have tried this, but a box pops up and asks for the
information from form 2 when it is not open.

Thank you.

 

[Stefan Hoffmann]

hi,

Hello. Is there a way to code a query to have it use a filter string
if a form is open, or ignore it if the filter is not open?

Yes, there are some ways to do it, I'd prefer the use of a "proxy"
function in a standard module, e.g.

Option Compare Database
Option Explicit

Public Function ProxyFilter As Variant

ProxyFilter = Null
If CodeProject.AllForms.Item("yourForm").IsLoaded Then
ProxyFilter = Forms![yourForm]![yourField]
End If

End Function

And use the following condition in your query:

WHERE IsNull(ProxyFilter) OR field = ProxyFilter

You need a function for each field you like to query.


mfG
--> stefan <--

 

[nybaseball22]

Thank you. I tried this and it does not seems to be working. I
inserted the code in a module (I think this is what you told me to do)
and entered the string you provided in my query field. I am getting
an error : Invalid Syntax. Any idea what I am doing wrong??

Thanks again

 

[Stefan Hoffmann]

Thank you. I tried this and it does not seems to be working. I
inserted the code in a module (I think this is what you told me to do)

It must be a standard module, not a form, report or class module.

and entered the string you provided in my query field. I am getting
an error : Invalid Syntax. Any idea what I am doing wrong??

What Access version do you use?

The SQL of your query should read like that:

SELECT *
FROM [yourTable]
WHERE IsNull(ProxyFilter()) Or [yourField]=ProxyFilter()



mfG
--> stefan <--

 

[nybaseball22]

Acces 2003. I tried the SQL view insert, but when I switch back to
design view, it only shows ProxyFilter() in the OR column, and when I
execute, I get an error "Undefined function "ProxyFilter' in
expression.