Record Search

  • Thread starter Thread starter Troy Scott
  • Start date Start date
T

Troy Scott

Is there an alternative to using the default "find record" feature you get
when you use the wizard to applying a command button to a form? I would
like to be able to use a wild card in my search field, like if I cannot
remember the name of Kimmel I could type in Kim and it would show me all
matches. I don't want a report, rather in form view.

TIA

Troy
 
You can place an unbound text box on your form (typically in the Form Header
section), and use its AfterUpdate event procedure to filter the form to
whatever the user enters.

Private Sub txtFind_AfterUpdate()
If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then 'Show all
Me.FilterOn = False
Else
Me.Filter = "[MyField] Like """ & Me.txtFind & "*"""
Me.FilterOn =True
End If
End Sub
 
SUPER! I went just a step further and created an option group so the user
can choose to filter by employee name or employee number, but I'm confused
as to where to what sub to place the option group in. I also would like to
remove the filter with a button if possible. Thanks!



Allen Browne said:
You can place an unbound text box on your form (typically in the Form Header
section), and use its AfterUpdate event procedure to filter the form to
whatever the user enters.

Private Sub txtFind_AfterUpdate()
If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then 'Show all
Me.FilterOn = False
Else
Me.Filter = "[MyField] Like """ & Me.txtFind & "*"""
Me.FilterOn =True
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Troy Scott mindspring.com> said:
Is there an alternative to using the default "find record" feature you get
when you use the wizard to applying a command button to a form? I would
like to be able to use a wild card in my search field, like if I cannot
remember the name of Kimmel I could type in Kim and it would show me all
matches. I don't want a report, rather in form view.

TIA

Troy
Click to expand...
Click to expand...
 
You will need to change the [MyField] part of this line:
Me.Filter = "[MyField] Like """ & Me.txtFind & "*"""
to whichever field name you want to match, based on the option group.

If the EmployeeNumber is actually a number field, you must drop the extra
quotes, and use the = operator instead of Like.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Troy Scott mindspring.com> said:
SUPER! I went just a step further and created an option group so the user
can choose to filter by employee name or employee number, but I'm confused
as to where to what sub to place the option group in. I also would like to
remove the filter with a button if possible. Thanks!



Allen Browne said:
You can place an unbound text box on your form (typically in the Form Header
section), and use its AfterUpdate event procedure to filter the form to
whatever the user enters.

Private Sub txtFind_AfterUpdate()
If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then 'Show all
Me.FilterOn = False
Else
Me.Filter = "[MyField] Like """ & Me.txtFind & "*"""
Me.FilterOn =True
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Troy Scott mindspring.com> said:
Is there an alternative to using the default "find record" feature you get
when you use the wizard to applying a command button to a form? I would
like to be able to use a wild card in my search field, like if I cannot
remember the name of Kimmel I could type in Kim and it would show me all
matches. I don't want a report, rather in form view.

TIA

Troy
Click to expand...
Click to expand...
Click to expand...
 
grr, after working with it for an hour I need a little more help. I get the
error "you entered an expression that has no value". Here is my code.

Private Sub txtFind_AfterUpdate()

If fraFilter = optEmployeeName Then

If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then 'Show all
Me.FilterOn = False
Else
Me.Filter = "[Employee Name] Like """ & Me.txtFind & "*"""
Me.FilterOn = True
End If
Else

If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then 'Show all
Me.FilterOn = False
Else
Me.Filter = "[W581 Number]"
Me.FilterOn = True
End If
End If

End Sub



Allen Browne said:
You will need to change the [MyField] part of this line:
Me.Filter = "[MyField] Like """ & Me.txtFind & "*"""
to whichever field name you want to match, based on the option group.

If the EmployeeNumber is actually a number field, you must drop the extra
quotes, and use the = operator instead of Like.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Troy Scott mindspring.com> said:
SUPER! I went just a step further and created an option group so the user
can choose to filter by employee name or employee number, but I'm confused
as to where to what sub to place the option group in. I also would like to
remove the filter with a button if possible. Thanks!



Allen Browne said:
You can place an unbound text box on your form (typically in the Form Header
section), and use its AfterUpdate event procedure to filter the form to
whatever the user enters.

Private Sub txtFind_AfterUpdate()
If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then 'Show all
Me.FilterOn = False
Else
Me.Filter = "[MyField] Like """ & Me.txtFind & "*"""
Me.FilterOn =True
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"Troy Scott mindspring.com>" <tigerweld<removebrakets> wrote in message
Is there an alternative to using the default "find record" feature
Click to expand...
you
get
when you use the wizard to applying a command button to a form? I would
like to be able to use a wild card in my search field, like if I cannot
remember the name of Kimmel I could type in Kim and it would show me all
matches. I don't want a report, rather in form view.

TIA

Troy
Click to expand...
Click to expand...
Click to expand...
 
Option buttons don't have a value. Try:
If fraFilter = optEmployeeName.OptionValue Then

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Troy Scott mindspring.com> said:
grr, after working with it for an hour I need a little more help. I get the
error "you entered an expression that has no value". Here is my code.

Private Sub txtFind_AfterUpdate()

If fraFilter = optEmployeeName Then

If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then 'Show all
Me.FilterOn = False
Else
Me.Filter = "[Employee Name] Like """ & Me.txtFind & "*"""
Me.FilterOn = True
End If
Else

If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then 'Show all
Me.FilterOn = False
Else
Me.Filter = "[W581 Number]"
Me.FilterOn = True
End If
End If

End Sub



Allen Browne said:
You will need to change the [MyField] part of this line:
Me.Filter = "[MyField] Like """ & Me.txtFind & "*"""
to whichever field name you want to match, based on the option group.

