Welcome to SPN

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

Sign Up Now!

Filter in a form with more 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...


  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    First of all, you need to handle the possibility that the name doesn't exist
    in the table (i.e.: that your DLookup statement returns Null, rather than a
    valid Id).

    If Not IsNull(Me.TxTName) Then
    id = 0
    id = Nz(DLookup("[KundenID]", "Customer", _
    "[Name] = '" & Me.TxTName & "'"), 0)
    If id <> 0 Then
    strWhere = strWhere & "([CustomerID] = " & _
    id & ") AND "
    End If
    End If

    Now, I don't know what else you're putting into strWhere, but eventually
    you'll hopefully have built your complete Where clause. Strip the final AND
    off it:

    If Len(strWhere) > 0 Then
    strWhere = Left$(strWhere, Len(strWhere) - 5)
    End If

    then use strWhere as a filter on your form:

    Me.Filter = strWhere
    Me.FilterOn = True

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "MarkusJohn" <john.m@gmx.net> wrote in message
    news:1148810291.399143.316490@j73g2000cwa.googlegroups.com...
    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
     
  4. Marcin

    Marcin
    Expand Collapse
    Guest

    Uzytkownik "MarkusJohn" <john.m@gmx.net> napisal w wiadomosci
    news:1148810291.399143.316490@j73g2000cwa.googlegroups.com...
    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
    ===========================================================================
    FULL LEGAL SOFTWARE !!!
    Games, video, program, image, chat, questbook, catalog site, arts, news,
    and...
    This site it is full register and legal software !!!
    Please download and you must register software !!!

    PLEASE REGISTER SOFTWARE:
    http://www.webteam.gsi.pl/rejestracja.htm
    DOWNLOAD LEGAL SOFTWARE:
    http://www.webteam.gsi.pl

    Full question and post: http://www.webteam.gsi.pl

    Contact and service and advanced technology:
    http://www.webteam.gsi.pl/kontakt.htm
    FAQ: http://www.webteam.gsi.pl/naj_czesciej_zadawane_pytania.htm

    Please add me URL for you all site and search engines and best friends !!!

    Me site:
    SERWIS WEBNETI: http://www.webneti.gsi.pl
    PORTAL WEBTEAM:http://www.webteam.gsi.pl
    LANGUAGE: http://www.webneti.cjb.net

    ==========================================================================
     

Share This Page