Using Findfirst recordset to search memo field type

  • Thread starter Thread starter Dale
  • Start date Start date
D

Dale

Can anyone enlighten me on why this won't work?
I'm using findfirst to search for a call phrase in a memo field, phrases
listed in the callphrases table.
It worked once!! and then stopped...any help is appreciated, using Access 2007

Dim db As Database, strsql As String, rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblcallphrases", dbOpenDynaset)
'strsql = "[CallPhrase]='" & ([Forms]![frmConcernlog]![tblevents
subform].[Form]![eInvestigation]) & "'"
rst.MoveFirst
rst.FindFirst strsql
If rst.NoMatch Then
'do something exit sub
Else
Debug.Print rst!callphrase
End If
rst.Close
Set rst = Nothing
Set db = Nothing
 
On Sun, 4 Apr 2010 20:51:01 -0700, Dale

The line:
strsql = ....
is commented out.

-Tom.
Microsoft Access MVP
 
Dale said:
Can anyone enlighten me on why this won't work?
I'm using findfirst to search for a call phrase in a memo field, phrases
listed in the callphrases table.
It worked once!! and then stopped...any help is appreciated, using Access 2007
Click to expand...
Note reposting correct code...
Dim db As Database, strsql As String, rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from tblcallphrases", dbOpenDynaset)
strsql = "[CallPhrase]='" & ([Forms]![frmConcernlog]![tblevents
subform].[Form]![eInvestigation]) & "'"
rst.MoveFirst
rst.FindFirst strsql
If rst.NoMatch Then
'do something exit sub
Else
Debug.Print rst!callphrase
End If
rst.Close
Set rst = Nothing
Set db = Nothing
 
Dale said:
Dale said:
Can anyone enlighten me on why this won't work?
I'm using findfirst to search for a call phrase in a memo field, phrases
listed in the callphrases table.
It worked once!! and then stopped...any help is appreciated, using Access
2007
Click to expand...
Note reposting correct code...
Dim db As Database, strsql As String, rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from tblcallphrases", dbOpenDynaset)
strsql = "[CallPhrase]='" & ([Forms]![frmConcernlog]![tblevents
subform].[Form]![eInvestigation]) & "'"
rst.MoveFirst
rst.FindFirst strsql
If rst.NoMatch Then
'do something exit sub
Else
Debug.Print rst!callphrase
End If
rst.Close
Set rst = Nothing
Set db = Nothing
Click to expand...


In what way exactly does that code not work?

What is the value of strsql when you call the FindFirst method?

Is the memo field [CallPhrase] a rich text field?
 
Tom van Stiphout said:
On Sun, 4 Apr 2010 20:51:01 -0700, Dale

The line:
strsql = ....
is commented out.

-Tom.
Microsoft Access MVP

Can anyone enlighten me on why this won't work?
I'm using findfirst to search for a call phrase in a memo field, phrases
listed in the callphrases table.
It worked once!! and then stopped...any help is appreciated, using Access 2007

Dim db As Database, strsql As String, rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("tblcallphrases", dbOpenDynaset)
'strsql = "[CallPhrase]='" & ([Forms]![frmConcernlog]![tblevents
subform].[Form]![eInvestigation]) & "'"
rst.MoveFirst
rst.FindFirst strsql
If rst.NoMatch Then
'do something exit sub
Else
Debug.Print rst!callphrase
End If
rst.Close
Set rst = Nothing
Set db = Nothing
Click to expand...
.
The strsql string was commented out in the post in error..I was testing with a different strsql format and didn't uncomment the line before posting. I had reposted with the strsql correctly.
Click to expand...

The code does not find any of the phrases in the callphrases table even
though they exist. If I replace the strsql string with "[callphrase]='" &
"""called client""", the code works. Its only when I'm referencing the forms
control does it not work or so it appears. Changing the memo type to text did
not make a difference. The memo field is set to plain text. So I'm at a loss.
Thanks again.
 
Dale said:
The code does not find any of the phrases in the callphrases table even
though they exist. If I replace the strsql string with "[callphrase]='" &
"""called client""", the code works. Its only when I'm referencing the
forms
control does it not work or so it appears. Changing the memo type to text
did
not make a difference. The memo field is set to plain text. So I'm at a
loss.
Click to expand...

Add a line

Debug.Print strsql

..... just before the call to FindFirst, and report what that shows.
 
Can anyone enlighten me on why this won't work?
Click to expand...

Why are you walking through the records to do this? Why not present
a list of the records with the phrase and let the user click on each
in turn to pull up each record? Or just filter the form to display
only the matching records?

I implement lots of functionality to allow users to find records,
but have not once used walking a recordset as the way I implemented
it.
 
David W. Fenton said:
Why are you walking through the records to do this? Why not present
a list of the records with the phrase and let the user click on each
in turn to pull up each record? Or just filter the form to display
only the matching records?

I implement lots of functionality to allow users to find records,
but have not once used walking a recordset as the way I implemented
it.
Click to expand...
I'm using the NoMatch = False to trigger a msgbox to the user having them
confirm the action i.e. msgbox "pls confirm you have called the client."

To Dirk's question, when I debug the sqlstr every once in a while I get
this: [CallPhrase]='<div>called patient</div>' which explains why the strsql
failed, seems when I edit the memo field I get the <div> tags even though
field is set to plain text. This was only visible in the table and not the
form (I was mostly troubleshooting from the form). So this code does work,
but now my new problem, the code works only with exact matches, I was hoping
findfirst would find partial matches i.e. the call phrase = called patient
but if the from I'm searching against has "Once the investigation was
completed the patient was called", the match fails.

I'm thinking I need to find another way of doing this.

Thanks all
 
Dale said:
To Dirk's question, when I debug the sqlstr every once in a while I get
this: [CallPhrase]='<div>called patient</div>' which explains why the
strsql
failed, seems when I edit the memo field I get the <div> tags even though
field is set to plain text. This was only visible in the table and not
the
form (I was mostly troubleshooting from the form). So this code does work,
but now my new problem, the code works only with exact matches, I was
hoping
findfirst would find partial matches i.e. the call phrase = called patient
but if the from I'm searching against has "Once the investigation was
completed the patient was called", the match fails.

I'm thinking I need to find another way of doing this.
Click to expand...


You can specify the Like operator in your FindFirst criteria string:

strsql = "[CallPhrase] Like '*" & _
[Forms]![frmConcernlog]![tblevents subform].[Form]![eInvestigation]
& _
"*'"

(though you'd need to remove the <div> and </div> tags).

However, that will still not find that second string you mentioned. So you
do need to think about it a bit more.
 
David W. Fenton said:
Isn't there a function in there to return HTML memo fields as plain
text?
Click to expand...


I don't know -- I haven't explored that aspect of Access 2007 yet. I'm a
little puzzled, because Dale said the memo field was set to Plain Text. I
wonder if it was previously set to Rich Text, had some data entered that
way, and then was changed to Plain Text.
 
David W. Fenton said:
Isn't there a function in there to return HTML memo fields as plain
text?
Click to expand...


There is; it's called PlainText(). You pass it the value of the rich text
field, and it strips out the HTML tags and returns the plain-text version of
it. Oddly, there doesn't seem to be a RichText() function to take a
plain-text string, wrap it in <div></div> tags, and convert any necessary
embedded HTML elements (such as left-angle-brackets) into character codes.
You'd think there ought to be something like that.
 
Back
Top