FindNext using Like *

[bhammer]

The subdatasheet's current record has a Doc_Number text field and a Comments
memo field. Some records have four digits of the Doc_Number in the Comments
field. I want to find the record with the matching four digits in the
Comments field.

I can get the four digits I want, but I can't get the FindNext method to
work using the string I have built, for example,

[Comments] = Like *0123*

------------------------
'Find the record on Main that matches the SubForm.
Dim rs as Recordset
Dim varBookmark as Variaint
Set rs = Forms.MyForm.Form.Recordset
varBookmark = rs.Bookmark
strSearch = "Like *" & Left(Right(Me.Doc_Number, 8), 4) & "*"

rs.FindNext "[Comments] = " & Chr(34) & strSearch & Chr(34)
If rs.NoMatch Then
rs.Bookmark = varBookmark
MsgBox "There is No matching Document.", , "ByAddressSub_Current"
Else:
.Form.Bookmark = rs.Bookmark
End If

 

[Dale Fye]

I'm assuming you typed this into the newsgroup, rather than cutting and
pasting, because you misspelled Variant in line 3 of your code, and have an
unneeded : after the ELSE clause. I would also use a clone of your forms
recordset, that way you can search it without affecting the currently active
recordset.

Another thing is that you cannot use = and Like in the same criteria, and
have two wrap the * and the text with either single or doubel quotes.

As I understand it, you are executing this code from within the subform, if
that is not correct, post back and explain what form or subform has the
focus when this code is supposed to be run. Assuming, as your first comment
line indicates, that you want to find and move to the record on the main
form, that matches the currently selected record in the subform, I think you
should try:
*********************************************
'Find the record on Main that matches the SubForm.

Dim rs as Recordset
Dim strSearch as string

'Assuming the MyForm is the main form, and you are executing this
'from within the subform, you could use either of the following two
lines.
'Set rs = Me.Parent.Recordsetclone
Set rs = Forms.MyForm.Recordsetclone

'The end of the next line is a " followed by a ', followed by a "
strSearch = "Like '*" & Left(Right(me.Doc_Number, 8), 4) & "'"
rs.findfirst "[Comments] " & strSearch

If rs.NoMatch Then
MsgBox "There is No matching Document.", , "ByAddressSub_Current"
Else
'Again, either of these methods should work
'me.Parent.bookmark = rs.Bookmark
Forms.MyForm.Form.Bookmark = rs.Bookmark
End If

rs.close
set rs = nothing


HTH
Dale

 

[bhammer]

Dale,

Just the input I was looking for. Yes I typed some of the code, thus the
misspellings. I'm (obviously) not clear on recordsets, clones, and what's the
diff when to use one vs. the other.

I have not tested your tips, yet as it's late PM.

I bet there's a way to avoid what I have set-up now, namely that I have the
same control source (a query) behind both the sub and the main forms. The
deal is that the main form displays both documents (i.e., the current and the
matching), and I want the subform's current pointer to stay on the record
while somehow a search for the matching is performed and the result is
displayed on the Main. Maybe that's what a clone is good for.

No equal sign with the Like operator. The quotes are always tricky. And the
closing two statements are good to know. . . thanks for you help.