If the EmployeeNumber is actually a number field, you must drop the extra
quotes, and use the = operator instead of Like.


Troy Scott mindspring.com> said:
SUPER! I went just a step further and created an option group so the user
can choose to filter by employee name or employee number, but I'm confused
as to where to what sub to place the option group in. I also would
Click to expand...
like
to
remove the filter with a button if possible. Thanks!



You can place an unbound text box on your form (typically in the Form
Header
section), and use its AfterUpdate event procedure to filter the form to
whatever the user enters.

Private Sub txtFind_AfterUpdate()
If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then 'Show all
Me.FilterOn = False
Else
Me.Filter = "[MyField] Like """ & Me.txtFind & "*"""
Me.FilterOn =True
End If
End Sub


"Troy Scott mindspring.com>" <tigerweld<removebrakets> wrote in message
Is there an alternative to using the default "find record" feature you
get
when you use the wizard to applying a command button to a form? I would
like to be able to use a wild card in my search field, like if I cannot
remember the name of Kimmel I could type in Kim and it would show
Click to expand...
me
all
matches. I don't want a report, rather in form view.
Click to expand...
Click to expand...
Click to expand...
 
I got this to work, but how do I make it find the next record with the
string? your help is greatly appretiated.

Allen Browne said:
Option buttons don't have a value. Try:
If fraFilter = optEmployeeName.OptionValue Then

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Troy Scott mindspring.com> said:
grr, after working with it for an hour I need a little more help. I get the
error "you entered an expression that has no value". Here is my code.

Private Sub txtFind_AfterUpdate()

If fraFilter = optEmployeeName Then

If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then 'Show all
Me.FilterOn = False
Else
Me.Filter = "[Employee Name] Like """ & Me.txtFind & "*"""
Me.FilterOn = True
End If
Else

If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then 'Show all
Me.FilterOn = False
Else
Me.Filter = "[W581 Number]"
Me.FilterOn = True
End If
End If

End Sub



Allen Browne said:
You will need to change the [MyField] part of this line:
Me.Filter = "[MyField] Like """ & Me.txtFind & "*"""
to whichever field name you want to match, based on the option group.

If the EmployeeNumber is actually a number field, you must drop the extra
quotes, and use the = operator instead of Like.


"Troy Scott mindspring.com>" <tigerweld<removebrakets> wrote in message
SUPER! I went just a step further and created an option group so the user
can choose to filter by employee name or employee number, but I'm confused
as to where to what sub to place the option group in. I also would like
to
remove the filter with a button if possible. Thanks!



You can place an unbound text box on your form (typically in the Form
Header
section), and use its AfterUpdate event procedure to filter the form to
whatever the user enters.

Private Sub txtFind_AfterUpdate()
If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then 'Show all
Me.FilterOn = False
Else
Me.Filter = "[MyField] Like """ & Me.txtFind & "*"""
Me.FilterOn =True
End If
End Sub


"Troy Scott mindspring.com>" <tigerweld<removebrakets> wrote in message
Is there an alternative to using the default "find record" feature you
get
when you use the wizard to applying a command button to a form? I
would
like to be able to use a wild card in my search field, like if I
cannot
remember the name of Kimmel I could type in Kim and it would show me
all
matches. I don't want a report, rather in form view.
Click to expand...
Click to expand...
Click to expand...
 
Hi

After you apply the filter, the form contains only the records that match.
Use the navigation buttons (bottom left of form, in the horizontal
scrollbar) to move to the next match.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Wael Fattouh said:
I got this to work, but how do I make it find the next record with the
string? your help is greatly appretiated.

Allen Browne said:
Option buttons don't have a value. Try:
If fraFilter = optEmployeeName.OptionValue Then


Troy Scott mindspring.com> said:
grr, after working with it for an hour I need a little more help. I
get the
error "you entered an expression that has no value". Here is my code.

Private Sub txtFind_AfterUpdate()

If fraFilter = optEmployeeName Then

If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then 'Show all
Me.FilterOn = False
Else
Me.Filter = "[Employee Name] Like """ & Me.txtFind & "*"""
Me.FilterOn = True
End If
Else

If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then 'Show all
Me.FilterOn = False
Else
Me.Filter = "[W581 Number]"
Me.FilterOn = True
End If
End If

End Sub



You will need to change the [MyField] part of this line:
Me.Filter = "[MyField] Like """ & Me.txtFind & "*"""
to whichever field name you want to match, based on the option group.

If the EmployeeNumber is actually a number field, you must drop the extra
quotes, and use the = operator instead of Like.


"Troy Scott mindspring.com>" <tigerweld<removebrakets> wrote in
message
SUPER! I went just a step further and created an option group so
the
user
can choose to filter by employee name or employee number, but I'm
confused
as to where to what sub to place the option group in. I also would like
to
remove the filter with a button if possible. Thanks!



You can place an unbound text box on your form (typically in the Form
Header
section), and use its AfterUpdate event procedure to filter the
form
to
whatever the user enters.

Private Sub txtFind_AfterUpdate()
If Me.Dirty Then 'Save first
Me.Dirty = False
End If
If IsNull(Me.txtFind) Then 'Show all
Me.FilterOn = False
Else
Me.Filter = "[MyField] Like """ & Me.txtFind & "*"""
Me.FilterOn =True
End If
End Sub


"Troy Scott mindspring.com>" <tigerweld<removebrakets> wrote in
message
Is there an alternative to using the default "find record"
feature
you
get
when you use the wizard to applying a command button to a form?
I
would
like to be able to use a wild card in my search field, like if
I
cannot
remember the name of Kimmel I could type in Kim and it would
show me
all
matches. I don't want a report, rather in form view.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top