Using filters in OpenForm macro action.

Resolved · Urgent Priority · Version 2007

Tue 13 Mar 2012, 16:26 Delegate Timothy said...

Timothy has attended:
Access Advanced course

Using filters in OpenForm macro action.

Using a series of buttons one one form (A) to open another form (B). The buttons on A are all using the OpenForm action and are filtering form B through the use of queries. Form A itself is based on a query.

I know that a control value can be used to filter form B but do not want to put text boxes on form A to achieve this and given that there is the potential for form A to have a lot of buttons I need to find a less long winded way of filtering the form.

I only need to filter one field in form B to achieve what I want. Should I try and input an SQL Where clause into the other action argument. If so, would I only need to state the field I want to limit or all fields on form A.

Or can I put a simple expression in the Filter field like [Forms].[FormName].[FieldName] = ValueIWant.

Cheers
Tim

Wed 14 Mar 2012, 11:57 Trainer Doug said...

RE: Using filters in OpenForm macro action.

Hi Tim

Sounds like you are on the right lines with adding an expression to filter form B. Add the expression to the Where Condition for the OpenForm action.

As an example I tried it for a form called [Personal Details Form] which contains a [Job Code] field. Clicking a button runs a macro that opens a [Job Details form] filtered to show the job description for the person displayed in the [Personal Details Form].

This is the Where Condition

[Forms]![Personal Details Form]![Job Code]=[Job Details]![Job Code]

Note the expression relates the field on one form (form A) to the field in the Table or Query that is the control source for form B.

I hope this helps and if you can relate this to your example.

Regards
Doug Dunn
Best STL

Wed 14 Mar 2012, 13:19 Edited on Wed 14 Mar 2012, 13:23 Delegate Timothy said...

RE: Using filters in OpenForm macro action.

HI Doug,
Thanks for the reply, my situation is a little different in that I don't have a field to relate to the Query that is the control source for the next form.
I am using buttons that run macros to open the form.
What I want to do (using your example) is substitute [Forms]![Personal Details Form]![Job Code] with a text value.

Would it be correct to say "text value" = [Job Details]![Job Code] and this would filter form B with the text value entered?

Cheers
Tim

Wed 14 Mar 2012, 14:19 Trainer Doug said...

RE: Using filters in OpenForm macro action.

Yes the Where Condition can include a "text value" but the value would be on the right of the = sign.

For example,

[Job Details]![Department]="Training"

I've just tested and click the button on Form A displays all job records in the Training department.

Here Department would be a field in Form B following your example.

Doug

Wed 14 Mar 2012, 15:08 Delegate Timothy said...

RE: Using filters in OpenForm macro action.

Hi Doug,
Spot on, that will save me having to create loads of pesky queries.
Cheers
Tim

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Access tip:

Random sampling

Data is th key to a good database, therefore checking is essential.

A good way of checking data is by random sampling. You can do this in a query, by typing the follow:
1) In the Field box create a RandonID field eg. Randon Id: Rnd(fieldname])

2) sort the field
3)Right click and chage the properties for To value to be the number of randon records you want to see.

4) Change the set to Show row for Randon ID to be False, add all the other fields you want to see and the run the query.

View all Access hints and tips