Programming Tool Box/SIMS_2.iso/access/diverses/sbfrmf/sbfrmf.txt

Text File | 1995-02-27 | 2KB | 46 lines

Function SearchSubform ()
    Dim strCriteria As String, strField  As String, strLinkChild As String
    Dim strLinkMaster As String, rsForm  As Recordset, rsSubForm As Recordset
    Dim strSearchValue As String, i As Integer, db As Database, varLinkValue As Variant

    'Identify the name of the control being searched
    'and the names of the fields involved in the linking relationship

    strField = Screen.activecontrol.Name
    strLinkChild = CStr(Me![Contacts].LinkChildFields)
    strLinkMaster = CStr(Me![Contacts].LinkMasterFields)
    strSearchValue = InputBox$("Please enter value to search for:", "Searching in " & strField)

    'Build the search criteria and search
    'Note that you search the underlying table or query of the subform.
    'You don't search the recordset clone of the subform, as this only holds
    'records matching the current main form record.

    Set db = dbEngine(0)(0)
    Set rsSubForm = db.OpenRecordset("Contacts", DB_OPEN_DYNASET)
    strCriteria = strField & " like " & Chr$(39) & strSearchValue & Chr$(42) & Chr$(39)
    rsSubForm.FindFirst strCriteria

    'On finding the subform record, note the value of the subform link field.
    'This value will have a match on the main form.

    If Not rsSubForm.nomatch Then
        For i = 0 To rsSubForm.Fields.count - 1
            If rsSubForm.Fields(i).Name = strLinkChild Then
                varLinkValue = rsSubForm.Fields(i).value
                Exit For
            End If
        Next

        'On the main form, search for the record with the matching link field value.
        'Use the bookmark property to display that record.

        Set rsForm = Me.recordsetclone
        strCriteria = strLinkMaster & " like " & Chr$(39) & varLinkValue & Chr$(42) & Chr$(39)
        rsForm.FindFirst strCriteria
        If Not rsForm.nomatch Then
            Me.bookmark = rsForm.bookmark
        End If
    End If
End Function