Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reference to a Field (2000)

    Hello, some time back I given this code to search for records by certain fields on my form

    Private Function DoSearches()
    Dim rs As Object
    Dim strFilter As String

    ' Assemble filter string


    ' Field1 is text
    If Not IsNull(Me.pols) Then
    strFilter = strFilter & _
    " And [policy number] = " & Chr(34) & Me.pols & Chr(34)
    End If

    If strFilter = "" Then
    ' Nothing to search for
    Exit Function
    End If

    ' Get rid of first " And "
    strFilter = Mid(strFilter, 6)

    ' This is the search code
    Set rs = Me.Recordset.Clone
    rs.FindFirst strFilter
    If rs.nomatch Then
    MsgBox ("No Record Found")
    Else
    Me.Bookmark = rs.Bookmark
    End If


    Set rs = Nothing

    End Function

    This worked fine but there has now been a modification in that one of the fields I wish to search under is on the subform but I want the search fields to be on the master form. Using the coding above, how would I write the code assuming policy numbe ris the field to search for and policy number sits on a subform policydetails on a master form policyrecords?

    Many thanks Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reference to a Field (2000)

    Hi Darren,

    Your statement "one of the fields I wish to search under is on the subform but I want the search fields to be on the master form" confuses me. Can you explain what you mean by that? Thanks.

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reference to a Field (2000)

    Sorry Hans, it confuses me too, thought it made sense when I posted it! Lets say text box 'policy number' sits on the subform, but the text box in which the user will type to search for a given 'policy number' sits on the main form. Hope this clarifies.

    Thanks Darren.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reference to a Field (2000)

    Darren,

    Thanks, that makes it clearer indeed. Try the following modification, where I assumed that PolicyDetails is the name of the subform as a control on the main form PolicyRecords.

    Set rs = Me.PolicyDetails.Form.Recordset.Clone
    rs.FindFirst strFilter
    If rs.nomatch Then
    MsgBox ("No Record Found")
    Else
    Me.PolicyDetails.Form.Bookmark = rs.Bookmark
    End If

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •