Find Command

[JennKriv]

I have a find button that I have set up to look in a particular text box. My
problem with it is that It is always set to find the whole part of the box
while I want it to look for any part of it. ie: I want to find a customer but
am unsure as to how it is entered exactly in the form.

 

[Dale Fye]

Jenn,

When I do this, I generally have an unbound textbox (txt_Find) in the forms
header, and a command button (cmd_Find) right next to it.

Assuming you are looking for a last name in the field [Last_Name], then my
code might look like:

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone
rs.findfirst strCriteria
if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing
end sub

You might want to add a find next in there too, so that you could search for
another instance of the string (especially if the field you are searching on
is not the sort order of the form).

HTH
Dale

 

[JennKriv]

Where would I put the find next

Jenn,

When I do this, I generally have an unbound textbox (txt_Find) in the forms
header, and a command button (cmd_Find) right next to it.

Assuming you are looking for a last name in the field [Last_Name], then my
code might look like:

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone
rs.findfirst strCriteria
if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing
end sub

You might want to add a find next in there too, so that you could search for
another instance of the string (especially if the field you are searching on
is not the sort order of the form).

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I have a find button that I have set up to look in a particular text box. My
problem with it is that It is always set to find the whole part of the box
while I want it to look for any part of it. ie: I want to find a customer but
am unsure as to how it is entered exactly in the form.

 

[Dale Fye]

The easiest way would probably be to start out with code in the Change event
of txt_Find, something like:

Private Sub txt_Find_Change

me.cmd_Find.Caption = "&Find"

End Sub

Then, modify the cmd_Find code to look like (this is untested):

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone

if me.cmd_Find.Caption = "&Find" Then
me.cmd_Find.Caption = "&Find Next"
rs.findfirst strCriteria
else
rs.FindNext strCriteria
endif

if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing

end sub


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Where would I put the find next

Jenn,

When I do this, I generally have an unbound textbox (txt_Find) in the forms
header, and a command button (cmd_Find) right next to it.

Assuming you are looking for a last name in the field [Last_Name], then my
code might look like:

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone
rs.findfirst strCriteria
if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing
end sub

You might want to add a find next in there too, so that you could search for
another instance of the string (especially if the field you are searching on
is not the sort order of the form).

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I have a find button that I have set up to look in a particular text box. My
problem with it is that It is always set to find the whole part of the box
while I want it to look for any part of it. ie: I want to find a customer but
am unsure as to how it is entered exactly in the form.

 

[Klatuu]

Good, Dale, but one problem.
You don't want to use the Change event. It means what it says. It fires
everytime you make any change to the value of the control which means it will
fire after every keystroke. It really should be the After Update event and
the test to change it should be after you do the find. Actually, now that I
think about it, I would do it all in the Click event event:

Private sub cmd_Find_Click
Dim strCriteria As String

strCriteria = "[Last_Name] Like ""*" & me.txt_Find & "*"""

With Me.RecordsetClone
If Me.cmd_Find.Caption = "&Find" Then
me.cmd_Find.Caption = "&Find Next"
.findfirst strCriteria
If .NoMatch Then
Msgbox "Could not find a last name similar to '" & me.txt_Find
Else
Me.Bookmark = .Bookmark
Me.cmd_Find.Caption = "&Find Next"
End If
Else
.FindNext strCriteria
If .NoMatch Then
Msgbox "No More Last Names Matching '" & me.txt_Find
Me.cmd_Find.Caption = "&Find"
End If
End If
End Sub

And, to keep the Find/FindNext displaying correctly, put
Me.cmd_Find.Caption = "&Find"
in the form Current event and in the Got Focus event of txt_Find

--
Dave Hargis, Microsoft Access MVP

The easiest way would probably be to start out with code in the Change event
of txt_Find, something like:

Private Sub txt_Find_Change

me.cmd_Find.Caption = "&Find"

End Sub

Then, modify the cmd_Find code to look like (this is untested):

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone

if me.cmd_Find.Caption = "&Find" Then
me.cmd_Find.Caption = "&Find Next"
rs.findfirst strCriteria
else
rs.FindNext strCriteria
endif

if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing

end sub


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Where would I put the find next

Jenn,

When I do this, I generally have an unbound textbox (txt_Find) in the forms
header, and a command button (cmd_Find) right next to it.

