Requery form based on query

[jwleonard]

I have a form that is based on a query, on the form there is an option group with two options (show all and show active). This option group is the criteria for the query the form is based on. My hope was to have a requery on the after update of the option group and have it show approptiate records based on that option group.
My query does work if I open it by itself, and the form will show the appropriate records when it is initially opened. However, when I change options in the option group the query runs again but the data in the form is not changed.
I have searched every forum I know of but I have not found out how to make this work. It is like the form is not being refreshed with the new data from the query.
Lastly, this is my first large database and my knowledge of database design is a little like swiss cheese! Is there a way and would it be better to use a filter to do this? I want the user to just click an option or button to show records marked as active only or show all records. By active I mean the yes no field in the table being set to yes.

 

[RuralGuy]

How about posting the Record Source for the form and whatever SQL you change it to when the CheckBox is checked.

 

[jwleonard]

RuralGuy, I am really not sure there is an SQL statement with what I am doing. I have the table that is the record source for the query, then the form is based on the query. In the Active (Yes/No) field of the query I have an IIf statement that chooses wether to show active (Value of -1) or inactive (Value of 0) based on the option group value on the form.
Even if I manually put the value in the criteria (for instance -1, which should return all records where the check box is checked) it only works when the form is initially opened and will not work if I change the criteria and requery without closing the form. I think this is really simple and I am just missing it due to lack of experience.
If I should be doing something different please take me to shcool on this!!!
I really appreciate your help, I wouldn't even know what a database was if it wasn't for the folks on this forum.

 

[RuralGuy]

Please post the SQL for the query to which you refer.

Be sure and copy and paste rather than retype.

 

[jwleonard]

Here is a copy of the database, I had to strip it down to make it small enough. Thanks again

 

[RuralGuy]

Do you just want to show Active in one case and Inactive in the other or Active only in one case and everyone in the other?

 

[jwleonard]

I haven't decided yet, I originally had it active and all but I couldn't get the false part of the IIf to work with both the 0 and -1 values for the criteria. I would like to try to get active and all just to learn how to do that.

 

[RuralGuy]

Here's your mdb back with a CheckBox added to the bottom of your form. It is just one way to accomplish what you describe.

 

[jwleonard]

Thanks, that works great! Now I see why you were asking about SQL statements. Is this the best way to accomplish this, or should I be doing something different? Do you have to type out that SQL statement manually or is there a way to do it automatically?
Sorry for all the questions, that works great; I am just trying to learn from it.

 

[RuralGuy]

There is nothing wrong with the way I did it unless someone wants to split hairs on speed or something. I simply used your query and copied the SQL view and pasted it into the string. Since Access is so robust there are probably 3 or 4 ways of doing about anything. Have fun learning, I am.