Find Command

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


"JennKriv" wrote:

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?

"Klatuu" wrote:

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


"Dale Fye" wrote:

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.



"JennKriv" wrote:

Where would I put the find next

"Dale Fye" wrote:

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.



"JennKriv" wrote:

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.