Assuming you are looking for a last name in the field [Last_Name], then my
code might look like:

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone
rs.findfirst strCriteria
if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing
end sub

You might want to add a find next in there too, so that you could search for
another instance of the string (especially if the field you are searching on
is not the sort order of the form).

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I have a find button that I have set up to look in a particular text box. My
problem with it is that It is always set to find the whole part of the box
while I want it to look for any part of it. ie: I want to find a customer but
am unsure as to how it is entered exactly in the form.

 

[JennKriv]

I got a compile error comming up at the Me.txt_Find with the .txt_find
highlighted I can't figure out what it needs to be changed to.

Good, Dale, but one problem.
You don't want to use the Change event. It means what it says. It fires
everytime you make any change to the value of the control which means it will
fire after every keystroke. It really should be the After Update event and
the test to change it should be after you do the find. Actually, now that I
think about it, I would do it all in the Click event event:

Private sub cmd_Find_Click
Dim strCriteria As String

strCriteria = "[Last_Name] Like ""*" & me.txt_Find & "*"""

With Me.RecordsetClone
If Me.cmd_Find.Caption = "&Find" Then
me.cmd_Find.Caption = "&Find Next"
.findfirst strCriteria
If .NoMatch Then
Msgbox "Could not find a last name similar to '" & me.txt_Find
Else
Me.Bookmark = .Bookmark
Me.cmd_Find.Caption = "&Find Next"
End If
Else
.FindNext strCriteria
If .NoMatch Then
Msgbox "No More Last Names Matching '" & me.txt_Find
Me.cmd_Find.Caption = "&Find"
End If
End If
End Sub

And, to keep the Find/FindNext displaying correctly, put
Me.cmd_Find.Caption = "&Find"
in the form Current event and in the Got Focus event of txt_Find

--
Dave Hargis, Microsoft Access MVP

The easiest way would probably be to start out with code in the Change event
of txt_Find, something like:

Private Sub txt_Find_Change

me.cmd_Find.Caption = "&Find"

End Sub

Then, modify the cmd_Find code to look like (this is untested):

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone

if me.cmd_Find.Caption = "&Find" Then
me.cmd_Find.Caption = "&Find Next"
rs.findfirst strCriteria
else
rs.FindNext strCriteria
endif

if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing

end sub


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Where would I put the find next

:

Jenn,

When I do this, I generally have an unbound textbox (txt_Find) in the forms
header, and a command button (cmd_Find) right next to it.

Assuming you are looking for a last name in the field [Last_Name], then my
code might look like:

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone
rs.findfirst strCriteria
if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing
end sub

You might want to add a find next in there too, so that you could search for
another instance of the string (especially if the field you are searching on
is not the sort order of the form).

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I have a find button that I have set up to look in a particular text box. My
problem with it is that It is always set to find the whole part of the box
while I want it to look for any part of it. ie: I want to find a customer but
am unsure as to how it is entered exactly in the form.

 

[JennKriv]

I also want to look up a number on a different form. I think this may be why
I got the compile error. how do I change that to look for a number value?

Good, Dale, but one problem.
You don't want to use the Change event. It means what it says. It fires
everytime you make any change to the value of the control which means it will
fire after every keystroke. It really should be the After Update event and
the test to change it should be after you do the find. Actually, now that I
think about it, I would do it all in the Click event event:

Private sub cmd_Find_Click
Dim strCriteria As String

strCriteria = "[Last_Name] Like ""*" & me.txt_Find & "*"""

With Me.RecordsetClone
If Me.cmd_Find.Caption = "&Find" Then
me.cmd_Find.Caption = "&Find Next"
.findfirst strCriteria
If .NoMatch Then
Msgbox "Could not find a last name similar to '" & me.txt_Find
Else
Me.Bookmark = .Bookmark
Me.cmd_Find.Caption = "&Find Next"
End If
Else
.FindNext strCriteria
If .NoMatch Then
Msgbox "No More Last Names Matching '" & me.txt_Find
Me.cmd_Find.Caption = "&Find"
End If
End If
End Sub

And, to keep the Find/FindNext displaying correctly, put
Me.cmd_Find.Caption = "&Find"
in the form Current event and in the Got Focus event of txt_Find

