Apply different filters based on base form name

[mariusk]

Hi,



I have a form (A) that is asking for a criteria, for example a date, and has a button who opens another form (B) that applies a filter and is showing only the relevant records for that date.
Now I want to create another form (C) that will ask for a different criteria, for example a name, and its button will open the same destination form (B), but this time will show only the records with that name.
My question is - is it possible to apply different filters on a form based on the base form that's opening it ? So if form A will open form B, it will apply a certain filter, but if form C opens form B it will apply a different filter ?

Thanks!

[ranman256]

there's no need to open multiple forms.
have 1 form show all records, then user fills in the criteria boxes, then filter:

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 
sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"
If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

[mariusk]

Thank you Ranman, I'm aware of this method, however this will not fit in this case.
While my example was so simple that called for this solution, in reality it's a little more complex, for example
a need to filter records that are missing some data, etc', and we don't want to put every criteria in the same form.

[orange]

Please tell us more. As ranman suggested --it's all the same base data. You want to filter that data to highlight selected data. Seems multiple, identifiable filters would be effective.

For example: consider a number of players for a team.
Filter:
-all right handed
-all left handed
-those over 200 lbs
-those taller than 6 ft.
-....

Same group of players--just filtered for specific attributes.
Good luck.

[mariusk]

Orange, what you're saying is of course possible.
However, in terms of gui logic I prefer to separate it.
I'll explain:
There's a single form that shows all of the records.
I want to apply several filters to the same form based on the need, for example:
1. Show me everything with a specific date (or date range).
2. Show me everything with a specific name.
3. Show me everything with a specific type.
All of these can be on the same base form, this is not a problem.
However, I also want for example "Show me records with a missing date".
Although this filter can also be applied to the same form, in terms of logic it doesn't look nice there.

Currently I have a base form that gets criteria from the user and opens a second form which applies the filters based on this criteria.
So I want to create a another base form that will open the same second form, but this time with a different type of criteria, for example "show me missing data".
I thought that if there's a way to tell the second form something like "if this form is opened by a form named <some form name>, apply these filters, otherwise apply other filters".

Thanks!

[Micron]

Are you unable to use/write the code you'd need? I agree and still see no reason for why multiple forms. "doesn't look nice" doesn't provide much of a clue. Maybe an option frame with 2 options - provide a date (date field gets enabled, validation uses Null if date is empty, date if not empty) OR don't include date thus date field is disabled. You build sql strings in parts and concatenate them together, using only the ones that apply and run that sql.

The reason for trying to dissuade someone from using multiple forms for one purpose is partly because of the problems they present, which is why you're here. Another is that should you need to modify the procedure, the changes likely cascade to every form involved. If you insist, you can use hidden textboxes on parent forms and refer back to their value, or you can pass parameters from one to another using OpenArgs of DoCmd.OpenForm. These methods will fail if a prior form is closed by the user.

[mariusk]

The reason for trying to dissuade someone from using multiple forms for one purpose is partly because of the problems they present, which is why you're here.

I have to agree with you on this one :-)

However, from my point of view I'm trying to do the opposite - to use a single "destination" form for multiple purposes.
The multiple forms you mentioned are only for the criteria input, and this is instead of using a single form for criteria and subform for the data.
I chose this path because the form that's showing the data is basically the same form I need for other tasks too, that aren't exactly related to showing filtered records.
For example, I want to also make it list only records that are missing some data, or to be able to add new records.
I know this can all be done in a single form, but it doesn't look right to me, basically in terms of gui design
(although after your and Ranman replies I'm starting to think that maybe what I'm used to may not be right).

[isladogs]

In case it helps, have a look at this link to my website http://www.mendipdatasystems.co.uk/m...ter/4594454290
This includes two examples of a single form used for multiple grouping and filtering.
The first displays individual records. The second has summary charts or records
In each case, one or more items can be filtered at once
You could also use an option group which would cover whether selected item should be included or excluded from the search.
The principle is identical. Build the search criteria based on selections made

[mariusk]

Thanks, it was quite helpful.

So I started building the subform that displays the records with some textboxes for criteria selection on the form and put some criteria as follows:


It needs a little tweaking, but the filters do work almost as they should.

My main issue is that I cannot make it work without filters, meaning when all of the criteria textboxes are empty it displays nothing instead of all records.
I tried basic things from my memory, like adding another line in the query builder and fill it with Like "*" on the relevant fields, but it doesn't change anything.

Any help would be appreciated.

Thanks.

[isladogs]

The reason for suggesting that link on my website was so you could see how I build multiple filters dynamically as strings using VBA.
That will also allow you to manage fields where no filters exist.
Try to study the code as the same ideas can be applied to any Access app.

[ijaz8883]

The reason for suggesting that link on my website was so you could see how I build multiple filters dynamically as strings using VBA.
That will also allow you to manage fields where no filters exist.
Try to study the code as the same ideas can be applied to any Access app.

You have a good collection of examples at your website. I wondered to see your multiple search system db. Thanks