Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2008

    Question Unanswered: Searching a subform


    I have a client database that I would like to search. I am able to search the main form, however I am unable to search my subform and have the main form display the corresponding record.
    The code I have used to search my main form is as follows:
    Private Sub cmdSearch_Click()

    If Len(cboSearchCriteria) = 0 Or IsNull(cboSearchCriteria) = True Then
    MsgBox "Please select a Search Criteria."

    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
    MsgBox "Please enter something to search for."

    'Generate search criteria
    GCriteria = cboSearchCriteria.Value & " LIKE '*" & txtSearchString & "*'"

    'Filter frmClients based on Search Criteria
    Form_frmClients.RecordSource = "select * from tblClients where " & GCriteria

    'Close frmSearch
    DoCmd.Close acForm, "frmSearch"

    MsgBox "Database has been searched. Matching records are now displayed"

    End If

    End Sub

    My users will select from a drop-down menu the type of information they are looking and then type into a text box the search string.

    When I try to change this code to work with my subform, it will display the correct subform record, but not the corresponding main form record. Everything is connected with a primary code ClientID. The Client Table (main form) and facilty table (sub form) has a one to many relationship. There is a possibility that a client as more than one facility associated with it possible for them all to show up and not just the one matching the search criteria?

    Thanks in advance for you help!

    P.S. I'm sorry if this is in the wrong forum.

  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    Of course you can't. The subform doesn't have access to that data at the time, it can only see the records that are related to the mainform.

    You'll have to do a lot more to achieve your goal, such as perhaps use VBA to find the related records and get the first (?) matching "main" record and then filter the mainform to that record.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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