Need Toggle Button to Filter Form 2

[JonWolgamuth]

I've done some searching via keyword, and I cannot find the answer here--which is amazing to me, so perhaps I'm not doing my search accurately.

Anyway, to the point, I'm working on a database which the analysts in our area will use to record the work we're currently working on. It will be a really nice organizational tool once we're done, but one feature has been requested which will make it even more valuable.

When users access the form to enter the tasks they're working on, it currently will show them the tasks "everyone" is working on. I want this to be the "toggle button up" scenario. When the user depresses the toggle button, I want the form to filter out everyone elses work, and show the individual's work only. I plan on using the Windows login ID for a filter [environ("username&quot], but I don't know how to go about getting the toggle switch to "filter by form".

Also, I need some code which will change the caption of the toggle button depending on whether it is depressed or not.

I hope I've adequately described my situation. Thanks!

Jon

 

[tledwards]

I liked your idea and had a use for it so I worked out how to do it on my form. The one thing I am not sure of is what you mean by login ID for a filter [environ("username&quot]. In my case I use a field called ACO_Code as the filter. If you made a field on your form that contained the value of '[environ("username&quot]' that field would replace my ACO_Code field.

This code needs to be in you open form to show all tasks when the form opens.

Me.FilterOn = False
Me.Filter_by_ACO.Caption = "All Records"
'Note when you type 'me.' a listing of all the field that you can filter on will be listed starting with 'Filter_by_' then the field name.

This code is behind your toggel switch. I used a standard command button.

Dim stACOCode As String

stACOCode = Me.ACO_Code
If Me.FilterOn = False Then
Me.Filter = "aco_code = stACOcode"
Me.FilterOn = True
Me.Filter_by_ACO.Caption = "My Records"
Else
Me.FilterOn = False
Me.Filter_by_ACO.Caption = "All Records"
End If

 

[PeteJohnston]

I did this as a ListBox where the RowSource was an SQL statment which was built on the fly depending on their selection. Any of the selection fields which had an entry was added to a filter string. This example would have a list box with 4 columns.

strFilter = ""
If Not IsNull(txtItem) Then
   strFilter = strFilter & " Item LIKE '*" & txtItem & "*' AND"
End If
If Not IsNull(txtDescription) Then
  strFilter = strFilter & " Description LIKE '*"  & txtDescription & "*' AND"
End If
If Not IsNull(cboType) Then
   strFilter = strFilter & " Type = " & cboType & " AND"
End If
If Right(strFilter, 3) = "AND" Then
   strFilter = Trim(left(strFilter, Len(strFilter) - 3))
End If
If Len(strFilter) > 0 Then
  strSQL = "SELECT Item, Description, Type, Price FROM tblItems WHERE " & strFilter
End If
lstView.RowSource = strSQL
lstView.Requery

This selection has a couple of TextBoxes and a ComboBox, but you can use anything which suits your needs. I had a few other bits to change the sort sequence but that is the basic idea. If the Item field is the unique key for the table you can do a form to display a single record which you can open in the lstView_DblClick event with a filter to edit the record:

strFilter = "[Item]= '" & lstView.Value & "'"
DoCmd.OpenForm "frmItemEdit", , , strFilter

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[PeteJohnston]

Woops!

The bit of code above to set up the SQL statment had a little boo-boo! It should have read:

strSQL = "SELECT Item, Description, Type, Price FROM tblItems"
If Len(strFilter) > 0 Then
  strSQL = strSQL & " WHERE " & strFilter
End If

Sorry
PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)