filter

  • Thread starter Thread starter Jane Pulley
  • Start date Start date
J

Jane Pulley

Hello group! I would like to use a txtBox to filter form
by employee number. What code do I need to accomplish this?

Thank You,

JP
 
Hello group! I would like to use a txtBox to filter form
by employee number. What code do I need to accomplish this?
Click to expand...
Set the form's filter property to the field and value, then turn it
on.

Me.Filter = "EmployeeNum = " & Me.txtBox
Me.FilterOn = True


To turn the filter off set it to an empty string.

Me.Filter = ""

You don't need to turn it off.


- Jim
 
-----Original Message-----

Set the form's filter property to the field and value, then turn it
on.

Me.Filter = "EmployeeNum = " & Me.txtBox
Me.FilterOn = True


To turn the filter off set it to an empty string.

Me.Filter = ""

You don't need to turn it off.


- Jim
.
Click to expand...
Jim, this won't work. I forgot to tell you I have an
option group, containing two option buttons. One is for
Employee name, other is for employee number. I get the
employee name to work fine, it's the number I cannot get
it to search right. When I use this code it takes me to
a number that isn't related what so ever. grr!

JP
 
Jim, this won't work. I forgot to tell you I have an
option group, containing two option buttons. One is for
Employee name, other is for employee number. I get the
employee name to work fine, it's the number I cannot get
it to search right. When I use this code it takes me to
a number that isn't related what so ever. grr!

JP
Click to expand...

Well, what is in the textbox? Actually I would think a combo box with
a Row Source of employees would be a better approach.

In what event are you putting the code, and what is the code?

There is nothing wrong with the approach.

- Jim
 
-----Original Message-----


Well, what is in the textbox? Actually I would think a combo box with
a Row Source of employees would be a better approach.

In what event are you putting the code, and what is the code?

There is nothing wrong with the approach.

- Jim
.
Click to expand...

Here is my code. If you know of a better way with a
combo box please tell me. Thank you!



Private Sub txtFind_AfterUpdate()

If fraFilter = optEmpName.OptionValue Then

If Me.Dirty Then
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then
Me.FilterOn = False
Else
Me.Filter = "[txtEmpName] Like """ &
Me.txtFind & "*"""
Me.FilterOn = True
End If
End If

If fraFilter = optEmpNumber.OptionValue Then

If Me.Dirty Then
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then
Me.FilterOn = False
Else
Me.Filter = "[txtEmpNumber]=" & Me.txtFind
Me.FilterOn = True
End If
End If

End Sub
 
-----Original Message-----


Well, what is in the textbox? Actually I would think a combo box with
a Row Source of employees would be a better approach.

In what event are you putting the code, and what is the code?

There is nothing wrong with the approach.

- Jim
.
Click to expand...

Here is my code. If you know of a better way with a
combo box please tell me. Thank you!



Private Sub txtFind_AfterUpdate()

If fraFilter = optEmpName.OptionValue Then

If Me.Dirty Then
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then
Me.FilterOn = False
Else
Me.Filter = "[txtEmpName] Like """ &
Me.txtFind & "*"""
Me.FilterOn = True
End If
End If

If fraFilter = optEmpNumber.OptionValue Then

If Me.Dirty Then
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then
Me.FilterOn = False
Else
Me.Filter = "[txtEmpNumber]=" & Me.txtFind
Me.FilterOn = True
End If
End If

End Sub
Click to expand...

Jane, it looks like there is an error in your code. You are setting
the filter reference to a control name, it should be to a field name
of the form's Record Source; e.g., EmpNumber instead of txtEmpNumber.

For searching and filtering a form I generally prefer an unbound combo
box over a text box. The combo using a Row Source query with the
appropriate data - in your case the EmpNumber and EmpName - provides
the user with a certain way to do their task. A textbox is susceptible
to typos and can be frustrating. A combo is about selecting data a
textbox is about entering data.

For example if your combo Row Source query includes EmpNumber, EmpName
then the row count would be set to 2. The column widths would be set
to 0";1.5" (or whatever you need to display the name - 0" hides the
EmpNumber column) The bound column would be 1 (the EmpNumber column).
I would set the combo to drop down on the change event. This gives the
user a look at the options within their selection so far. If they spot
the name they are looking for then they can just arrow down and hit
Enter.

Use the After Update event to do the filtering. Use only the EmpNumber
- that's the bound column.
The code would look something like...

Private Sub cboEmployee_AfterUpdate()
If Me.Dirty Then Me.Dirty = False
Me.Filter = "EmpNumber = " & Me.cboEmployee
Me.FilterOn = True
End Sub

- Jim
 
Back
Top