A textboxt to search through records in a subform based on a memo

[BrianPaul]

I have a form and a subform, Linked by the SubjectID, In the subform I have 3
fields, SubjectID, Scripture, and Remarks, I would like to put a txt box on
the subform that would search through the remarks field and go to that record
in the subform where the field matches part of what the user types into the
text box. I know the code would be placed in the After Update of the text
box. and I know partially it would look something like I use for searching
through queries. Example:
Like "*" & [Type anything you wish to search] & "*" However this is how I
do it in queries but would like to do it on a txt box that is placed on the
subform. Thanks.

 

[BrianPaul]

I didnt add this to the question. When The user puts what he is searching
for in the text box, I would like it to just jump to that record and not to
filter those records containing that text.

 

[Dirk Goldgar]

I have a form and a subform, Linked by the SubjectID, In the subform I have
3
fields, SubjectID, Scripture, and Remarks, I would like to put a txt box
on
the subform that would search through the remarks field and go to that
record
in the subform where the field matches part of what the user types into
the
text box. I know the code would be placed in the After Update of the text
box. and I know partially it would look something like I use for
searching
through queries. Example:
Like "*" & [Type anything you wish to search] & "*" However this is how I
do it in queries but would like to do it on a txt box that is placed on
the
subform. Thanks.

Something like this:

'----- start of example code ------
Private Sub txtSearchRemarks_AfterUpdate()

Dim strSought As String
Const Q As String = """"
Const QQ As String = Q & Q


strSought = Me.txtSearchRemarks & vbNullString

If Len(strSought) > 0 Then

With Me.Recordset

.FindFirst "Remarks Like " & Q & "*" & _
Replace(strSought, Q, QQ) & "*" & Q

If .NoMatch Then
MsgBox "Not found."
End If

End With

End If

End Sub
'----- end of example code ------

 

[BrianPaul]

Thanks for the response, after researching and reading some other posts I
came up with this, which I had to re-post the question because I wasnt
considering filtering and just wanted to navigate to the record. So I came
up with this that worked but, I would have to know all the text to paste in
the field to search on to get it right.

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
'rs.FindFirst "[CustomerID] = " & Me.cboMoveTo
rs.FindFirst "[remarks] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub

So now I am left with the challenge of using the like statement so it will
match up with what I partially type in the search box without knowing all of
it. I hope that makes since.

I have a form and a subform, Linked by the SubjectID, In the subform I have
3
fields, SubjectID, Scripture, and Remarks, I would like to put a txt box
on
the subform that would search through the remarks field and go to that
record
in the subform where the field matches part of what the user types into
the
text box. I know the code would be placed in the After Update of the text
box. and I know partially it would look something like I use for
searching
through queries. Example:
Like "*" & [Type anything you wish to search] & "*" However this is how I
do it in queries but would like to do it on a txt box that is placed on
the
subform. Thanks.

Something like this:

'----- start of example code ------
Private Sub txtSearchRemarks_AfterUpdate()

Dim strSought As String
Const Q As String = """"
Const QQ As String = Q & Q


strSought = Me.txtSearchRemarks & vbNullString

If Len(strSought) > 0 Then

With Me.Recordset

.FindFirst "Remarks Like " & Q & "*" & _
Replace(strSought, Q, QQ) & "*" & Q

If .NoMatch Then
MsgBox "Not found."
End If

End With

End If

End Sub
'----- end of example code ------


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks for the response, after researching and reading some other posts I
came up with this, which I had to re-post the question because I wasnt
considering filtering and just wanted to navigate to the record. So I
came
up with this that worked but, I would have to know all the text to paste
in
the field to search on to get it right.

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
'rs.FindFirst "[CustomerID] = " & Me.cboMoveTo
rs.FindFirst "[remarks] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub

So now I am left with the challenge of using the like statement so it will
match up with what I partially type in the search box without knowing all
of
it. I hope that makes since.

If you're going to search for partial matches, there is no point in using a
combo box, since the whole point of a combo box is to present the user with
a list of valid selections. If I were you, I'd stick with the text box, in
which case the code I posted before -- or something very like it -- should
work.

 

[BrianPaul]

Just what I needed and it worked great. Sorry I got a real serious case of
the Dumb *ss, my apoligies

I have a form and a subform, Linked by the SubjectID, In the subform I have
3
fields, SubjectID, Scripture, and Remarks, I would like to put a txt box
on
the subform that would search through the remarks field and go to that
record
in the subform where the field matches part of what the user types into
the
text box. I know the code would be placed in the After Update of the text
box. and I know partially it would look something like I use for
searching
through queries. Example:
Like "*" & [Type anything you wish to search] & "*" However this is how I
do it in queries but would like to do it on a txt box that is placed on
the
subform. Thanks.

Something like this:

'----- start of example code ------
Private Sub txtSearchRemarks_AfterUpdate()

Dim strSought As String
Const Q As String = """"
Const QQ As String = Q & Q


strSought = Me.txtSearchRemarks & vbNullString

If Len(strSought) > 0 Then

With Me.Recordset

.FindFirst "Remarks Like " & Q & "*" & _
Replace(strSought, Q, QQ) & "*" & Q

If .NoMatch Then
MsgBox "Not found."
End If

End With

End If

End Sub
'----- end of example code ------


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)