Very simple yet cant get it working...toggle box switch

[Jasperuk]

Hi

Using a toggle box to ask the following question, i have data that contains either OPEN or CLOSED against each record. I tried using the following in the criteria for the specific data to either select all the OPEN records when the box is clicked and i would like to select all records OPEN and CLOSED when it is nto clicked

IIf([Forms]![MEQN_ProdACStn_Picker]![Toggle38]=True,"OPEN","")

This didn't work and a number of other attempts failed as well...

How do i do it in the criteria selection or is it not possible

Cheers

Newbie Jasper

 

[DCrake]

Simple Software Solutions

It sounds like your "toggle button" is actually a check box, and from your post you want only 2 conditions

if ticked only show me the OPEN records

else show me all the records

Therefore your code should appear as follows

If TickBox = True then

Select * From Table Where Field = "OPEN"

Else

Select * From Table Where Field Is Not Null

End If

Code has been simplified for brevity. What the above code will not show is any records where there is a Null value in the specified field. If you want to include them as well then simply omit the Where condition.

CodeMaster:

 

[Jasperuk]

I am sure its a toggle button (see attached hopefully)

Dont forget you dealing with a real novice here so not quite sure what i need to do, do i need to use code and forget the criteria box in the query ?

 

[DCrake]

Simple Software Solutions

To get you started

On the OnClick Event of yur toggle button place the following code

If Me.Toggle38.Caption = "OPEN" Then
Me.Toggle38.Caption = "BOTH"
Else
Me.Toggle38.Caption = "OPEN"
End If

I would prefer to use a tick box and have the label say ... "Tick to show open records only."

And employ the method as per my previous post.

CodeMaster:

 

[Jasperuk]

ok

now we have a toggle box that changes if clicked on or not....cool

Where do i put the other code ?

Told you i was a complete novice didn't I

 

[DCrake]

Simple Software Solutions

So we are down to the nitty gritty.

Your form is bound to a Table/Query? Yes?

Your table/query has a field in it that contains either OPEN or CLOSED or Nothing (Null)? Yes?

For brevity I am going to call this field ProdID and the Table ProdInfo

I suggest you delete the toggle button and replace it with a Checkbox

Call it ChkStatus

Set the default value to False

Next change the caption of the label to "Show only OPEN records only"

Right click on the checkbox and select properties and scroll down to the OnClick Event and enter an opening square bracket "[" then click the build button at the end of the line. Done that?

You should now be in VBA mode and see the following command

Sub ChkStatus_OnClick()

End Sub

Within the sub and end sub place the following code

If ChkStatus = True Then

Me.Fillter = "[ProdID]='OPEN'"

Else

Me.Filter = "[ProdID] Is Not Null"

End If

Me.FilterOn = True

Me.Repaint

Save your code, open the form tick and untick the check box. Do you see any difference in the number of records in your form? Did anything happen? Did you get an error message?

David

 

[Jasperuk]

Your table/query has a field in it that contains either OPEN or CLOSED or Nothing (Null)? Yes?

Not quite the query has no blanks at all if that makes a difference...

I presume this is a single 'l' Me.Fillter = "[ProdID]='OPEN'" ???

 

[DCrake]

Simple Software Solutions

The table that has the field in it that stores the value of either OPEN or CLOSED is the field that the query depends on to filter out the appropriate records.

The fact that I included the Null value was to ensure that any bogus records were still visible. When you add a new record to the table have you set the default value of this field to "OPEN" ? If not, do so, this will ensure that you have a value in this field.

Cannot understand your comment about the single "|" (Pipe)

Square brackets [] around field or table names are included for clarity or when field names have spaces in them, NOT recommended. The code could quite easily have been "OrderStatus='OPEN'". The single quotation marks around the word OPEN signifies that this is what the contents of the field should be, and the fact that is is a text type field. If it was a numeric value or a boolean value then the quotation marks are not needed.

David