Apply filter on openform (1 Viewer)

[izzy441]

Hello,

I am currently trying to make it easier for my colleages to filter when opening a report. We have made a database of internet users etc. I have set a search box which should filter on all tables in my data query. This is the part where i am stuck as im not into access that much anymore.

The code im using at the moment is as following:

Private Sub OpenReport_Click()

DoCmd.OpenReport "Overzicht", acViewReport, , Me.Filter = "[Search]", Me.FilterOn

End Sub

Is there a way to connect all tables to the search so that if any field contains (for example) room number 110 it will only show the report with all computers using internet in room 110. I have though of making a listbox to mark in what table the search/filtering should take place.

Any help or direction to where i might find something that can help me on my way would be appreciated.

Thanks in advance.

 

[RainLover]

A pic of your relationships would be helpful

 

[Isskint]

I have never used filters in reports. The argument is there, so i guess it is viable. I however put the criteria in the query that the report uses as its Record Source. Now the criteria could just be on one field, so a critera of [Search] would be ok. If you want a more dynamic search criteria, this is best achieved by creating a search form. On the form place textboxes or comboboxes and a command buttton to run the report. In the command buttons OnClick event EITHER put code to construct the querys SQL OR have the query criteria preset to =Forms!SearchFormName.ControlName.

Search this forum for Dynamic Reports, Dynamic Queries or Dynamic Searching, there are LOADS of examples

 

[izzy441]

A pic of your relationships would be helpful

Here is a screenshot from the relations

 

[izzy441]

I have never used filters in reports. The argument is there, so i guess it is viable. I however put the criteria in the query that the report uses as its Record Source. Now the criteria could just be on one field, so a critera of [Search] would be ok. If you want a more dynamic search criteria, this is best achieved by creating a search form. On the form place textboxes or comboboxes and a command buttton to run the report. In the command buttons OnClick event EITHER put code to construct the querys SQL OR have the query criteria preset to =Forms!SearchFormName.ControlName.

Search this forum for Dynamic Reports, Dynamic Queries or Dynamic Searching, there are LOADS of examples

The filter option in access itself does work. If i place my cursor in the field which i want to use as searching. For example again, the room number, and right click to filter set 'contains 110' and open report with the button i created for it, it will show all the computers using internet in room 110. As said, i want to make this part easier for my colleages by setting a allround search field as made out of a textbox which would be linked to be a filter/search option on open report. As show in the image uploaded in the previous post, all the entries from the data table are linked to the search field from the data_1 table. Which for what i remember should give out a search on all the fields. So whenever the number 110 shows up or a last name it should add that as filter in the report. Then again, i havn't been doing this for a couple of years and want to get back into it so i might have done something wrong in the previous steps to create this.

I will deffinitly look up dynamic forms to see if that might work out better. Do hope to get it solved this way. Love these challenges

Edit: With the code i have shown in my initial post it does open with the filter applied, just not the search criteria. It will give out a black form.

 

[RainLover]

Re your design.

I don't understand it at all.

You have every field from one table related to one field in another table.

I believe you need to relook at this. Some reading up on design principals may help to get you up to speed.

The way it is now I cannot help.

 

[Solo712]

The filter option in access itself does work. .....QUOTE]

The example you are giving does not use the 'FilterName' argument of the DoCmd.OpenReport but the 'WhereCondition' argument which is essentially an SQL WHERE clause.

Best,
Jiri

 

[paulscherm]

I use queries using the control name as the criteria. Then I open the report using the query name as the filter.