By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,802 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Multiple filter command buttons on a form

P: 55
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
Share this Question
Share on Google+
3 Replies


JustJim
Expert 100+
P: 407
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

P: 55
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 100+
P: 407
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.