Using Findfirst recordset to search memo field type

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
 
T

Tom van Stiphout

On Sun, 4 Apr 2010 20:51:01 -0700, Dale

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

-Tom.
Microsoft Access MVP
 
D

Dale

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
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
 
D

Dirk Goldgar

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
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


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?
 
D

Dale

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
.
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.

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.
 
D

Dirk Goldgar

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.

Add a line

Debug.Print strsql

..... just before the call to FindFirst, and report what that shows.
 
D

David W. Fenton

Can anyone enlighten me on why this won't work?

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.
 
D

Dale

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.
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
 
D

Dirk Goldgar

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.


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.
 
D

Dirk Goldgar

David W. Fenton said:
Isn't there a function in there to return HTML memo fields as plain
text?


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.
 
D

Dirk Goldgar

David W. Fenton said:
Isn't there a function in there to return HTML memo fields as plain
text?


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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top