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_Click()
On Error GoTo Err_CmdCurrent_Click


DoCmd.DoMenuItem 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_Click()
On Error GoTo Err_CmdCurrent_Click


DoCmd.DoMenuItem 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.DoMenuItem 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)

Private Sub ToggleOne_OnClick()
 
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

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_AfterUpdate()

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_AfterUpdate()

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)