--
Dave Hargis, Microsoft Access MVP

The easiest way would probably be to start out with code in the Change event
of txt_Find, something like:

Private Sub txt_Find_Change

me.cmd_Find.Caption = "&Find"

End Sub

Then, modify the cmd_Find code to look like (this is untested):

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone

if me.cmd_Find.Caption = "&Find" Then
me.cmd_Find.Caption = "&Find Next"
rs.findfirst strCriteria
else
rs.FindNext strCriteria
endif

if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing

end sub


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Where would I put the find next

:

Jenn,

When I do this, I generally have an unbound textbox (txt_Find) in the forms
header, and a command button (cmd_Find) right next to it.

Assuming you are looking for a last name in the field [Last_Name], then my
code might look like:

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone
rs.findfirst strCriteria
if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing
end sub

You might want to add a find next in there too, so that you could search for
another instance of the string (especially if the field you are searching on
is not the sort order of the form).

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I have a find button that I have set up to look in a particular text box. My
problem with it is that It is always set to find the whole part of the box
while I want it to look for any part of it. ie: I want to find a customer but
am unsure as to how it is entered exactly in the form.

 

[Klatuu]

Do you have a control on your form named txt_Find? It should be the name of
the text box control you want to do the search on.

When asking about an error, it helps to point out the line and include the
error number or message.
--
Dave Hargis, Microsoft Access MVP

I got a compile error comming up at the Me.txt_Find with the .txt_find
highlighted I can't figure out what it needs to be changed to.

Good, Dale, but one problem.
You don't want to use the Change event. It means what it says. It fires
everytime you make any change to the value of the control which means it will
fire after every keystroke. It really should be the After Update event and
the test to change it should be after you do the find. Actually, now that I
think about it, I would do it all in the Click event event:

Private sub cmd_Find_Click
Dim strCriteria As String

strCriteria = "[Last_Name] Like ""*" & me.txt_Find & "*"""

With Me.RecordsetClone
If Me.cmd_Find.Caption = "&Find" Then
me.cmd_Find.Caption = "&Find Next"
.findfirst strCriteria
If .NoMatch Then
Msgbox "Could not find a last name similar to '" & me.txt_Find
Else
Me.Bookmark = .Bookmark
Me.cmd_Find.Caption = "&Find Next"
End If
Else
.FindNext strCriteria
If .NoMatch Then
Msgbox "No More Last Names Matching '" & me.txt_Find
Me.cmd_Find.Caption = "&Find"
End If
End If
End Sub

And, to keep the Find/FindNext displaying correctly, put
Me.cmd_Find.Caption = "&Find"
in the form Current event and in the Got Focus event of txt_Find

--
Dave Hargis, Microsoft Access MVP

The easiest way would probably be to start out with code in the Change event
of txt_Find, something like:

Private Sub txt_Find_Change

me.cmd_Find.Caption = "&Find"

End Sub

Then, modify the cmd_Find code to look like (this is untested):

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone

if me.cmd_Find.Caption = "&Find" Then
me.cmd_Find.Caption = "&Find Next"
rs.findfirst strCriteria
else
rs.FindNext strCriteria
endif

if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing

end sub


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Where would I put the find next

:

Jenn,

When I do this, I generally have an unbound textbox (txt_Find) in the forms
header, and a command button (cmd_Find) right next to it.

Assuming you are looking for a last name in the field [Last_Name], then my
code might look like:

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone
rs.findfirst strCriteria
if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing
end sub

You might want to add a find next in there too, so that you could search for
another instance of the string (especially if the field you are searching on
is not the sort order of the form).

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I have a find button that I have set up to look in a particular text box. My
problem with it is that It is always set to find the whole part of the box
while I want it to look for any part of it. ie: I want to find a customer but
am unsure as to how it is entered exactly in the form.

 

[Dale Fye]

Dave,

Actually, I like the idea of changing it back to '&Find' in the GotFocus
event of txt_Find; that makes more sense than the Change event. But if you
do that in the forms Current event as well, won't that change it back to
'&Find' every time you find a match? In which case, you would never get a
'&FindNext', would you?

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Good, Dale, but one problem.
You don't want to use the Change event. It means what it says. It fires
everytime you make any change to the value of the control which means it will
fire after every keystroke. It really should be the After Update event and
the test to change it should be after you do the find. Actually, now that I
think about it, I would do it all in the Click event event:

