How To Re-Filter Data

[ahd2008]

Hi everyone,

I have a form for departments and personnel. There are there fields for the sake of search:

DepartmentSearch as combo box
Division\UnitSearch as combo box
Number as combo box

the user should selcet values form those and press the button( I 'm usnig Macro) to filter data. So far, everything works fine but when re-select other values to look for other data it doesnt work!! it keeps giving me the same result

How to solve this problem ?

[MikeTheBike]

@ahd2008
Hi
You have not indicated how you are applying the filter in the first instance, but if the form filtering controls are linked to the Record Source of the form (ie. a Stored Query), them I suggest you insert Me.Requery in the After Update event for the filtering controls.

MTB

[ahd2008]

I'm full of gratitude to you Sir and sorry i didnt provide sufficient details.

the way i do the filter proceess is by using Macro. As i mentioned the user should select the Department and the Division or Unit and its number then click button which run the Macro. Everything goes as planned but when I want to select other department it doesnt work. !! it gives me the same result for the previous search.

Also when i click the Toggle Filter button on the upper side of the window to trun off filter and try again i get the same result. Moreover, I tried Me.Requery but it doesnt work too.

in regard to your question, the filtering controls are not inked to the recourd source

Looking forward to your assistance

[MikeTheBike]

@ahd2008
Hi again

I thing it would be usefull if you posted the macro code, we might then be able to be more specific in our reply.

MTB

[ahd2008]

Here you go

I put this code into where condition part of the macro

[Departments.Department Name] Like [Forms]![Departments and Personnel]![DepartmentSearch] And [Units&Divisions.UDName] Like [Forms]![Departments and Personnel]![UnitDivisionsearch] and [Units&Divisions.Number]=[Forms]![Departments and Personnel]![NumberSearch] 

appreciate your help.

[MikeTheBike]

@ahd2008
Unfortunately the line of code posted does not give any clue as to how you are filtered the form originally or how you are trying to re-filter it after changing the filter criteria.

It would be better if you posted the complete Macro/Sub !?


MTB

[ahd2008]

Thanks for your support.

I attached my database now. The way i filter the form i click on the search button to show the hidden fields (Department and Unit\Division) on the form. After that, i fill out the fields and click the search button which includes macro to do filter.

Thanks again

[MikeTheBike]

@ahd2008
Hi

Unfortunatly our IT policy will not permit down loading (or opening) files from the internet (as you can imagine this is a great source of frustation!). Therefore, I cannot be of any help unless you post the actual code.

MTB

[mshmyob]

This is why I don't like to use macro's. But if you insist then in your search macro just add a REQUERY action after your APPLYFilter line.

For the REQUERY action you do not put in any parameters for the control. (No parameter means to requery the underlying form).

cheers,

[ahd2008]

Sorry to pester you the whole code is already posted besides I explained the way i use the macro.

I dont know what the problem is and thanks for you help all. it seems i cant clearly deliver my concern.

Anyhow, if we could start over and provide me with other method to do search on form by using unbound fields i would appreicate it.

[mshmyob]

Maybe you misunderstood me.

Using the code you have all you need to do is REQUERY the form. So in your search macro you add a line at the end to do a REQUERY.

Go into design mode and after your last line you add a requery. In the action column add the command REQUERY. That's it.

cheers,

@ahd2008

[ahd2008]

thanks all for your support and the fruitful suggestions.

it works now i put the code Requery after macro code.

Althoug i know what is the major function of Requery but i'm still curious to know how it programatically makes it work.

I will be more than grateful if you explian this aspect

Thanks again.

[mshmyob]

The Requery method does one of the following:

1. Reruns the query on which the form or control is based.
2. Displays any new or changed records or removes deleted records from the table on which the form or control is based.
3. Updates records displayed based on any changes to the Filter property of the form.

The 3rd one applies to your scenario. You were changing the filter property of the form. This won't update your data until you do a requery.

The problem with using the built in Wizard to create your macro's is that the M$ designers cannot take into account every scenario that you may want to implement. You still may need to tweak the macro generated by the wizard. Unfortunately most beginners assume the Wizard is foolproof and can read their minds on what exactly they want done. I think if you really want to learn Access and continue using it for future projects that you learn how to program with it and leave the macro's behind.

Anyways good luck with your project.

cheers,


@ahd2008

[ahd2008]

thanks you a lot. what i was aware of is number 3

Yes i do want to use it for futuer and I'll be more than glad if you enlighten me in how to use better alternative than marco.

Thanks again ,which you marvelous luck.