FindFirst Problem

[Alireza355]

I am trying to have a search in a subform to find records that include a certain word. This word can be at any part of field.

I am having difficaulty figuring it out.

Private Sub Command22_Click()
Dim rst As Recordset
If Not IsNull(Text1) Then
Set rst = [Forms]![MyForm]![MySubform].Form.RecordsetClone
 
rst.FindFirst "[MyField] Like """ & Me.Text1 & "*"""
 
If Not rst.NoMatch Then
[Forms]![MyForm]![MySubform].Form.Bookmark = rst.Bookmark
Else
 
MsgBox "Not Found!"
End If
End If
 
rst.Close
Set rst = Nothing
 
End Sub
 

But this code only searches for records that start with that certain word. if the word is in the middle of the field, it does not bookmark that record.

In a normal query, I use this:

MyField Like "*" & [forms]![Myform]![text1] & "*"

But I don't know how to do the same with FindFirst

Can anyone provide any help?????

Thanx a lot

[ADezii]

Dim rst As Recordset
 
If IsNull(Text1) Then Exit Sub
 
Set rst = [Forms]![MyForm]![MySubform].Form.RecordsetClone
 
rst.FindFirst "[MyField] Like ""*" & Me.Text1 & "*"""
 
If Not rst.NoMatch Then
  [Forms]![frmMain]![Child].Form.Bookmark = rst.Bookmark
Else
  MsgBox "Not Found!"
End If
 
rst.Close
Set rst = Nothing
 

[GazMathias]

I usually approach this in one of two ways.

Either I delete and redeclare the querydefs on the the query behind the subform or I include { Like "*" & [Forms]![Someform]![Somefield] & "*" } in the criteria of the field being searched for the query behind the form.

I nearly always create a query for form data as it is allows you the freedom to manipulate it in this way.

Obviously which one I choose depends on the form's function but they generally both work in their respective circumstance.

Gaz.

[Alireza355]

Thank you Adezii,

it works perfectly...

[ADezii]

@Alireza355
You are quite welcome.