467,858 Members | 1,725 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,858 developers. It's quick & easy.

Multiple filter command buttons on a form

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
Jan 29 '08 #1
  • viewed: 8248
Share:
3 Replies
JustJim
Expert 256MB
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)
Expand|Select|Wrap|Line Numbers
  1. Private Sub ToggleOne_OnClick()
  2.  
  3. If Me.ToggleOne = True Then
  4.      Me.Filter = "[Termination Date] Is Null"
  5.      Me.FilterOn = True
  6. Else
  7.      Me.Filter = "[Termination Date] Is Not Null"
  8.      Me.FilterOn = True
  9. End If
  10.  
  11. 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 = ""
Jan 30 '08 #2
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
Feb 1 '08 #3
JustJim
Expert 256MB
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)
Feb 2 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

16 posts views Thread by Nhmiller | last post: by
1 post views Thread by lorirobn | last post: by
2 posts views Thread by chiinook | last post: by
1 post views Thread by AccessNewbie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.