Search Textbox on Main Form

[beckynickie]

Hello,

I am new to Access VBA. I have a Main form with a subform on it. The tables are related through a 1 to Many relationship via field "IMIM". I am attempting to search all records for a subform field ("PROP") through a textbox search on the Main form with a "Go" button. If found, I want to GoTo the subform record and related Main form record. The code below gets me to the right Main form record, but only the first subform record. Any ideas how to take the final step? I would also appreciate any improvements/advice on the existing code.

Thank you! :)


Option Compare Database
Public recordFound As Boolean
Public imimFound As Boolean


Private Sub Command574_Click()
Dim derivedSearchBoxValue
recordFound = False
imimFound = False
derivedSearchBoxValue = Me.KeywordSearch

Me.Refresh
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim varBookmark As Variant
Dim MainFormIMIM As Double
MainFormIMIM = Me.IMIM

'find the subform record with the IMIM = to the MainFormIMIM value

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM [tbl_Subform]")
rst.MoveFirst

If rst.AbsolutePosition > -1 Then
'Force the entire recordset to load
rst.MoveLast
rst.MoveFirst


Do While rst.EOF = False
If rst("PROP") = derivedSearchBoxValue Then
Dim saveIMIM
saveIMIM = rst("IMIM")
imimFound = True
Exit Do
Else
rst.MoveNext
End If
Loop
rst.Close
End If

Set rst = dbs.OpenRecordset("SELECT * FROM [tbl_Mainform]")
rst.MoveFirst
If rst.AbsolutePosition > -1 Then
'Force the entire recordset to load
rst.MoveLast
rst.MoveFirst

Do While rst.EOF = False
If rst("IMIM") = saveIMIM Then
varBookmark = rst.Bookmark
recordFound = True
Exit Do
Else
rst.MoveNext
End If
Loop


End If
If (imimFound = False Or recordFound = False) Then
MsgBox ("Record not found")
Me.KeywordSearch.Value = ""
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Sub
End If
rst.Bookmark = varBookmark
'MsgBox (rst.AbsolutePosition)
DoCmd.GoToRecord acDataForm, "frm_Main", acGoTo, rst.AbsolutePosition + 1
Me.Refresh
rst.Close
Me.Refresh
Set rst = Nothing
Set dbs = Nothing
End Sub

[jonh]

Forms have a recordset and a recordsetclone, so no need to open other recordsets.


Private Sub Command4_Click()
Me.Bookmark = findrecord(Me, Me.KeywordSearch, "IMIM")
me.subform.Form.Bookmark = findrecord(me.subform.Form, Me.KeywordSearch, "IMIM")
End Sub


Private Function findrecord(f As Form, s As String, fld As String) As Byte()
With f.RecordsetClone
.MoveFirst
Do Until .EOF
If .Fields(fld) = s Then
findrecord = .Bookmark
Exit Do
End If
.MoveNext
Loop
End With
End Function