Form Filtering??

[jamesormi]

Hi,

I have a form that has a number of controls on it and a subform (displaying a datasheet of the same table used in the main form). I would like to filter the subform with the values I have selected in the controls. I don’t want to have to complete all the controls in order to see the data in the subform.

If I had 10 controls on the form I would like to be able to select values from control1, control4 and control7 and see all the information for these values in the subform.

I’m trying this using a filter but if I leave any of the values blank I see no data in the sub form. Do I have to check the values are not null and adjust the filter to suit or is there some other way?

Any help would be much appreciated.

 

[tebule]

Maybe I am not understanding what you need. But you can make the subform data source a query and then make the criteria the field on the form. Hit the build button on the criteria line if you don't know how to select the form and field.

Hope this helps.

 

[jamesormi]

Hi,

I've now realised that this can be done (make the subform data source a query).

I would like the user to be able to search without completing all the on my form and still show data in my subform. Using this method I have to complete all fields before any information is shown in the subform (query). Is there something that I'm missing to say that not all fields have to be complete?

Thanks

 

[tebule]

You can do that as long as a null value in your query will return results. Or you can put alternate criteria in your query for your form on the or line. Does that answer your question?

 

[jamesormi]

At present I have the following in my criteria for my query:

[Forms]![FormName]![Control] Or [Forms]![FormName]![Control] Is Null

This is not working for me.

 

[Endre]

Use the following:

Like "*" & [Forms]![f_MainForm]![ctr_Comb1] & "*"

In this way if there is an empty value you are searching for all values.

If you are going to store this in a Query, remember to set up the parameter for it also. (See http://www.access-programmers.co.uk/forums/showthread.php?t=176085 Post #3)

 

[tebule]

Endre's suggestion will work great.

I think that yours is not working because you have is null. That would return only records with nothing in that field if you do not have anything in your form field.

Should be is not null.. See if this works.

 

[jamesormi]

That works a treat!!!!

Thanks Guys!!!