Multiple filter command buttons on a form

[angi35]

Working in Access 2000... I have a form with records for every employee. I'd like to create two command buttons so a user can filter the form for either 'only current' or 'only former' employees. I applied the first filter ([Termination Date] Is Null) and created the first 'apply filter' command button (using the wizard), then cleared the filter, applied the second filter ([Termination Date] Is Not Null), and created the second command button. It changed the first button to apply the same filter as the second button.

How do I create two different filter options on the same form? Using the wizard, the event procedure that was created is:

Private Sub CmdCurrent_Clic k()
On Error GoTo Err_CmdCurrent_ Click


DoCmd.DoMenuIte m acFormBar, acRecordsMenu, 2, , acMenuVer70

Exit_CmdCurrent _Click:
Exit Sub

Err_CmdCurrent_ Click:
MsgBox Err.Description
Resume Exit_CmdCurrent _Click

End Sub

I don't understand enough of this to know how to change it.

Any ideas?

Angi

[JustJim]

Working in Access 2000... I have a form with records for every employee. I'd like to create two command buttons so a user can filter the form for either 'only current' or 'only former' employees. I applied the first filter ([Termination Date] Is Null) and created the first 'apply filter' command button (using the wizard), then cleared the filter, applied the second filter ([Termination Date] Is Not Null), and created the second command button. It changed the first button to apply the same filter as the second button.

How do I create two different filter options on the same form? Using the wizard, the event procedure that was created is:

Private Sub CmdCurrent_Clic k()
On Error GoTo Err_CmdCurrent_ Click


DoCmd.DoMenuIte m acFormBar, acRecordsMenu, 2, , acMenuVer70

Exit_CmdCurrent _Click:
Exit Sub

Err_CmdCurrent_ Click:
MsgBox Err.Description
Resume Exit_CmdCurrent _Click

End Sub

I don't understand enough of this to know how to change it.

Any ideas?

Angi

Hi Angi,

First of all, don't worry about not understanding the DoCmd.DoMenuIte m command. Nobody understands that unless they have all 5 previous versions of Access installed and running and they go to the menu bars and count the steps!

What I think you need is an option group with two toggle buttons (the ones where when one is pressed, the other pops up)

In the option group's AfterUpdate event you need something like (I'm just typing here, the code hasn't been checked)
[CODE=vb]Private Sub ToggleOne_OnCli ck()

If Me.ToggleOne = True Then
Me.Filter = "[Termination Date] Is Null"
Me.FilterOn = True
Else
Me.Filter = "[Termination Date] Is Not Null"
Me.FilterOn = True
End If

End Sub[/CODE]

The actual filter wording may need some work, I seem to remember that it should be like an SQL WHERE clause without the word "Where"

Good Luck

Jim

Ps if you need a third option (all employees) you'll need a third option button, ElseIf testing (or possibly a Select Case statement) and Me.Filter = ""

[angi35]

Thanks, Jim. I got it to work. For the record, here's what it looks like. (Case 2 is current, 3 is former, and 1 is all.)

Private Sub Frame107_AfterU pdate()

Select Case Frame107

Case 2
Me.Filter = "[Termination Date] Is Null"
Me.FilterOn = True

Case 3
Me.Filter = "[Termination Date] Is Not Null"
Me.FilterOn = True

Case 1
Me.FilterOn = False

End Select


End Sub

Angi

[JustJim]

Thanks, Jim. I got it to work. For the record, here's what it looks like. (Case 2 is current, 3 is former, and 1 is all.)

Private Sub Frame107_AfterU pdate()

Select Case Frame107

Case 2
Me.Filter = "[Termination Date] Is Null"
Me.FilterOn = True

Case 3
Me.Filter = "[Termination Date] Is Not Null"
Me.FilterOn = True

Case 1
Me.FilterOn = False

End Select


End Sub

Angi

Good work.

Jim
Edited to add more characters (stupid 20 character limit)