Simple dynamic query of true/false field

[TasCat09]

Hello all
I am sorry to ask what I am sure must be a very simple question but I have googled (including this forum) for a couple of days and can't find the answer to my question put simply enough for my very inexperienced MS Access brain to understand.

I have a one table database for collecting recipes. The table includes a true/false field called DinnerOption which I use to 'tag' a recipe as one I could cook for dinner. I want to create a form that allows the user to select either the records that are a dinner option (true) or the records that are not a dinner option (false) to be included in the query. I have been able to include the true/false criteria in a query (not dynamically) by adding the word 'true' or 'false' in the criteria of the DinnerOption field in a query.

I have also been able to create a control in a form that allows a user to limit the query based on the contents of a text field.



This works when I use it as a criteria in the RecipeName field in the query:

[Forms]![Form1]![txtRecipeName]

I thought the answer would be to combine the successes I have had by placing the following in the criteria of the DinnerOption field in the query:

[Forms]![Form1]![chkDinnerOption] = True

Unfortunately, this did not work.

I know I am missing something really basic but can't work out what. I would really appreciate any thoughts some more experience people have.

Thank you.

[ArviLaanemets]

Create continuous form based on your table (e.g. cfRecipes, and add it as subform (name subform like scfRecipes) to another, single form (unbound or bound one, let's name it e.g. fMain). Into fMain, add a combo (e.g. cbbMainOption with RowSource like "-1, 'Dinner', 0, 'Not Dinner'"). Set LinkMasterFields property of subform scfRecipes to cbbMainOption and LinkChildFields property to field DinnerOption (or whatever name it has in your table).

Now when user selects "Dinner" in combo on Main form, all recipes with DinnerOption = True are displayed in subform. When user selects "Not Dinner", all recipes with DinnerOption = False are displayed in subform.

NB! When adding new recipes is allowed for subform, added recipe gets dinner option, currently selected in Main form, applied automatically.

Another way is to have an unbound combo in your recipes form, and it's AfterUpdate event is setting form's Filter property to "DinnerOption = True" and FilterOn Property to True.

[ssanfu]

@TasCat09

It would be helpful if you have provided the SQL of your query and/or your dB. I'm not sure exactly how you want to show recipes.

So I threw together a dB with two forms. One form has a saved query like what you described as the form record source and a check box in the header to show dinners or not.
The other form has a query as the form record source, but sets a filter to limit the records displayed. There are two options to filter the form - one is using a check box and a button to limit records and the other method is an option group.

This is just to show you different methods of limiting records.

[TasCat09]

Thank you both ArviLaanemets and ssaanfu. Both of your advice helped me research further and ultimately come up with something I am starting to understand that involves the afterupdate event you discussed Arvi.

What I came up with was to add this code to the after update event on the checkbox:

Private Sub chkDinner_AfterUpdate()MySQL = " SELECT Recipes.RecipeName, Recipes.Ingredients " & _
        " FROM [Recipes] " & _
        " WHERE [Recipes].[Dinner]= " & chkDinner.Value & ";"


Forms!frmRecipe.Form.RecordSource = MySQL



End Sub

My next challenge is to include multiple checkboxes and other controls that work with each other rather than overriding each other. I think I have a plan!