Results 1 to 2 of 2
  1. #1
    Join Date
    May 2005
    Posts
    1,191

    Unanswered: WHERE criteria on sub-table

    I have a db that has a form that allows the users to do a search. The user can enter various criteria, the form then generates a WHERE string, and opens a report with this filter. The form has various criteria on it, but what this post is concerned with is a "Search All" field. There's a textbox that a user can enter in any phrase to search on. I use the following code to achieve this part of it:
    Code:
    Dim frm As Form, ctl As Control, varItm As Variant, strTemp As String, _
        i As Integer, intMax As Integer, blnAnySelected As Boolean
    Set frm = Forms!Frm_Search
    
    'build WHERE string for report
    Dim strWHERE As String
    strWHERE = ""
    
    'keyword criteria
    Set ctl = frm!TxtBox_All_Text
    If Nz(ctl, "") <> "" Then
        If strWHERE <> "" Then strWHERE = strWHERE & " AND "
        strWHERE = strWHERE & "(" & _
                                "(TblMain.[FldMainKey]) Like '*" & ctl & "*'" & _
                                " OR (TblMain.[Fld2]) Like '*" & ctl & "*'" & _
                                " OR (TblMain.[Fld3]) Like '*" & ctl & "*'" & _
                                " OR (TblMain.[Fld4]) Like '*" & ctl & "*'" & _
                                " OR (TblMain.[Fld5]) Like '*" & ctl & "*'" & _
                                ")"
    End If
    This works fine.

    However, what I'd like to do involves a sub table of this main table (with a one-to-many relationship on the starred fields), which have the following layouts:

    TblMain
    *FldMainKey
    Fld2
    Fld3
    Fld4
    Fld5

    TblSub
    FldSubKey
    *FldMainKey
    FldA
    FldB

    Is it possible to search on fields FldA and FldB for the same phrase I'm using for the main table, and include this in the filter?
    Me.Geek = True

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    JOIN the tables and add additional WHERE clauses..?
    George
    Home | Blog

Posting Permissions

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