Private sub cmd_Find_Click
Dim strCriteria As String

strCriteria = "[Last_Name] Like ""*" & me.txt_Find & "*"""

With Me.RecordsetClone
If Me.cmd_Find.Caption = "&Find" Then
me.cmd_Find.Caption = "&Find Next"
.findfirst strCriteria
If .NoMatch Then
Msgbox "Could not find a last name similar to '" & me.txt_Find
Else
Me.Bookmark = .Bookmark
Me.cmd_Find.Caption = "&Find Next"
End If
Else
.FindNext strCriteria
If .NoMatch Then
Msgbox "No More Last Names Matching '" & me.txt_Find
Me.cmd_Find.Caption = "&Find"
End If
End If
End Sub

And, to keep the Find/FindNext displaying correctly, put
Me.cmd_Find.Caption = "&Find"
in the form Current event and in the Got Focus event of txt_Find

--
Dave Hargis, Microsoft Access MVP

The easiest way would probably be to start out with code in the Change event
of txt_Find, something like:

Private Sub txt_Find_Change

me.cmd_Find.Caption = "&Find"

End Sub

Then, modify the cmd_Find code to look like (this is untested):

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone

if me.cmd_Find.Caption = "&Find" Then
me.cmd_Find.Caption = "&Find Next"
rs.findfirst strCriteria
else
rs.FindNext strCriteria
endif

if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing

end sub


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Where would I put the find next

:

Jenn,

When I do this, I generally have an unbound textbox (txt_Find) in the forms
header, and a command button (cmd_Find) right next to it.

Assuming you are looking for a last name in the field [Last_Name], then my
code might look like:

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone
rs.findfirst strCriteria
if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing
end sub

You might want to add a find next in there too, so that you could search for
another instance of the string (especially if the field you are searching on
is not the sort order of the form).

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I have a find button that I have set up to look in a particular text box. My
problem with it is that It is always set to find the whole part of the box
while I want it to look for any part of it. ie: I want to find a customer but
am unsure as to how it is entered exactly in the form.

 

[Klatuu]

You are correct. I forgot it is changing records. It should only do it in
the text box Got Focus event.
Good catch
--
Dave Hargis, Microsoft Access MVP

Dave,

Actually, I like the idea of changing it back to '&Find' in the GotFocus
event of txt_Find; that makes more sense than the Change event. But if you
do that in the forms Current event as well, won't that change it back to
'&Find' every time you find a match? In which case, you would never get a
'&FindNext', would you?

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Good, Dale, but one problem.
You don't want to use the Change event. It means what it says. It fires
everytime you make any change to the value of the control which means it will
fire after every keystroke. It really should be the After Update event and
the test to change it should be after you do the find. Actually, now that I
think about it, I would do it all in the Click event event:

Private sub cmd_Find_Click
Dim strCriteria As String

strCriteria = "[Last_Name] Like ""*" & me.txt_Find & "*"""

With Me.RecordsetClone
If Me.cmd_Find.Caption = "&Find" Then
me.cmd_Find.Caption = "&Find Next"
.findfirst strCriteria
If .NoMatch Then
Msgbox "Could not find a last name similar to '" & me.txt_Find
Else
Me.Bookmark = .Bookmark
Me.cmd_Find.Caption = "&Find Next"
End If
Else
.FindNext strCriteria
If .NoMatch Then
Msgbox "No More Last Names Matching '" & me.txt_Find
Me.cmd_Find.Caption = "&Find"
End If
End If
End Sub

And, to keep the Find/FindNext displaying correctly, put
Me.cmd_Find.Caption = "&Find"
in the form Current event and in the Got Focus event of txt_Find

--
Dave Hargis, Microsoft Access MVP

The easiest way would probably be to start out with code in the Change event
of txt_Find, something like:

Private Sub txt_Find_Change

me.cmd_Find.Caption = "&Find"

End Sub

Then, modify the cmd_Find code to look like (this is untested):

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone

if me.cmd_Find.Caption = "&Find" Then
me.cmd_Find.Caption = "&Find Next"
rs.findfirst strCriteria
else
rs.FindNext strCriteria
endif

if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing

end sub


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Where would I put the find next

