Using Findfirst recordset to search memo field type
in [Forms]
|
Prev: Referencing Forms
Next: Keep getting error from code
From: Dale on 4 Apr 2010 23:51 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
From: Tom van Stiphout on 4 Apr 2010 23:57 On Sun, 4 Apr 2010 20:51:01 -0700, Dale <Dale(a)discussions.microsoft.com> wrote: 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
From: Dale on 4 Apr 2010 23:59 "Dale" wrote: > 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
From: Dirk Goldgar on 5 Apr 2010 00:21 "Dale" <Dale(a)discussions.microsoft.com> wrote in message news:D723DCDE-0D9C-4132-9707-49C8AA7F208E(a)microsoft.com... > > > "Dale" wrote: > >> 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? -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
From: Dale on 5 Apr 2010 08:39
"Tom van Stiphout" wrote: > On Sun, 4 Apr 2010 20:51:01 -0700, Dale > <Dale(a)discussions.microsoft.com> wrote: > > 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. |