Toggle button to control criteria

[Master Klick]

I have form with a number of of combo boxes that filter a query that populates a list box. It's used to track inventory. I want to add a toggle button that when press will filter my query so that it shows only items with a value > 0 in the quantity field. And when not pressed it won't filter by that field at all.

I hope this isn't too complicated.

[Dal Jeanis]

If you have the combo boxes already set up to call a single routine that builds the query, then you're just concatenating a single additional test into the SQL. The toggle button's AfterUpdate event would call the same BuildQuery function. Because the unclicked condition doesn't limit the selection criteria of the query, it doesn't put anything into the SQL.

 
If tglValue = True Then 
   strSQL = strSQL & " AND [Value] > 0 "
End If

If you're using my code, in which I use a boolean variable to track whether I've already added the WHERE clause to the SQL, then it might look like this -

 
If tglValue = True Then 
   If WhereDone Then 
      strSQL = strSQL & " WHERE [Value] > 0 "
   Else
      strSQL = strSQL & " AND [Value] > 0 "
      WhereDone = True
   End If
End If

[Master Klick]

I'm sorry, could you explain that in more detail. I don't have any idea what you're telling me to do.

[June7]

Dal is suggesting VBA code to build an sql statement.

How are you using the comboboxes to filter query? Is the query parameterized by referencing the comboboxes? Post the query SQL statement.

Will every record have a value in Quantity field?

Try this criteria under the Quantity field:

>=IIf([Forms]![formname]![togglename],1,0)

[Dal Jeanis]

Sorry, there was another thread where I had posted some code on how to have multiple different controls interacting to build SQL. I'll see if I can find it.

[Dal Jeanis]

I found one of the threads where I posted the code. Post #8 in this thread - https://www.accessforums.net/queries...orm-36100.html

The basic idea is that the After Update event of each control calls the same BuildQuery routine, which then builds the proper SQL query for the selected states/conditions of all the controls.

At the beginning I set a Boolean variable WhereDone to False, which indicates that there is no WHERE clause yet. When the first control needs to add a condition, it places the "WHERE (" and sets WhereDone to True, and every control after that will place an "AND" instead. At the end, if WHereDone is False, there is no condition at all, and if it's True, then you close the parenthesis ")" before the semicolon ending the Query ";".

[Master Klick]

The criteria in the query is: Like [Forms]![InventoryForm]![Name] & "*", where ![Name] is the combo box. The combo boxes also pull their info from that query. So, the combo boxes not only narrow down the list box but they narrow the choices in the other combo boxes as well.

I just want to add a button to limit it to what I have in stock. But, I need to be able to toggle it on and off.

So like, button pressed=InvQuery!Quantity > 0, button released, no criteria in quantity.

[June7]

You want comboboxes to draw their RowSource from query that uses the comboboxes as criteria? This is circular reference and won't work.

Did you review my post #4?

[Master Klick]

Well, it's working fine right now. It's isn't circular logic because there is no actual overlap. It's linear logic, but the beginning and end are at the same point. And now I'm confusing myself. Never explained without drawing a picture before. Anyway, it works, trust me.

I just need to make a toggle button to turn the criteria for one field in the query on and off, if possible.

[June7]

I show criteria using toggle in that earlier post.

[Dal Jeanis]

Ah, yes, June7's post#4 can be ANDed into the base query or the filter and will work no matter which way the toggle is set up.

[Master Klick]

Yeah, I'm still not getting how it works.

[June7]

If the toggle is down its value is True, if up it is False. So If True, use 1, otherwise parameter is 0. So If the expression returns 1 then all the 0 values are ignored because the criteria is: >=1. If 0 returns then all records are selected because criteria is: >=0 - the same result as if no criteria applied.

This does assume every record has a value of 0 or greater, no Nulls, no negatives.

At least it worked that way in my testing.

[Master Klick]

Ha ha, it works! That was way way easier than I thought it would be. Thanks guys.

[Dal Jeanis]

Yeah, I thought June's code was pretty slick.