:

Jenn,

When I do this, I generally have an unbound textbox (txt_Find) in the forms
header, and a command button (cmd_Find) right next to it.

Assuming you are looking for a last name in the field [Last_Name], then my
code might look like:

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone
rs.findfirst strCriteria
if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing
end sub

You might want to add a find next in there too, so that you could search for
another instance of the string (especially if the field you are searching on
is not the sort order of the form).

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I have a find button that I have set up to look in a particular text box. My
problem with it is that It is always set to find the whole part of the box
while I want it to look for any part of it. ie: I want to find a customer but
am unsure as to how it is entered exactly in the form.

 

[Klatuu]

You would need to duplicate the code in the other form. To use a number, you
need to change the syntax a bit

Private sub cmd_Find_Click
Dim strCriteria As String

strCriteria = "[Number Field] = " & me.txt_Find

With Me.RecordsetClone
If Me.cmd_Find.Caption = "&Find" Then
me.cmd_Find.Caption = "&Find Next"
.findfirst strCriteria
If .NoMatch Then
Msgbox "Could not find a last name similar to '" & me.txt_Find
Else
Me.Bookmark = .Bookmark
Me.cmd_Find.Caption = "&Find Next"
End If
Else
.FindNext strCriteria
If .NoMatch Then
Msgbox "No More Last Names Matching '" & me.txt_Find
Me.cmd_Find.Caption = "&Find"
End If
End If
End Sub


--
Dave Hargis, Microsoft Access MVP

I also want to look up a number on a different form. I think this may be why
I got the compile error. how do I change that to look for a number value?

Good, Dale, but one problem.
You don't want to use the Change event. It means what it says. It fires
everytime you make any change to the value of the control which means it will
fire after every keystroke. It really should be the After Update event and
the test to change it should be after you do the find. Actually, now that I
think about it, I would do it all in the Click event event:

Private sub cmd_Find_Click
Dim strCriteria As String

strCriteria = "[Last_Name] Like ""*" & me.txt_Find & "*"""

With Me.RecordsetClone
If Me.cmd_Find.Caption = "&Find" Then
me.cmd_Find.Caption = "&Find Next"
.findfirst strCriteria
If .NoMatch Then
Msgbox "Could not find a last name similar to '" & me.txt_Find
Else
Me.Bookmark = .Bookmark
Me.cmd_Find.Caption = "&Find Next"
End If
Else
.FindNext strCriteria
If .NoMatch Then
Msgbox "No More Last Names Matching '" & me.txt_Find
Me.cmd_Find.Caption = "&Find"
End If
End If
End Sub

And, to keep the Find/FindNext displaying correctly, put
Me.cmd_Find.Caption = "&Find"
in the form Current event and in the Got Focus event of txt_Find

--
Dave Hargis, Microsoft Access MVP

The easiest way would probably be to start out with code in the Change event
of txt_Find, something like:

Private Sub txt_Find_Change

me.cmd_Find.Caption = "&Find"

End Sub

Then, modify the cmd_Find code to look like (this is untested):

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone

if me.cmd_Find.Caption = "&Find" Then
me.cmd_Find.Caption = "&Find Next"
rs.findfirst strCriteria
else
rs.FindNext strCriteria
endif

if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing

end sub


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Where would I put the find next

:

Jenn,

When I do this, I generally have an unbound textbox (txt_Find) in the forms
header, and a command button (cmd_Find) right next to it.

Assuming you are looking for a last name in the field [Last_Name], then my
code might look like:

Private sub cmd_Find_Click

Dim strCriteria as string
Dim rs as dao.recordset

strCriteria = "[Last_Name] Like '*" & me.txt_Find & "*'"
Dim rs as me.recordsetclone
rs.findfirst strCriteria
if rs.nomatch then
msgbox "Could not find a last name similar to '" & me.txt_Find & "'"
else
me.bookmark = rs.bookmark
endif

rs.close
set rs = nothing
end sub

You might want to add a find next in there too, so that you could search for
another instance of the string (especially if the field you are searching on
is not the sort order of the form).

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

I have a find button that I have set up to look in a particular text box. My
problem with it is that It is always set to find the whole part of the box
while I want it to look for any part of it. ie: I want to find a customer but
am unsure as to how it is entered exactly in the form.