Option button to filter Query on Form

[aron.ridgway]

Hi There,

Any ideas on how i would filter a form using an option button. E.g i want to be able to click an option button that is called Filter user and it will filter the query based on who is logged in? and a Filter all option that will clear the filter and show all records?

thanks

 

[Cronk]

In the after update event of the option box, have an if clause that changes the recordsource of the form.

If me.optChoice =1 then
   Me.recordsource = "select * from yourTable where UserId="& chr(34) & environ$("username") & chr(34)
Else
  Me.recordsource = "select * from yourTable"
endif

 

[aron.ridgway]

thank you for the post.

i have tried the following code, it dosnt throw an error but dosnt create a filter?

If Me.OptUser = 1 Then
Me.RecordSource = "Select * from qryOrderSummary where Username=" & Chr(34) & Environ("Username") & Chr(34)
Else
Me.RecordSource = "Select * from qryOrderSummary"
End If
End Sub

the Username field is text so im matching the environ with the username field

can you see anything wrong?

 

[Cronk]

Does the code execute when the option is changed?

Have you looked at the SQL generated by the code and tried pasting it into a query to see what the results are?