Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    14

    Unanswered: Multiple Table Search Form

    I have a form that allows the user to enter data in to an unbound text box which will compare the data two different tables and then when they click on the button to execute the search it will display a form with a sub-form containing all of the matching records. It works fine for the first two fields of the form, comparing to the Customer table, but will not for the other 4 fields, ReceiptBook table. How do I get it to recognize that those 4 fields are fields in another table. Thank you in advance for your assistance.

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    How are you doing your search?

    Are the tables related somehow, maybe a PK/FK relationship?

    Are the results you display from one table or a combination of both?

    Steve

  3. #3
    Join Date
    May 2003
    Posts
    14
    The tables have a one to many relationship based on Customer_ID. The results are displayed from both tables. Here is the code for the search ...

    Private Sub Toggle17_Click()
    On Error GoTo Err_Toggle17_Click

    Dim stCriteriaFilter As String

    If Forms!FRM_Reciept_Filter!F_Customer_Last <> "" Then
    stCriteriaFilter = stCriteriaFilter & "C_Customer_Last = '" & Forms!FRM_Reciept_Filter!F_Customer_Last & "'" & " AND "
    End If

    If Forms!FRM_Reciept_Filter!F_Customer_First <> "" Then
    stCriteriaFilter = stCriteriaFilter & "C_Customer_First = '" & Forms!FRM_Reciept_Filter!F_Customer_First & "'" & " AND "
    End If

    If Forms!FRM_Reciept_Filter!F_Catalog <> "" Then
    stCriteriaFilter = stCriteriaFilter & "[TBL_ReceiptBook]![R_Catalog] = '" & Forms!FRM_Reciept_Filter!F_Catalog & "'" & " AND "
    End If

    If Forms!FRM_Reciept_Filter!F_Insider <> "" Then
    stCriteriaFilter = stCriteriaFilter & "R_Insider = '" & Forms!FRM_Reciept_Filter!F_Insider & "'" & " AND "
    End If

    If Forms!FRM_Reciept_Filter!F_From_Date <> "" And Forms!FRM_Reciept_Filter!F_To_Date <> "" Then
    stCriteriaFilter = stCriteriaFilter & "R_Date >= #" & Forms!FRM_Reciept_Filter!F_From_Date & "#" & _
    " AND R_Date <= #" & Forms!FRM_Reciept_Filter!F_To_Date & "#" & " AND "
    End If

    If Forms!FRM_Reciept_Filter!F_Item_Number <> "" Then
    stCriteriaFilter = stCriteriaFilter & "[Table]![TBL_ReceiptBook]![R_Item_Number] = '" & Forms!FRM_Reciept_Filter!F_Item_Number & "'" & " AND "
    End If

    If Len(stCriteriaFilter) > 0 Then
    stCriteriaFilter = Left(stCriteriaFilter, Len(stCriteriaFilter) - 5)
    ' For debug-purpose only ...
    Debug.Print "CriteriaFilter: " & stCriteriaFilter
    End If

    DoCmd.OpenForm "FRM_Customer", wherecondition:=stCriteriaFilter

    Exit_Toggle17_Click:
    Exit Sub

Posting Permissions

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