On Click event filter By checkbox = True

[nick404]

Need help with code to filter selections. If the check box is checked for certain items i want a button to filter the results to only show these items when the button is clicked.
So far I have:
Private Sub Command25_Click()
If chkNeedPart = -1 Then
Me.Filter = "NeedPart=" True


Me.Form.Refresh
End If
End Sub

I tried using RunCmd.ApplyFilter( ,"chkNeedPart=" True, ) didn't work.

[ssanfu]

You are missing a line:

If chkNeedPart = -1 Then
    Me.Filter = "NeedPart = " True
    Me.FilterOn = True
    Me.Form.Refresh
End If

[June7]

Also missing concatenation operator but don't need to concatentate. True is an Access and VBA constant and can be within the quoted string:

Me.Filter = "NeedPart = True"

The Refresh line is probably not needed. I don't use it in conjunction with Filter/FilterOn.

[ssanfu]

Oops..... I missed the concatenation.

[nick404]

Perfect. Now the issue is after i filter this, i unchecked a result and hit the button again- it did not re filter. I was presented with the same options.

Private Sub Command25_Click()
If Me.chkNeedPart = -1 Then
Me.Filter = "NeedPart = True"
End If
Me.FilterOn = True
Me.Form.Refresh
End Sub

Do I need a line for something like
ElseIf Me.chkNeedPart = 0 then
Me.Filter = "NeedPart = False"
within the if clause?

[June7]

Eliminate the If Then

Private Sub Command25_Click()
Me.Filter = "NeedPart = " & Me.chkNeedPart
Me.Filter = Me.chkNeedPart
End Sub

[nick404]

Did just that... and the filter doesn't work? All results stay

[ssanfu]

Private Sub Command25_Click()
    Me.Filter = "NeedPart = " & Me.chkNeedPart
    Me.FilterOn = Me.chkNeedPart
End Sub

[nick404]

Okay this worked well. But I still have the issue of re-filtering my already filtered results. With this code it just sends back all the results, does not re filter them.

[June7]

Ooops, sorry for typo.

Filter using the checkbox as parameter should switch between showing records that are True and records that are False. Unless this in an unbound checkbox set for triple state.

If you want to switch between show all records and showing only the True records, that will need If Then Else.

If you want to filter for True records and then apply another filter parameter, that is a different issue. http://www.allenbrowne.com/ser-62.html

What exactly do you want to do?

I am wondering why there is a checkbox and a button. Is chkNeedPart an unbound control?

[nick404]

Well I have query results shown on a form (of parts to order). The user can select what parts they need to order with a checkbox. I have a button on the bottom to filter the results to only include what they checked. The most recently posted code worked, however let's say the user accidentally checked some 3rd part they didn't want to order and un-checks it and clicks the button again to filter out that item (assuming they will now see only 2 parts)...currently it does not filter again. It simply shows the same 3 parts- two being checked and one being un-checked. Understand the problem better?

[nick404]

I am wondering why there is a checkbox and a button. Is chkNeedPart an unbound control?

No chkNeedPart 's control is the NeedPart field from my table. The button is so I can filter the results to only show those the user has checked.

[June7]

So chkNeedPart is checkbox bound to NeedPart field? I was thinking it was an UNBOUND checkbox for user input of filter parameter.

So why filter the records? This means user cannot select a record they forgot to select.

What do you want to happen with the selected records? Run code to save their IDs into an OrderDetails table? Then when that's done, need to reset all the parts records back to False?

[nick404]

I could make it unbound..the field is only in there for the user to use, so perhaps it would be better if it was unbound for user input of filter param? The selected records are apart of a form that will eventually be emailed as a purchase requisition. Yes, ideally when that is done reset back to false as if nothing had happened for next use.

[June7]

I don't think UNBOUND will work for what you want. Every record on form will show the same value in an UNBOUND control that does not have a dynamic expression.

You want to email a form? A report would be better instrument for output.

Set the check value for each record. Save the records to OrderDetails. Publish report. Set check value back to False.

A simple UPDATE SQL action can reset the field: CurrentDb.Execute "UPDATE Parts SET NeedPart=False"

But really, a subform bound to OrderDetails and combobox to select Part in each record row might be just as fast as scrolling through Parts records and clicking checkbox. Lot less code.