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

    Expand Collapse

    I want to do a search based on a form with a filter througout two
    There is one table with order data and another table with customer
    data. I created text fields where the desired values can be entered.

    The table order contains order specific attributes and is linked to the
    table customer via customerid.

    If I look for a specific customer ID the results are fine. If I look
    e.g. for a name (which can occur more than once) I run into serious

    I tried DLookup, however I got only one ID of an customer.

    How do I implement something like DLookup that results me all the ID´s
    that fit to the entered search term?

    If there´s a solution for that problem, would it be reccomendable to
    loop over the result set?
    How could I do that?
    I want to get a term like "id = 1 or id = 2 or id = n".

    Thanks very much for your help!
  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. Rick Wannall

    Rick Wannall
    Expand Collapse

    Can you say more about what you're trying to accomplish? THere are
    techniques for reports, techniques for data display on a form, techniques
    for data entry on a form, and so on.

    First approximation: If you want to display all orders for a customer, put
    a combo box on an unbound form, and feed its display with the list of
    customers. Put a subform in that form, and base the subform on the orders
    table. Use the CusomerID (combobox name for the master, order table field
    name for the child) as the link field between Master and Child data. When
    you select a customer in the combobox on the main form, the order records
    will automatically appear in the subform.
  4. MarkusJohn

    Expand Collapse

    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