Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Filter / Search in two tables

Discussion in 'Information Technology' started by MarkusJohn, Jul 28, 2006.

  1. MarkusJohn

    MarkusJohn
    Expand Collapse
    Guest

    I have a form.. in the header there are unbound search fields. The
    entered values are concatenated to a string. When you press the filter
    button in the form all the found entries appear.

    The searchable fields are not only based on one table.
    In my case I have the tables order and customer. primary keys for order
    is order no and for customer custid. When I created the form I used the
    wizzard and selected all required fields. But when I look for a
    customers name the realtion order customer is not linked over name but
    id. So I get for the name only one result or a mistake.
    What I want is all Peter´s for example.
    So my question how can I execute in a query in my vba code? DBLookup
    works but results only one person..

    Here´s a snippet of my code:
    If Not IsNull(Me.TxTName) Then
    id = 0
    id = DLookup("[KundenID]", "Customer", "[Name] = '" &
    Me.TxTName & "'")
    strWhere = strWhere & "([CustomerID] = " & id & ") AND "
    End If
     
  2. Loading...

    Similar Threads Forum Date
    General Filter Test Hard Talk May 20, 2008
    Sikh News Indian American Couple Donates For Sikh, Punjabi Culture Research In US Breaking News Aug 19, 2016
    Soul Search By Swarn Singh Bains Book Reviews & Editorials May 25, 2016
    Harpal Singh Kumar Cancer Researcher Knighted By Queen Elizabeth II Sikh Personalities Jan 5, 2016
    Movies Ashdoc's Movie Review---dozakh In Search Of Heaven ( 2015 ) Theatre, Movies & Cinema Apr 6, 2015

  3. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Change the RecordSource of the form to a query which includes both tables,
    e.g. to order the records in the form by an OrderDate column for instance:

    SELECT Order.*, Customer.[Name]
    FROM Order INNER JOIN Customer
    ON Order.CustID = Customer.CustID
    ORDER BY OrderDate;

    You can now filter the form to rows where the value of the Name column in
    the Customer table matches the selection in the combo box:

    If Not IsNull(Me.TxTName) Then
    strWhere = strWhere & "([Name] = """ & Me.TxtName & """) AND "
    End If

    This will return all customers of the same name.

    However, I'd suggest that you avoid the use of Name as a column name. It
    can easily be confused with the Name property of an object (I recall a case
    where someone got the name of their report in every row rather than the name
    of each customer). Its better to use something like KundeName.

    Have you considered using a combo box rather than the TxTName control? With
    a RowSource of:

    SELECT DISTINCT [Name]
    FROM Customer
    ORDER BY [Name];

    the user can then select a name from the list rather than typing it in. If
    the combo box's AutoExpand property is True the user can type in a name and
    it will go to the first match as each character is entered

    Ken Sheridan
    Stafford, England

    "MarkusJohn" wrote:

    > I have a form.. in the header there are unbound search fields. The
    > entered values are concatenated to a string. When you press the filter
    > button in the form all the found entries appear.
    >
    > The searchable fields are not only based on one table.
    > In my case I have the tables order and customer. primary keys for order
    > is order no and for customer custid. When I created the form I used the
    > wizzard and selected all required fields. But when I look for a
    > customers name the realtion order customer is not linked over name but
    > id. So I get for the name only one result or a mistake.
    > What I want is all Peter´s for example.
    > So my question how can I execute in a query in my vba code? DBLookup
    > works but results only one person..
    >
    > Here´s a snippet of my code:
    > If Not IsNull(Me.TxTName) Then
    > id = 0
    > id = DLookup("[KundenID]", "Customer", "[Name] = '" &
    > Me.TxTName & "'")
    > strWhere = strWhere & "([CustomerID] = " & id & ") AND "
    > End If
    >
    >
     

Share This Page